November 23, 2009 - 29 comments

Creating an App from Scratch: Part 2

Posted by Jason Lengstorf in Development
Creating a Web 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 out three tables

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.

Published by: Jason Lengstorf in Development

  • TeMc

    Hi,

    Great article ! Made someting vague and unknown very simple and clear.

    One question tho, Why keep so many table-entries ? Wouldn't a few array()'s be a much better choise in this case ?

    TeMc

  • TeMc

    Folllow up:

    I'd do 1 table for everything related to lists.

    With array's for each list. The array would contain all the information about the list, and one of the items in an array would be another array wich contain the actuall list items.

    I think that would ease the future use on the front end, ie.:

    $list[id][item_id][color] = “blue”;

  • @TeMc

    We opted to keep separate tables for the sake of normalization and ease of readability. Using arrays seems like it would have been difficult to read moving forward. I think that our choice will start to make more sense as the series progresses.

    If I'm misunderstanding you, feel free to set me straight. 🙂

  • This is great! As a .NET developer I have been digging around PHP recently and it seems like its pretty easy to pick up (I guess thats why its so popular). This is a great article and I look forward to reading the rest, hopefully it will be as well explained as this!

    @TeMc – setting up separate tables is good practise, it allows for expansion and leaves a certain amount of flexibility in the design. I don't see why the front end coding will be any more difficult with more tables, you are just pulling data from a well structured database. Multi dimensional arrays are best avoided where possible as they really make life difficult for everyone involved, not to mention debugging etc.

    Keep it up Jason (you have a new subscriber :))

  • st

    Great article. Sharing working source code samples will be great.

  • Sprogz

    Jason,

    Great article, I'm really looking forward to the other parts. It's an interesting twist bouncing between Chris's visua/UI contributions and your more “codey” bits.

    Like many of us one-man developers, I usually have to do both the design, front-end and back-end work so it's great to see a series of articles joining the two and showing your process.

    Incidentally, who did the artwork for this site? Specifically the “Who is this Guy” portrait and “tin cans and string?”

    Thanks again for sharing all your hard work.

  • @tbmedia:

    Glad you're enjoying it so far! If you're looking to get deeper into PHP, it might be worth checking my book out. It can refresh you on the basics and covers some of the more advanced things possible with PHP (yeah, I know, shameless). 🙂

    @st:

    All the source for this project is going to be covered in the coming articles, and the full app will be released as a “demo” of how it all works.

    @Sprogz:

    I usually do the one-man show as well, so this experience was a lot of fun for me.

    Regarding the artwork on this site, that was me. 🙂

  • greg

    What's the point of ListURL?

  • Wabbitseason

    Why do you define the password field as VARCHAR(150)? Do you plan to store passwords as plain text?

    Have you considered storing only the hash of the password? This would only require 32 or 40 characters, depending on the hashing function (md5 or sha1).

    Of course the hashes require salting, and of course you would have to offer the ability of setting a new password instead of sending out the current one in a reminder email, but storing unencrypted passwords in a database is not secure enough and is a bad practice.

  • Sprogz

    @greg: I think ListURL is going to hold a hash value unique to each list. If you want to “share” a list with one or more people, you can make them aware of this hash (in the form of a URL). It'll probably be something like /list/176d6acc5245dc76e. Could come in useful to share my Xmas list 😀

    @Wabbitseason: Of course there's nothing to stop them storing a 32 or 40 character hash of the password and prepending/appending an eight character (or whatever sized) salt into this one field. However, I agree using a VARCHAR(150) looks a little “large” if they were planning on doing this though.

    I hope they don't use the old “it's only a demo” excuse. Surely they will try and teach best practise so I'm sure they're not going to store the passwords un-encrypted?

    I don't think we should jump the gun until we've seen the remaining parts.

  • @greg:

    As Sprogz suspected, we'll be using ListURL to store a unique URL for each list in the database.

    @Wabbitseason:

    We are definitely not storing plain text passwords. The use of VARCHAR(150) was a lazy oversight on my part; it could have been smaller. I'll be sure to adjust this for v2.0.

    @Wabbitseason and Sprogz:

    We didn't salt the passwords for this app (another 2.0 feature), and I don't really have an excuse for that; it just got overlooked as we were pushing to get this live.

    Thanks for the input!

  • Q_the_novice

    Thankx guys, this is gr8!

  • Important!

    Without the ListItemDone field in list_items table, the application can't retrieve your list after you log out.

    Solution

    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

    )

  • Derik

    This is a great article!

    But I was wondering if you could give me a more detailed explanation as to why the application can't retrieve your list after you log out unless you have a field ListItemDone as suggested by fuSiON?

    I apologize if this seems like it's a self explanatory issue, but I'm missing the logic.

    Thanks,

    Derik

  • @Derik

    You should check ColoredListItems class and specifically formatListItems method in order to understand why this field is important.

  • @Derik:

    In a nutshell, if the table doesn't have the ListItemDone field, the SQL query fails.

    @FuSi0N:

    Thanks for pointing that out; I've updated the article to reflect the proper SQL query and given you credit for catching the bug. Thanks!

  • simn

    Very cool…

    stumbled upon this site “mistakenly” and now im beginning to feel i need to make more of such mistakes…;)

  • mixedup

    Amazing articles, the whole site is mega- useful!! Thanks guys. Also the 'Onlineuniversalwork.com' link is spam, filthy spammers looking for illicit clicks 🙂

  • GillBate

    I've downloaded source code and run them.

    The result is “Internal Server Error”. What should I do?

  • this was really nice as i m a beginner and my knowledge about web designing is very low and it helped me a lot tnx guys

  • nice article

  • usually do the one-man show as well, so this experience was a lot of fun for me.

    Regarding the artwork on this site, that was me. 🙂

  • scott

    great tutorial, thanks.

  • sooraj

    I'm running win 7, and php isn't working for me. Is there a section/forum on errors and troubleshooting related to this tuotiral? Thanks!

  • @sooraj:

    There's not a specific forum on this site for troubleshooting, but Chris has a forum over on CSS-Tricks.com (http://css-tricks.com/forums/discussions) — however, if you're having trouble getting PHP files to execute, it's probably a better idea to start scouring Google or look at http://stackoverflow.com/

    Good luck!

  • Wow.. great series here. This is something I really want to dive into, and hope to be following along building my own app very soon.. thanks for creating this series.

  • Ian

    I'm using MySQL server 5.1.44, and I am getting error code 1064 for the code for tables 1 and 2. Any idea what the reserved word in the code for tables 1 and 2 may be? I can't find it.

  • Pingback: Web Application development for Beginner | Momin Iqbal()

  • Pingback: Why can't I navigate to the page for creating database?()