Creating an App from Scratch: Part 2

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.

Moving On

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!

Series Authors

Jason Lengstorf is a software developer based in Missoula, MT. He is the author of PHP for Absolute Beginners and regularly blogs about programming. When not glued to his keyboard, he’s likely standing in line for coffee, brewing his own beer, or daydreaming about being a Mythbuster.

Chris Coyier is a designer currently living in Chicago, IL. He is the co-author of Digging Into WordPress, as well as blogger and speaker on all things design. Away from the computer, he is likely to be found yelling at Football coaches on TV or picking a banjo.

All Series Navigation