Where We’re At
Up to this point, we’ve planned the way our app is going to look, as well as given ourselves a basic idea of how the app is going to function. The next step is to figure out what’s going to happen behind the scenes to allow our app to work the way we’ve planned.
Okay, So We Know How It Looks, but How Does It Work?
In order to keep a list available after a user logs out of our app, we’ll need to store list information in a database. And, of course, to access that database we’re going to need some kind of server-side scripting language. For this app, we made the choice to go with a combination of MySQL and PHP to handle all our behind-the-scenes data handling and storage.
Data Storage—Planning and Database Structure
Our first step is to decide how we want to organize list data. Since this app is fairly simple, we’ll only need three tables in our database. The first table will store user information, and the second will store list information. The third table will keep track of list items.
Creating the Database
Of course, before we can create our tables, we’ll need a database to work with. For anyone working along at home, we’ll be operating under the assumption that you’re building and testing locally (we recommend XAMPP).
Navigate to http://localhost/phpmyadmin and open the SQL tab. You can use the GUI if you want, but we’re going to use raw SQL commands for learning purposes. The database will be named cl_db, which is all the information that is required to build the database. However, we want to make sure that our users can use characters from any language in their lists, so it’s also a good idea to specify the collation and character set of the database. We’ll be using the UTF-8 character set with general collation, which supports multilingual characters and is case-insensitive.
The command to create this database is:
CREATE DATABASE `cl_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Execute this command from the SQL tab in phpMyAdmin and the new database will become available. Now that we’ve got a database, we’re ready to build our tables.
Table 1: User Information
Using our list app doesn’t require a high security clearance; all we need to know is that you’ve got an email address and that it’s real. To determine that an email address is real, we’ll be sending new users a confirmation link in an email, which they need to follow before using our app. This means we need to have a unique confirmation link and a place to store whether or not an account has been verified.
Of course, we also need to store the user’s email address, and in the interest of keeping redundant data storage to a minimum, we’ll assign each user a unique numeric identifier.
The MySQL command to build this table will look like this:
CREATE TABLE cl_db.users ( UserID INT PRIMARY KEY AUTO_INCREMENT, Username VARCHAR(150) NOT NULL, Password VARCHAR(150), ver_code VARCHAR(150), verified TINYINT DEFAULT 0 )
Table 2: List Information
List information is fairly straightforward. Each list will have a unique identifier, a unique URL, and the identifier of the user that owns the list. This helps us limit the amount of redundant information that needs to be stored.
To build this table, execute the following MySQL command in phpMyAdmin’s SQL tab:
CREATE TABLE cl_db.lists ( ListID INT PRIMARY KEY AUTO_INCREMENT, UserID INT NOT NULL, ListURL VARCHAR(150) )
Table 3: List Items
Finally, we need a table that will store our list items. Each list item needs a unique identifier, the ID of the list it belongs to, and the information the user enters as his or her list item. Also, to support features we’ll be adding later on, we also need to keep a record of the item’s position and color. Execute this command in the SQL tab of phpMyAdmin:
CREATE TABLE cl_db.list_items ( ListItemID INT PRIMARY KEY AUTO_INCREMENT, ListID INT NOT NULL, ListText VARCHAR(150), ListItemDone INT NOT NULL, ListItemPosition INT NOT NULL, ListItemColor INT NOT NULL )
NOTE: The ListItemDone field was omitted in the original post of this article. It was added here after being pointed out in the comments by FuSi0N.
The database with our three tables
Now we have our database and the three tables we’ll need to build our app. Next, we’ll plan how we’re going to create and access our database information using PHP.
Data Handling—Planning and Script Organization
Before we start coding, it’s always a good idea to take a moment and map out everything that needs to be done. That way, we can group tasks into logical arrangements.
Because great code starts with great organization, we’ll be using an object-oriented approach.
Planning our PHP Classes
Object-oriented programming provides an easy way to keep related functions grouped together. After learning object-oriented programming, it becomes an incredibly powerful tool that increases portability, readability, and usability of scripts. Our app is pretty simple, so we’ll only need two classes. The first class is going to handle user interactions, such as registering, updating information, and logging in and out. The second class will handle list interactions, such as adding, deleting, and moving list items.
User Actions Class
Our first class, which we’ll name ColoredListsUsers, needs to handle all the actions our app will perform that are user account-related. Again, this is a pretty simple application, so when we map out everything that users can do with their account, we end up with pretty short list:
- Create an account
- Verify the account
- Update the account email address
- Update the account password
- Retrieve a forgotten password
- Delete the account
In addition to those methods, we’ll also need some support methods, such as one that will send a verification email. We’ll define these methods as we build the app in later installments of this series.
List Actions Class
The list actions class, which we’ll call ColoredListsItems, also has a pretty short list of methods. This class will handle everything else our app does, which is anything a user can do with his or her list items. The list of available actions ends up looking like this:
- Create a list item
- Update a list item
- Delete a list item
- Change a list item’s position
- Change a list item’s color
Action Handling Scripts
Finally, we’ll need a couple action-handling scripts. These will determine what the user’s desired action is, create an instance of the proper object, and call the correct method. As we build our app, we’ll go into more detail on how these scripts will work.
In our next installment of this series, we’ll create the application workflow. Make sure you’re subscribed to CSS-Tricks so you don’t miss out!