[DAY 7] Technical | Interacting with Our Database

[DAY 7] Technical | Interacting with Our Database

This is Day 7 of a 10 Day series on building N-ter, a place where you can manage your job applications online, from idea to launch. You can find the previous posts here:

[Day 0] Let’s Build an Online Startup in 10 Days
[Day 1] Creating a Value Proposition for Our Idea
[Day 2] Refining the Business Model and Naming the Baby
[Day 3] Designing the Product Using a Method from the Lean Startup
[Day 4] Establishing Our Website
[Day 5] Creating Our First Webpages
[Day 6] Developing Our First User Story

Today you can expect quite a short post with an update on development progress and a couple of tips and resources for creating and managing database connections with your website. If you’re not interested in the technical details of the journey, today is a good day to skip. 🙂

 


Today was quite a full day, especially because a few issues came up in the code implementation, that took me quite a while to solve. I’m going to talk about it a bit in this post. Treat the post today as more of a short progress update than an extensive overview. I will provide some resources to accompany what I am doing at the moment, though.

 

Let’s Have a Look Where We Need Custom Code

I’ve mentioned at the start of this series that I am going to try to keep the required code for our solution at a minimum. As I gave up on trying to utilize the Contact Form 7 plugin for creating an input form for the user, the scope we need custom code for expanded a little. We can say that now we need to create custom code for all interactions concerning data for and from the database, starting from the page to the database and the way back. This means the custom code is required for the following activities (I’ve provided a link with code examples for every one of them so you can look up how it can be done):

I’m writing the code for all five parts above in only three files, which are all located in our child-themes folder: the functions.php file, the template file for our application dashboard and the template file for our application editing page.

Direct interactions with the database work a bit differently in WordPress than in “pure” PHP. Many functions are already prepared to be used, which simplifies the entire process quite a bit. There is a global class called WPDB which you can use anywhere to directly interact with the database. This class is thoroughly explained in the WordPress Codex links for storing and retrieving data from the database above.

Another thing I need to mention: We should add SQL to the list of languages that you should have a basic understanding of. SQL is used for interaction with and manipulation of databases like MySQL. The language itself is quite simple once you understand how it works. Here is a good tutorial on SQL.

 

The Troubles of Trying to be a Coder

If you get into coding yourself, you will see that sometimes there’s that one small tiny thing in the code that you did wrong or just forgot about. Subsequently, that little thing drives you crazy because you can’t find it and your code, which should be working, just doesn’t work. And then you can’t figure out why. And after a while you start questioning everything including your own sanity. I’m sure this problem is not exclusively reserved to newbies like me and the experienced coders around here can relate to this problem as well. Often, it’s a thing as tiny as a missing semicolon.

For me this small little thing today was the database table prefix. I spent 3 hours today (we’re on a tight schedule!! :D) trying to figure out why the code wouldn’t work that I just created to get data from our database. I double and triple checked the code, rewrote everything from the ground up, tried to make the code as lean as possible and so on. Nothing would work, I just kept on getting a generic error that something was wrong with my SQL statement.

I discovered my mistake when I tried pulling data from another WordPress table with the same code and it worked! But with our custom table, it still didn’t! That’s when I realized the problem must be with the table. And yes, it was. Or at least with the way I handled the table. The WordPress database tables have a prefix (standard is “wp_”) that you can change during WordPress installation. When accessing standard WordPress tables with code, that prefix doesn’t need to be added. But if you’re working with a custom table, you need to add it by putting the following code in front of the table name: ‘$wpdb->prefix’.

There goes three hours of my day just because I forgot to add that damn prefix. If you’re getting into code, be prepared for minor stuff like this to throw you off your schedule! But there we go, problem solved. We can continue.

 

Achievements for Today

Overall, I was mainly working on the database connection today, as mentioned in the first paragraph of this post. Users can now create new job applications along with supporting data, that all gets stored in the database. Also, they can now display all their applications on the application dashboard. There is also a functionality which allows to edit specific application records by clicking on a button on the application dashboard. A click takes the user to the application editing page, where all the data is already pre-filled in the form and can be edited. Once the user is done editing an application record and saves is, he is automatically taken back to the application dashboard.

This results in the following user story board for today.

(Click to enlarge)

As you can see, three more user stories are in the “developed” bucket today. They still have to be refined and thoroughly tested though, which we are going to do at the end. I am planning to touch on testing on Day 9 of this series.

 

Day 7 Summary

There’s not that much to summarize today. Keep working on making your user stories reality! 😊

Tomorrow, we are going to keep doing exactly that!

 

Meanwhile, sign up for the email-newsletter and I’ll let you know when the new posts go online!

One Reply to “[DAY 7] Technical | Interacting with Our Database”

Leave a Reply

Your email address will not be published. Required fields are marked *