All Posts in Development

May 11, 2016 - No Comments!

Making the Most of a Great Opportunity With Email Capture

Posted by Ian Murphy in Client News, Design, Development

It’s been a pretty cool 2016 for Copter Labs and our clients. We’re working with new people and doing new things for clients, and the clients themselves are turning into TV stars.

Rob Sulaver of Bandana Training rode to the rescue for LaTasha on ABC’s My Diet is Better Than Yours in January, and she started shedding weight as soon as he started on the show.

To top that off, for the last month two Copter clients have been tearing it up on NBC’s STRONG - Chris Ryan and Todd Durkin. The competition show, which airs Thursdays at 8 p.m., has been dramatic, exciting, inspiring and heartbreaking to watch. 

While we could brag all day about Copter clients - really, we could - what we wanted to highlight for you was the two different approaches Chris and Todd took to spruce up their websites to take advantage of the increased traffic from the show.

A special-made single page for email capture

Todd Durkin only knows how to do things one way - all in and full speed - and that’s how he approached the single page we made special for his time on STRONG. He came to us looking for a place to put all his purpose-built content around the show, like pre- and post-game videos (that are fantastic) and information about all his appearances with local and national media.

But most importantly, he didn’t want to miss out on any opportunity to capture information about all the visitors coming to his site to check out who this crazily-intense competitor was.

So we built him a page with a email capture front-and-center supported by Todd’s guide “5 Secrets to a STRONGER Mindset.” We crafted a gorgeous cutout of his promo picture from the show, and supported the top banner with a nice big STRONG logo to make sure everybody got the picture. Todd wanted people to be drawn to that free guide, and nobody who hits his page can possibly miss the offer.

todd page

The next element has his video content, which he updates each week with two videos for each show. That’s dedication and hard work, and it’s bringing people back to the site over and over, week after week. If he didn’t get their information on the first go-around, he’ll get them eventually.

We incorporated the colors, fonts and style from his original site to make sure it was a smooth experience for visitors heading from the single page to the rest of his site. On his home page, the button in the top banner sends traffic to this single STRONG page, indicating that right now this is the most important action for a visitor to do when they first arrive the site.

Email capture through a clean, simple pop up

Copter built Chris Ryan’s site way back 2013, and it’s still one of our favorite sites we’ve ever made. It’s simple, it’s elegant and it’s different (back then it was also way ahead of its time).

It’s also one of the last sites we did before mobile-friendly responsive design was part of our standard build on launch. At that point, the iPad hadn’t even been out a full year, and people still were surfing the Web almost exclusively via computers.

Well that’s all changed - more people surf the web via mobile phones today than any other device combined, including computers. Chris knew that people sitting on their couches would search for his site with their phones, so he came to us to get the site mobile-friendly and ready to wow them.

Like Todd, he also didn’t want to miss any opportunity to capture information from the increased traffic. He went a different route, one more in keeping with the design of his site: a simple, clean pop up that offers his guide in exchange for an email address.

chris popup

We used his cutout from the NBC promo material (we just really like cutouts, okay?) and used as much white space as we could to keep people’s eyes on the text and moving towards the goal of capturing their email.

We hooked Chris up with one of our favorite new tools, a plugin called OptinMonster that’s not only pretty easy to use, but it shows you a ton of great data on how well your pop-up is performing.

optinmonster

Grab the bull by the horns

Chris and Todd (and Rob too, though for a different show) knew that their appearances on these nationally-televised network TV shows would get increased web traffic, so each came to Copter to make sure their everyday businesses took advantage of the boost.

Not everybody is going to make it onto TV. But if you’re doing a special promotion, or you’ve got a big advertising campaign about to happen, or if you’re brand has just gotten much bigger than when you started, make sure your site is prepared to take advantage of that increased traffic.
Good luck to Chris and Todd on STRONG!

February 6, 2011 - 3 comments

Strict vs. Loose Comparisons in PHP

Posted by Jason Lengstorf in Development

If you've come up against this in the past, this is going to sound like a no-brainer, but for programmers who have only used PHP and don't know the difference between loose comparisons and the strict variety, this tip might save a lot of headache.

What's the Problem?

In what was an effort to make PHP more accessible to programmers, variables in PHP can't be declared with a specific type. While this does make it easier to get started, it also leaves room for some confusing situations.

Unexpected Output in Mathematical Operations

The lack of a specific type means that PHP operates on a "best-guess" principle (they call it type juggling), where PHP converts the value of a variable to the most appropriate type for the action being carried out.

This means that while you'll usually get the expected results, there are some cases where you can end up with some seemingly bizarre output.

For example, check out this code:

echo 'fortieth' + 7; // Outputs 7
echo '40th' + 7; // Outputs 47

Seems weird, right? What's happening is that if a string starts with a number, PHP will pull it out and use it as the value for mathematical operations. Otherwise, it becomes 0.

Unexpected Output from Loose Comparisons

A loose comparison is one performed using two equals signs (==). It follows suit with the "best-guess" approach, which can lead to some unexpected results.

As proof, here are three different data types:

echo gettype(0); // Outputs "integer"
echo gettype(FALSE); // Outputs "boolean"
echo gettype('test'); // Outputs "string"

Now check out what happens when we compare these types of data:

var_dump(0==FALSE); // bool(true)
var_dump(0=='test'); // bool(true)
var_dump(FALSE=='test'); // bool(false)

This is confusing on several levels.

It does make sense that 0 and FALSE would be considered equal. So we're good so far, but the number 0 and the word "test", in pretty much every situation I can think of, should probably come back FALSE. This one's pretty odd to have come back TRUE, but it goes back to the fact that PHP will try and convert "test" to an integer, so it becomes 0 for the comparison.

Next, we've got FALSE and "test" coming back FALSE. Well, that seems inconsistent. If "test" becomes 0, and 0==FALSE, why doesn't this one come out TRUE as well?

(It's happening because FALSE=='test' would require "test" to undergo two type changes: string to integer 0, then integer to boolean. I think. Not the point. It's just not intuitive.)

In most cases, loose comparisons aren't a problem. But as we've seen, there are a few cases where loose comparisons can cause really confusing bugs.

Extra Credit: Read the PHP manual entry on type comparisons.

I Don't See How This Affects Me

The above examples are pretty unlikely to appear in production code, so it might be easy to write this off as a "Geek on a Soapbox" type post. That's not the case, though.

Let's look at an example that you might actually see in a real project. In this snippet, the comments for an entry are counted and returned from the database as an integer. If the query fails, boolean FALSE is returned instead.

To make this example easy to reproduce, we're going to replace the database query with an explicitly set variable. Let's start by returning a count of 5.

<?php

echo "<pre>";

$comment_count = get_comment_count();

if( $comment_count==FALSE )
{
    echo "The entry count database query failed.";
}
else
{
    echo "The entry count is $comment_count.";
}

echo "</pre>";

function get_comment_count(  )
{
    /*
     * This is a DB query; it returns an integer (number of comments) or
     * boolean FALSE on query failure. To keep this example concise, we're going
     * to fake the return value
     */
    $comment_count = 5;

    // Output the type of the comment count
    echo 'Database return value: ';
    var_dump($comment_count);

    // If our "DB query" fails, return FALSE to indicate failure
    if( $comment_count==FALSE )
    {
        return FALSE;
    }

    return $comment_count;
}

?>

The output is what we'd expect:

Database return value: int(5)
The entry count is 5.

And if we change line 25 to read $comment_count = FALSE; (which means the query failed), we also see the expected output:

Database return value: bool(false)
The entry count database query failed.

However, what happens when an entry is new and doesn't have any comments yet? Check out the results when you change line 25 to $comment_count = 0; in the test code:

Database return value: int(0)
The entry count database query failed.

Uh oh. A valid integer, 0, was returned, but our script misinterpreted it as a failed database query!

Keeping It Clear and Logical — Enter Strict Comparisons

Here's where we can take one easy step to ensure that our code avoids as many weird bugs as possible: switch to strict comparisons.

A strict comparison is done with three equals signs (===), and it requires the two pieces of data being compared to have not only the same value, but the same type as well.

Personally, I think you should use strict comparisons whenever possible.

If we apply strict comparisons to our comment count example, we can see that the bug goes away:

<?php

echo "<pre>";

$comment_count = get_comment_count();

if( $comment_count===FALSE )
{
    echo "The entry count database query failed.";
}
else
{
    echo "The entry count is $comment_count.";
}

echo "</pre>";

function get_comment_count(  )
{
    /*
     * This is a DB query; it returns an integer (number of comments) or
     * boolean FALSE on query failure. To keep this example concise, we're going
     * to fake the return value
     */
    $comment_count = 0;

    // Output the type of the comment count
    echo 'Database return value: ';
    var_dump($comment_count);

    // If our "DB query" fails, return FALSE to indicate failure
    if( $comment_count===FALSE )
    {
        return FALSE;
    }

    return $comment_count;
}

?>

The output now comes out properly.

Database return value: int(0)
The entry count is 0.

A Small Adjustment for a Lot Less Headache

It doesn't take much to use strict comparisons in your code. And I can promise you that you'll barely notice the adjustment.

You will, however, avoid one of those ghost-in-the-machine type bugs that keeps you banging your head against the keyboard for an hour or two. (Trust me. I know from experience.)

NOTE: For a table detailing how comparisons work in both loose and strict comparisons, see the PHP manual.

On an Unrelated Note

For anyone who missed it, I had a couple articles run on Nettuts+ recently. If you haven't already, check them out and read through them:

And, as always, I want to hear your thoughts, love, hate, and haiku in the comments!

December 14, 2009 - 40 comments

Creating an App from Scratch: Part 9

Posted by Jason Lengstorf in Development
Creating a Web App from Scratch Part 9

Bugs, Security, and Other Tweaks

There were supposed to be only eight parts to this series, but as we started releasing them, Chris and I realized that there was going to need to be a follow-up post to address some of the bug fixes, security patches, and a few other minor changes.

NOTE: All the changes we're going to cover in this article are already included in the source code.

Bug Fixes

After releasing the live app, a handful of bugs showed up in the comments. We tried to address these as quickly as possible to keep the app from causing unnecessary grief for our users. We'll go over the major bugs here.

Account Created, List Failed Error

The first thing we saw was that when there were more than just one or two people trying to create accounts, the app started failing to create user lists after an account was created. Upon reviewing the code, I found that the error seemed to be coming from the following line:

$userID = $this->_db->lastInsertId();

$userID seemed to be unreliable, and therefore the query to insert a new list into the database was failing regularly. To fix this, we implemented a complex query that worked around the use of lastInsertId():

            /*
             * If the UserID was successfully
             * retrieved, create a default list.
             */
            $sql = "INSERT INTO lists (UserID, ListURL) VALUES
                    (
                        (
                            SELECT UserID
                            FROM users
                            WHERE Username=:email
                        ),
                        (
                            SELECT MD5(UserID)
                            FROM users
                            WHERE Username=:email
                        )
                    )";

Performance-wise, this is going to be slower than the original post, but it's incredibly more reliable (since implementing this fix, we've had no reports of this error). Any MySQL supergeeks who may have a better solution, please post it in the comments!

Double-Clicking "Add" Sometimes Added Duplicate Entries

One little user interface quirk that was discovered was that you could click multiple times in succession on the "Add" button. In our original JavaScript, we only cleared the value of the input field upon a successful AJAX result. That is ideal, since when that text disappears that is your visual queue that it's been added to your list successfully. Plus, generally that happens quickly enough it feels pretty instant. However, if you straight up "double-click" on that add button (which people absolutely still do that), you might get two or more requests off before the success comes back and clears the fields (when the field is clear, the submit button will do nothing).

One method to fix this could have been to clear the field as soon as a click happens, but the problem there is that if the save is unsuccessful you'll lose your text. Instead, we just add a little more smarts. When the submit button is clicked and there is text ready to add, the AJAX request is made and the button is disabled. Upon success, the field is cleared and the button is re-enabled. This ensures only one submission is possible.

In /js/lists.js, we added the following at line 114:

    $('#add-new').submit(function(){

       //  ... variables and whitelist stuff ...

        if (newListItemText.length > 0) {
            // Button is DISABLED
            $("#add-new-submit").attr("disabled", true);

            $.ajax({

                // Ajax params stuff

                success: function(theResponse){

                    // list adding stuff

                    // field is cleared and button is RE-ENABLED
                    $("#new-list-item-text").val("");
                    $("#add-new-submit").removeAttr("disabled");
     }

NOTE: As you can see, we remove the "disabled" attribute entirely upon a successful response from our query. That is the only way to re-able a submit button. Setting disabled to "false" has no effect.

Changing Email Address with a Blank Email Crippled Account

It was also brought to our attention that clicking the "Change Email" button with a blank field would not only succeed, but would cripple the account and make it unusable. Fixing this was as simple as making sure the email address submitted was valid by inserting the following in the updateEmail() method in /inc/class.users.inc.php:

        if( FALSE === $username = $this->validateUsername($_POST['username']) )
        {
            return FALSE;
        }

Then, instead of binding the $_POST value to the query, we bind the new variable $username, which contains the valid email address if the check didn't fail. Note the use of the new function validateUserEmail()—we'll go over that in the next section on security.

Security Issues

After we worked the bugs out of our app, we turned to the security holes that were pointed out by commenters. Some of these were simple oversights on our part, and some of the problems were news to us. With the help of our readers, though, we tried to patch everything up.

JavaScript Could Be Inserted Into Edited Items

When creating new items, we checked for any JavaScript in the input using the cleanHREF() function, then stripped out unwanted tags on the server side using strip_tags() and a whitelist of acceptable tags. However, we had missed that JavaScript could be inserted into existing items when they were edited. To correct this issue, we turned to a preexisting input sanitizing function (lines 00326-00384) posted by Zoran in the comments of Part 8.

We wrapped the code in a method called cleanInput() and placed it in /inc/class.users.inc.php:

    /**
     * Removes dangerous code from the href attribute of a submitted link
     *
     * @param string $input        The string to be cleansed
     * @return string            The clean string
     */
    private function cleanInput($data)
    {
        // http://svn.bitflux.ch/repos/public/popoon/trunk/classes/externalinput.php
        //  ----------------------------------------------------------------------
        // | Copyright (c) 2001-2006 Bitflux GmbH                                 |
        //  ----------------------------------------------------------------------
        // | Licensed under the Apache License, Version 2.0 (the "License");      |
        // | you may not use this file except in compliance with the License.     |
        // | You may obtain a copy of the License at                              |
        // | http://www.apache.org/licenses/LICENSE-2.0                           |
        // | Unless required by applicable law or agreed to in writing, software  |
        // | distributed under the License is distributed on an "AS IS" BASIS,    |
        // | WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or      |
        // | implied. See the License for the specific language governing         |
        // | permissions and limitations under the License.                       |
        //  ----------------------------------------------------------------------
        // | Author: Christian Stocker <chregu@bitflux.ch>                        |
        //  ----------------------------------------------------------------------
        //
        // Kohana Modifications:
        // * Changed double quotes to single quotes, changed indenting and spacing
        // * Removed magic_quotes stuff
        // * Increased regex readability:
        //   * Used delimeters that aren't found in the pattern
        //   * Removed all unneeded escapes
        //   * Deleted U modifiers and swapped greediness where needed
        // * Increased regex speed:
        //   * Made capturing parentheses non-capturing where possible
        //   * Removed parentheses where possible
        //   * Split up alternation alternatives
        //   * Made some quantifiers possessive

        // Fix &entityn;
        $data = str_replace(array('&amp;','&lt;','&gt;'), array('&amp;amp;','&amp;lt;','&amp;gt;'), $data);
        $data = preg_replace('/(&#*w )[x00-x20] ;/u', '$1;', $data);
        $data = preg_replace('/(&#x*[0-9A-F] );*/iu', '$1;', $data);
        $data = html_entity_decode($data, ENT_COMPAT, 'UTF-8');

        // Remove any attribute starting with "on" or xmlns
        $data = preg_replace('#(<[^>] ?[x00-x20"'])(?:on|xmlns)[^>]* >#iu', '$1>', $data);

        // Remove javascript: and vbscript: protocols
        $data = preg_replace('#([a-z]*)[x00-x20]*=[x00-x20]*([`'"]*)[x00-x20]*j[x00-x20]*a[x00-x20]*v[x00-x20]*a[x00-x20]*s[x00-x20]*c[x00-x20]*r[x00-x20]*i[x00-x20]*p[x00-x20]*t[x00-x20]*:#iu', '$1=$2nojavascript...', $data);
        $data = preg_replace('#([a-z]*)[x00-x20]*=(['"]*)[x00-x20]*v[x00-x20]*b[x00-x20]*s[x00-x20]*c[x00-x20]*r[x00-x20]*i[x00-x20]*p[x00-x20]*t[x00-x20]*:#iu', '$1=$2novbscript...', $data);
        $data = preg_replace('#([a-z]*)[x00-x20]*=(['"]*)[x00-x20]*-moz-binding[x00-x20]*:#u', '$1=$2nomozbinding...', $data);

        // Only works in IE: <span style="width: expression(alert('Ping!'));"></span>
        $data = preg_replace('#(<[^>] ?)style[x00-x20]*=[x00-x20]*[`'"]*.*?expression[x00-x20]*([^>]* >#i', '$1>', $data);
        $data = preg_replace('#(<[^>] ?)style[x00-x20]*=[x00-x20]*[`'"]*.*?behaviour[x00-x20]*([^>]* >#i', '$1>', $data);
        $data = preg_replace('#(<[^>] ?)style[x00-x20]*=[x00-x20]*[`'"]*.*?s[x00-x20]*c[x00-x20]*r[x00-x20]*i[x00-x20]*p[x00-x20]*t[x00-x20]*:*[^>]* >#iu', '$1>', $data);

        // Remove namespaced elements (we do not need them)
        $data = preg_replace('#</*w :w[^>]* >#i', '', $data);

        do
        {
            // Remove really unwanted tags
            $old_data = $data;
            $data = preg_replace('#</*(?:applet|b(?:ase|gsound|link)|embed|frame(?:set)?|i(?:frame|layer)|l(?:ayer|ink)|meta|object|s(?:cript|tyle)|title|xml)[^>]* >#i', '', $data);
        }
        while ($old_data !== $data);

        return $data;
    }

Then, we modified updateListItem() on line 239 to call the new method:

        $newValue = $this->cleanInput(strip_tags(urldecode(trim($_POST["value"])), WHITELIST));

CATCH: This function appears to encode any non-English characters. Foreign language users may see some unexpected behavior.

Cross-Site Request Forgeries

Another risk we hadn't considered when building this app was the possibility that a malicious user could send bogus requests to our app by piggybacking on a Colored Lists user's session in a form of attack called Cross-Site Request Forgeries (CSRF). The snippet of JavaScript below, placed on any site, would be executed if a user that was logged in to our app were to visit the page. (Huge thanks to Dan at Sketchpad for pointing this out and providing the above sample attack.)

    <script type="text/javascript">

        var form = document.createElement("form");
        form.setAttribute("method", "post");
        form.setAttribute("action", "http://coloredlists.com/db-interaction/users.php");

        var fields = new Array();
        fields["user-id"] = "158";
        fields["action"] = "deleteaccount";

        for(var key in fields)
        {
            var hiddenField = document.createElement("input");
            hiddenField.setAttribute("type", "hidden");
            hiddenField.setAttribute("name", key);
            hiddenField.setAttribute("value", fields[key]);

            form.appendChild(hiddenField);
        }

        document.body.appendChild(form);
        form.submit();

    </script>

To remedy this, we need to generate a token to include with each form submission that is also stored in the session. That way we can make sure the two match before executing any requests. In doing this, CSRF is virtually eliminated.

In /common/base.php, we added following at line 19:

    if ( !isset($_SESSION['token']) )
    {
        $_SESSION['token'] = md5(uniqid(rand(), TRUE));
    }

This creates a unique token for the user's session. Then, on every form on our site, we added the following hidden input:

                <input type="hidden" name="token"
                    value="<?php echo $_SESSION['token']; ?>" />

And updated both /db-interaction/lists.inc.php and /db-interactions/users.inc.php with the following starting at line 15:

if ( $_POST['token'] == $_SESSION['token']
    && !empty($_POST['action'])
    && isset($_SESSION['LoggedIn'])
    && $_SESSION['LoggedIn']==1 )

Now any request made without a valid token will fail. For more on CSRF, visit Chris Shiflett's blog.

Some Input Was Improperly Sanitized

Above, we talked about the problem with blank email change requests breaking accounts, and we created a method called validateUsername() that made sure only valid email addresses were allowed to change an existing user email. That method looks like this:

    /**
     * Verifies that a valid email address was passed
     *
     * @param string $email    The email address to check
     * @return mixed        The email address on success, FALSE on failure
     */
    private function validateUsername($email)
    {
        $pattern = "/^([ a-zA-Z0-9]) ([ a-zA-Z0-9._-])*@([a-zA-Z0-9_-]) ([a-zA-Z0-9._-] ) $/";
        $username = htmlentities(trim($email), ENT_QUOTES);
        return preg_match($pattern, $username) ? $username : FALSE;
    }

Essentially, it uses a regular expression to match the pattern of a valid email address, and either returns the validated email address of boolean FALSE.

Other Changes

Aside from bugs and security patches, there were a couple parts of the site that we just felt should have been better.

Made Public URLs Tougher to Guess

First, the original public list URLs were determined using dechex(), and they were short and easy to guess. We modified them to use MD5 instead to create longer, much more difficult to guess public URLs. This happens right at the list's creation when the query calls SELECT MD5(UserID) in createAccount() on line 100.

Allowed "Safe" Links in List Items

Some links are acceptable, and we felt that our app would be much more useful if safe links were allowed in list items. To allow this, we simply removed the call to strip_tags() in formatListItems() (found in /inc/lists.inc.php on line 173):

        return "tttt<li id="$row[ListItemID]" rel="$order" "
            . "class="$c" color="$row[ListItemColor]">$ss"
            . $row['ListText'].$d
            . "$se</li>n";

The items are now sanitized on the way in, so we don't need to worry about them on the way out.

Summary

The steps we took above helped make our app more secure and dependable. However, we know that nothing is ever perfect, so if you've got other bugs, security holes, or suggestions, let us know in the comments!

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.

December 4, 2009 - 3 comments

Creating an App from Scratch Source Files and Giveaway Winner

Posted by Jason Lengstorf in Development

Chris and I have packaged up the source code and PSD files for our Colored Lists web app and put them up on a splash page that includes navigation to the whole series.

Creating a Web App from Scratch Homepage

Also, we selected the five random winners for the book giveaway: congratulations to Bryan, gemmes, Gaston, An, and Mike Henderson. They'll be receiving a free copy of PHP for Absolute Beginners. If you didn't win a copy, you can still save on the ebook with the discount code PHPXBRZQXSIKG (good through 12/31/2009).

Next week we'll add a Part 9 to the series that will go over some of the bug fixes and security patches that were pointed out.

November 26, 2009 - 13 comments

Creating an App from Scratch: Part 7

Posted by Jason Lengstorf in Development
Creating a Web App from Scratch Part 7

Where We're At

Now that we've got a set of AJAX controls mostly assembled, we can start building our PHP class to handle list interactions. This class will be called ColoredListsItems and it will reside in the file class.lists.inc.php in the inc folder.

This class will contain methods to handle all of the actions performed by our app or our users regarding list items. Namely, these actions are:

  • Displaying list items
  • Saving new list items
  • Reordering list items
  • Changing item colors
  • Editing item text
  • Marking an item as "done"
  • Deleting items

Also, because we need to be able to load a non-editable version of a list when viewed from the public URL, we'll need to add that functionality as well.

Defining the Class

Before we can do much of anything, we need to have our ColoredListsItems class defined. Inside inc/class.lists.inc.php, add the following class declaration and constructor:

<?php

/**
 * Handles list interactions within the app
 *
 * PHP version 5
 *
 * @author Jason Lengstorf
 * @author Chris Coyier
 * @copyright 2009 Chris Coyier and Jason Lengstorf
 * @license   http://www.opensource.org/licenses/mit-license.html  MIT License
 *
 */
class ColoredListsItems
{
    /**
     * The database object
     *
     * @var object
     */
    private $_db;

    /**
     * Checks for a database object and creates one if none is found
     *
     * @param object $db
     * @return void
     */
    public function __construct($db=NULL)
    {
        if(is_object($db))
        {
            $this->_db = $db;
        }
        else
        {
            $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
            $this->_db = new PDO($dsn, DB_USER, DB_PASS);
        }
    }
}

?>

Notice that the constructor is identical to the one we used in ColoredListsUsers (see Part 5—Developing the App: User Interaction); it checks for a database object and creates one if none are available.

Displaying List Items

Even though we don't currently have any items saved in our database, we know what they're going to look like. With that in mind, we can write our output functions to display our list items to users, both in a logged in and logged out state.

If the User Is Logged In

When our user is logged in, we'll be loading their list by their user name. This user name is stored in the $_SESSION superglobal.

In inc/class.lists.inc.php, define the method loadListItemsByUser() and insert the following code:

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Loads all list items associated with a user ID
     *
     * This function both outputs <li> tags with list items and returns an
     * array with the list ID, list URL, and the order number for a new item.
     *
     * @return array    an array containing list ID, list URL, and next order
     */
    public function loadListItemsByUser()
    {
        $sql = "SELECT
                    list_items.ListID, ListText, ListItemID, ListItemColor,
                    ListItemDone, ListURL
                FROM list_items
                LEFT JOIN lists
                USING (ListID)
                WHERE list_items.ListID=(
                    SELECT lists.ListID
                    FROM lists
                    WHERE lists.UserID=(
                        SELECT users.UserID
                        FROM users
                        WHERE users.Username=:user
                    )
                )
                ORDER BY ListItemPosition";
        if($stmt = $this->_db->prepare($sql))
        {
            $stmt->bindParam(':user', $_SESSION['Username'], PDO::PARAM_STR);
            $stmt->execute();
            $order = 0;
            while($row = $stmt->fetch())
            {
                $LID = $row['ListID'];
                $URL = $row['ListURL'];
                echo $this->formatListItems($row,   $order);
            }
            $stmt->closeCursor();

            // If there aren't any list items saved, no list ID is returned
            if(!isset($LID))
            {
                $sql = "SELECT ListID, ListURL
                        FROM lists
                        WHERE UserID = (
                            SELECT UserID
                            FROM users
                            WHERE Username=:user
                        )";
                if($stmt = $this->_db->prepare($sql))
                {
                    $stmt->bindParam(':user', $_SESSION['Username'], PDO::PARAM_STR);
                    $stmt->execute();
                    $row = $stmt->fetch();
                    $LID = $row['ListID'];
                    $URL = $row['ListURL'];
                    $stmt->closeCursor();
                }
            }
        }
        else
        {
            echo "tttt<li> Something went wrong. ", $db->errorInfo, "</li>n";
        }

        return array($LID, $URL, $order);
    }
}

This method starts with a somewhat complex query that starts by joining the list_items and lists tables, then uses a sub-query to filter the list items by user ID.

If the query returns results, we loop through them and call the yet-to-be-defined formatListItems() method. Each formatted item is output immediately using echo(), and the list ID and URL are saved.

If no results are returned (meaning the user doesn't have any items on their list), the list ID and URL won't be returned. However, we need this information in order to allow users to submit new items and share their lists. A check is in place to see if the list ID variable ($LID) is set. If not, an additional query is executed to retrieve the user's list ID and URL.

The list ID and URL are then returned as an array.

If the User Is Not Logged In

If no user is logged in, we don't have access to a user name with which to load the items. Therefore, we need to use the list's ID to load items. We're able to determine the list's ID using the list's URL, which is the only way a user who isn't logged in will be able to view a list in the first place. The method looks like this in inc/class.lists.inc.php:

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Outputs all list items corresponding to a particular list ID
     *
     * @return void
     */
    public function loadListItemsByListId()
    {
        $sql = "SELECT ListText, ListItemID, ListItemColor, ListItemDone
                FROM list_items
                WHERE ListID=(
                    SELECT ListID
                    FROM lists
                    WHERE ListURL=:list
                )
                ORDER BY ListItemPosition";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(':list', $_GET['list'], PDO::PARAM_STR);
            $stmt->execute();
            $order = 1;
            while($row = $stmt->fetch())
            {
                echo $this->formatListItems($row, $order);
                  $order;
            }
            $stmt->closeCursor();
        } else {
            echo "<li> Something went wrong. ", $db->error, "</li>";
        }
    }
}

Formatting List Items

To make our previous two methods work properly, we also need to define our formatListItems() method. This method is fairly straightforward, but it also needs a helper method to determine the CSS class for each item, which we'll call getColorClass(). This helper method only exists to simplify our code. Insert both methods into inc/class.lists.inc.php as follows:

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Generates HTML markup for each list item
     *
     * @param array $row    an array of the current item's attributes
     * @param int $order    the position of the current list item
     * @return string       the formatted HTML string
     */
    private function formatListItems($row, $order)
    {
        $c = $this->getColorClass($row['ListItemColor']);
        if($row['ListItemDone']==1)
        {
            $d = '<img class="crossout" src="/assets/images/crossout.png" '
                . 'style="width: 100%; display: block;"/>';
        }
        else
        {
            $d = NULL;
        }

        // If not logged in, manually append the <span> tag to each item
        if(!isset($_SESSION['LoggedIn'])||$_SESSION['LoggedIn']!=1)
        {
            $ss = "<span>";
            $se = "</span>";
        }
        else
        {
            $ss = NULL;
            $se = NULL;
        }

        return "tttt<li id="$row[ListItemID]" rel="$order" "
            . "class="$c" color="$row[ListItemColor]">$ss"
            . htmlentities(strip_tags($row['ListText'])).$d
            . "$se</li>n";
    }

    /**
     * Returns the CSS class that determines color for the list item
     *
     * @param int $color    the color code of an item
     * @return string       the corresponding CSS class for the color code
     */
    private function getColorClass($color)
    {
        switch($color)
        {
            case 1:
                return 'colorBlue';
            case 2:
                return 'colorYellow';
            case 3:
                return 'colorRed';
            default:
                return 'colorGreen';
        }
    }
}

An array containing each list item's attributes is passed to formatListItems(), and different attributes are created depending on the values that are passed. If a user isn't logged in, we manually append a <span> to the markup to keep our CSS from breaking, and then we wrap the whole thing in a <li> and return it.

Calling Our New Methods in the Main View

Our main page (index.php) currently has notes from the designer that look like this:

<div id="main">


   <noscript>This site just doesn't work, period, without JavaScript</noscript>


   <!-- IF LOGGED IN -->

          <!-- Content here -->


   <!-- IF LOGGED OUT -->

          <!-- Alternate content here -->


</div>

In order to make these notes into a functional script, we need to add the following logic to index.php to call the proper methods:

        <div id="main">
            <noscript>This site just doesn't work, period, without JavaScript</noscript>
<?php
if(isset($_SESSION['LoggedIn']) && isset($_SESSION['Username'])):
    echo "ttt<ul id="list">n";

    include_once 'inc/class.lists.inc.php';
    $lists = new ColoredListsItems($db);
    list($LID, $URL, $order) = $lists->loadListItemsByUser();

    echo "ttt</ul>";
?>

            <br />

            <form action="db-interaction/lists.php" id="add-new" method="post">
                <input type="text" id="new-list-item-text" name="new-list-item-text" />

                <input type="hidden" id="current-list" name="current-list" value="<?php echo $LID; ?>" />
                <input type="hidden" id="new-list-item-position" name="new-list-item-position" value="<?php echo   $order; ?>" />

                <input type="submit" id="add-new-submit" value="Add" class="button" />
            </form>

            <div class="clear"></div>

            <div id="share-area">
                <p>Public list URL: <a target="_blank" href="http://coloredlists.com/<?php echo $URL ?>.html">http://coloredlists.com/<?php echo $URL ?>.html</a>
                &nbsp; <small>(Nobody but YOU will be able to edit this list)</small></p>
            </div>

            <script type="text/javascript" src="js/jquery-ui-1.7.2.custom.min.js"></script>
            <script type="text/javascript" src="js/jquery.jeditable.mini.js"></script>
            <script type="text/javascript" src="js/lists.js"></script>
            <script type="text/javascript">
                initialize();
            </script>

<?php
elseif(isset($_GET['list'])):
    echo "ttt<ul id='list'>n";

    include_once 'inc/class.lists.inc.php';
    $lists = new ColoredListsItems($db);
    list($LID, $URL) = $lists->loadListItemsByListId();

    echo "ttt</ul>";
else:
?>

            <img src="/assets/images/newlist.jpg" alt="Your new list here!" />

<?php endif; ?>

        </div>

This script checks if a user is logged in, then outputs their list and the proper controls if so. If not, we check if there was a list URL supplied and outputs a non-editable list if one is found. Otherwise, the "sales" page is displayed, encouraging the viewer to sign up.

Saving New List Items

At this point, our app will function properly for a user that is logged in. Now we just need to plug in the controls that will allow him or her to interact with the list. First, we need to allow for new items to be created. To do this, we need to write a PHP method that will add list items to our database, and then we need to complete the jQuery started by our designer in Part 6.

The PHP

Saving an item is simple enough on the server side. We simply grab all of the new item's information out of the $_POST superglobal, prepare a statement, and save the info in the database. Note that we're running strip_tags() on the list item's text. This is a redundant check since we're using JavaScript to remove any unwanted tags, but we shouldn't rely on data that was sanitized client-side.

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Adds a list item to the database
     *
     * @return mixed    ID of the new item on success, error message on failure
     */
    public function addListItem()
    {
        $list = $_POST['list'];
        $text = strip_tags(urldecode(trim($_POST['text'])), WHITELIST);
        $pos = $_POST['pos'];

        $sql = "INSERT INTO list_items
                    (ListID, ListText, ListItemPosition, ListItemColor)
                VALUES (:list, :text, :pos, 1)";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':list', $list, PDO::PARAM_INT);
            $stmt->bindParam(':text', $text, PDO::PARAM_STR);
            $stmt->bindParam(':pos', $pos, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();

            return $this->_db->lastInsertId();
        }
        catch(PDOException $e)
        {
            return $e->getMessage();
        }
    }
}

Notice that we used a constant called WHITELIST in the strip_tags() function. This is a list of allowed tags that our users have access to. However, we should assume that we'll want to change this list at some point in the future, which is why we're saving the list as a constant, which we'll define in inc/constants.inc.php:

    // HTML Whitelist
    define('WHITELIST', '<b><i><strong><em><a>');

Finishing the JavaScript

To complete the jQuery in js/lists.js, we need to modify the script with the code below:

    // AJAX style adding of list items
    $('#add-new').submit(function(){
        // HTML tag whitelist. All other tags are stripped.
        var $whitelist = '<b><i><strong><em><a>',
            forList = $("#current-list").val(),
            newListItemText = strip_tags(cleanHREF($("#new-list-item-text").val()), $whitelist),
            URLtext = escape(newListItemText),
            newListItemRel = $('#list li').size() 1;

        if(newListItemText.length > 0) {
            $.ajax({
                type: "POST",
                url: "db-interaction/lists.php",
                data: "action=add&list="   forList   "&text="   URLtext   "&pos="   newListItemRel,
                success: function(theResponse){
                  $("#list").append("<li color='1' class='colorBlue' rel='" newListItemRel "' id='"   theResponse   "'><span id="" theResponse "listitem" title='Click to edit...'>"   newListItemText   "</span><div class='draggertab tab'></div><div class='colortab tab'></div><div class='deletetab tab'></div><div class='donetab tab'></div></li>");
                  bindAllTabs("#list li[rel='" newListItemRel "'] span");
                  $("#new-list-item-text").val("");
                },
                error: function(){
                    // uh oh, didn't work. Error message?
                }
            });
        } else {
            $("#new-list-item-text").val("");
        }
        return false; // prevent default form submission
    });

We're completing the $.ajax() call by submitting the new item via the POST method to db-interation/lists.php. The successfully added item is then appended to our list, all without a page refresh.

Handling List Interactions

Our $.ajax() call sends to db-interaction/lists.php, which doesn't exist yet. This script acts as a switch that will determine what action is needed and execute the proper method. All requests are handled the same way, so let's just define the whole file here. Create new file called lists.php in the db-interaction folder and insert the following code into it:

<?php

session_start();

include_once "../inc/constants.inc.php";
include_once "../inc/class.lists.inc.php";

if(!empty($_POST['action'])
&& isset($_SESSION['LoggedIn'])
&& $_SESSION['LoggedIn']==1)
{
    $listObj = new ColoredListsItems();
    switch($_POST['action'])
    {
        case 'add':
            echo $listObj->addListItem();
            break;
        case 'update':
            $listObj->updateListItem();
            break;
        case 'sort':
            $listObj->changeListItemPosition();
            break;
        case 'color':
            echo $listObj->changeListItemColor();
            break;
        case 'done':
            echo $listObj->toggleListItemDone();
            break;
        case 'delete':
            echo $listObj->deleteListItem();
            break;
        default:
            header("Location: /");
            break;
    }
}
else
{
    header("Location: /");
    exit;
}

?>

Reordering List Items

Next, we need to allow users to save the order of their items after they've dragged and dropped them. This is definitely the most complex part of our whole app.

The PHP

Each item is assigned a position when it's read out of the database. This is the item's starting position. When it is dragged, it ends up in a new place in the list; we're going to call this new position it's current position.

When changeListItemPosition() is called, both the item's starting position and current position are passed, as well as the direction it moved. This is where it gets tricky.

Depending on the direction the item was moved, we set up one of two conditional queries. We select all the items in the current list with a position falling between the starting and current positions, then, using the CASE clause, increment or decrement their positions by 1 unless the item's position plus or minus one falls outside the range we've selected, at which point we set the item's position to the current position. In this way, we're able to avoid firing an individual query for each item, which could potentially cause a performance bottleneck.

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Changes the order of a list's items
     *
     * @return string    a message indicating the number of affected items
     */
    public function changeListItemPosition()
    {
        $listid = (int) $_POST['currentListID'];
        $startPos = (int) $_POST['startPos'];
        $currentPos = (int) $_POST['currentPos'];
        $direction = $_POST['direction'];

        if($direction == 'up')
        {
            /*
             * This query modifies all items with a position between the item's
             * original position and the position it was moved to. If the
             * change makes the item's position greater than the item's
             * starting position, then the query sets its position to the new
             * position. Otherwise, the position is simply incremented.
             */
            $sql = "UPDATE list_items
                    SET ListItemPosition=(
                        CASE
                            WHEN ListItemPosition 1>$startPos THEN $currentPos
                            ELSE ListItemPosition 1
                        END)
                    WHERE ListID=$listid
                    AND ListItemPosition BETWEEN $currentPos AND $startPos";
        }
        else
        {
            /*
             * Same as above, except item positions are decremented, and if the
             * item's changed position is less than the starting position, its
             * position is set to the new position.
             */
            $sql = "UPDATE list_items
                    SET ListItemPosition=(
                        CASE
                            WHEN ListItemPosition-1<$startPos THEN $currentPos
                            ELSE ListItemPosition-1
                        END)
                    WHERE ListID=$listid
                    AND ListItemPosition BETWEEN $startPos AND $currentPos";
        }

        $rows = $this->_db->exec($sql);
        echo "Query executed successfully. ",
            "Affected rows: $rows";
    }
}

Finishing the JavaScript

To call our method, we need to modify js/lists.js by adding a new function called saveListOrder():

function saveListOrder(itemID, itemREL){
    var i = 1,
        currentListID = $('#current-list').val();
    $('#list li').each(function() {
        if($(this).attr('id') == itemID) {
            var startPos = itemREL,
                currentPos = i;
            if(startPos < currentPos) {
                var direction = 'down';
            } else {
                var direction = 'up';
            }
            var postURL = "action=sort&currentListID=" currentListID
                 "&startPos=" startPos
                 "&currentPos=" currentPos
                 "&direction=" direction;

            $.ajax({
                type: "POST",
                url: "db-interaction/lists.php",
                data: postURL,
                success: function(msg) {
                    // Resets the rel attribute to reflect current positions
                    var count=1;
                    $('#list li').each(function() {
                        $(this).attr('rel', count);
                        count  ;
                    });
                },
                error: function(msg) {
                    // error handling here
                }
            });
        }
        i  ;
    });
}

This function accepts the ID and rel attribute of the item that was moved. The rel attribute contains the original position of the item, which we need as its starting position. Then we loop through each list item while incrementing a counter (i). When we find the list item that matches the moved item's ID, our counter now reflects the item's current position. We can then determine which direction the item was moved and send the info to db-interaction/lists.php for processing.

This function needs to be called when a sortable item is updated, which we accomplish by modifying the following in js/lists.js:

    // MAKE THE LIST SORTABLE VIA JQUERY UI
    // calls the SaveListOrder function after a change
    // waits for one second first, for the DOM to set, otherwise it's too fast.
    $("#list").sortable({
        handle   : ".draggertab",
        update   : function(event, ui){
            var id = ui.item.attr('id');
            var rel = ui.item.attr('rel');
            var t = setTimeout("saveListOrder('" id "', '" rel "')",500);
        },
        forcePlaceholderSize: true
    });

Changing Item Colors

Changing an item's color is fairly simple on both the server- and client-side.

The PHP

To update an item's color, we simply pass it's ID and the new color code to the method changeListItemColor() and create and execute a query.

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Changes the color code of a list item
     *
     * @return mixed    returns TRUE on success, error message on failure
     */
    public function changeListItemColor()
    {
        $sql = "UPDATE list_items
                SET ListItemColor=:color
                WHERE ListItemID=:item
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':color', $_POST['color'], PDO::PARAM_INT);
            $stmt->bindParam(':item', $_POST['id'], PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            return TRUE;
        } catch(PDOException $e) {
            return $e->getMessage();
        }
    }
}

Finishing the JavaScript

The function that saves new colors is called by submitting the item ID and new color via POST in the $.ajax() call below in js/lists.js:

    // COLOR CYCLING
    // Does AJAX save, but no visual feedback
    $(".colortab").live("click", function(){
        $(this).parent().nextColor();

        var id = $(this).parent().attr("id"),
            color = $(this).parent().attr("color");

        $.ajax({
            type: "POST",
            url: "db-interaction/lists.php",
            data: "action=color&id="   id   "&color="   color,
            success: function(msg) {
                // error message
            }
        });
    });

Editing Item Text

Next, let's make sure edited items are updated in the database.

The PHP

To save updated items in the database, we need to create a method called updateListItem(). This method will extract the ID of the modified item and the new text from the $_POST superglobal, double-check the item text for disallowed tags, and prepare and execute a query to update the item in the database. Add the following method in inc/class.lists.inc.php:

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Updates the text for a list item
     *
     * @return string    Sanitized saved text on success, error message on fail
     */
    public function updateListItem()
    {
        $listItemID = $_POST["listItemID"];
        $newValue = strip_tags(urldecode(trim($_POST["value"])), WHITELIST);

        $sql = "UPDATE list_items
                SET ListText=:text
                WHERE ListItemID=:id
                LIMIT 1";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(':text', $newValue, PDO::PARAM_STR);
            $stmt->bindParam(':id', $listItemID, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();

            echo $newValue;
        } else {
            echo "Error saving, sorry about that!";
        }
    }
}

Finishing the JavaScript

Activate this method by modifying the path in bindAllTabs() in js/lists.js:

// This is seperated to a function so that it can be called at page load
// as well as when new list items are appended via AJAX
function bindAllTabs(editableTarget) {

    // CLICK-TO-EDIT on list items
    $(editableTarget).editable("db-interaction/lists.php", {
        id        : 'listItemID',
        indicator : 'Saving...',
        tooltip   : 'Double-click to edit...',
        event     : 'dblclick',
        submit    : 'Save',
        submitdata: {action : "update"}
    });

}

Marking Items as "Done"

To mark an item as done, the user needs to be able to save a flag in the database that will indicate the item's "done" status.

The PHP

The toggleListItemDone() method retrieves the item's ID and "done" status from the $_POST superglobal and uses them to update the item in the database:

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Changes the ListItemDone state of an item
     *
     * @return mixed    returns TRUE on success, error message on failure
     */
    public function toggleListItemDone()
    {
        $sql = "UPDATE list_items
                SET ListItemDone=:done
                WHERE ListItemID=:item
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':done', $_POST['done'], PDO::PARAM_INT);
            $stmt->bindParam(':item', $_POST['id'], PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
            return TRUE;
        } catch(PDOException $e) {
            return $e->getMessage();
        }
    }
}

Finishing the JavaScript

To call our method, we write a function called toggleDone() in js/lists.js. This function simply executes a call to the $.ajax() function and sends the item ID and "done" status to our list handler.

    function toggleDone(id, isDone)
    {
        $.ajax({
            type: "POST",
            url: "db-interaction/lists.php",
            data: "action=done&id=" id "&done=" isDone
        })
    }

Next, we assign toggleDone() as the callback function for the animate() even that happens when our user clicks the done tab:

    $(".donetab").live("click", function() {
        var id = $(this).parent().attr('id');
        if(!$(this).siblings('span').children('img.crossout').length)
        {
            $(this)
                .parent()
                    .find("span")
                    .append("<img src='/images/crossout.png' class='crossout' />")
                    .find(".crossout")
                    .animate({
                        width: "100%"
                    })
                    .end()
                .animate({
                    opacity: "0.5"
                },
                "slow",
                "swing",
                toggleDone(id, 1));
        }
        else
        {
            $(this)
                .siblings('span')
                    .find('img.crossout')
                        .remove()
                        .end()
                    .animate({
                        opacity : 1
                    },
                    "slow",
                    "swing",
                    toggleDone(id, 0));

        }
    });

Deleting Items

Finally, we need to allow our users to delete items that they no longer want on their list.

The PHP

To delete an item, we need to create a method called deleteListItem() in inc/class.lists.inc.php. This method will retrieve the item and list IDs from the $_POST superglobal, then remove the item from the list. Then, to preserve proper order in the list, all items in the list with a position higher than that of the item that was deleted need to be decremented by 1.

class ColoredListsItems
{
    // Class properties and other methods omitted to save space


    /**
     * Removes a list item from the database
     *
     * @return string    message indicating success or failure
     */
    public function deleteListItem()
    {
        $list = $_POST['list'];
        $item = $_POST['id'];

        $sql = "DELETE FROM list_items
                WHERE ListItemID=:item
                AND ListID=:list
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':item', $item, PDO::PARAM_INT);
            $stmt->bindParam(':list', $list, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();

            $sql = "UPDATE list_items
                    SET ListItemPosition=ListItemPosition-1
                    WHERE ListID=:list
                    AND ListItemPosition>:pos";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(':list', $list, PDO::PARAM_INT);
                $stmt->bindParam(':pos', $_POST['pos'], PDO::PARAM_INT);
                $stmt->execute();
                $stmt->closeCursor();
                return "Success!";
            }
            catch(PDOException $e)
            {
                return $e->getMessage();
            }
        }
        catch(Exception $e)
        {
            return $e->getMessage();
        }
    }
}

Finishing the JavaScript

To activate this method, we need to modify our jQuery by updating the section in js/lists.js that deals with item deletion:

    // AJAX style deletion of list items
    $(".deletetab").live("click", function(){
        var thiscache = $(this),
            list = $('#current-list').val(),
            id = thiscache.parent().attr("id"),
            pos = thiscache.parents('li').attr('rel');

        if (thiscache.data("readyToDelete") == "go for it") {
            $.ajax({
                type: "POST",
                url: "db-interaction/lists.php",
                data: {
                        "list":list,
                        "id":id,
                        "action":"delete",
                        "pos":pos
                    },
                success: function(r){
                        var $li = $('#list').children('li'),
                            position = 0;
                        thiscache
                            .parent()
                                .hide("explode", 400, function(){$(this).remove()});
                        $('#list')
                            .children('li')
                                .not(thiscache.parent())
                                .each(function(){
                                        $(this).attr('rel',   position);
                                    });
                    },
                error: function() {
                    $("#main").prepend("Deleting the item failed...");
                }
            });
        }
        else
        {
            thiscache.animate({
                width: "44px",
                right: "-64px"
            }, 200)
            .data("readyToDelete", "go for it");
        }
    });

Moving On

We have now succeeded in building all of the AJAX functionality for our app! There was a ton of information in this article, and we went through it rather quickly, so please post any questions you have in the comments!

In the final installment of this series, we'll go over the security measures and other finishing touches this app needs to be ready for public use. We'll also go over some of the features we hope to add in the future.

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.

November 25, 2009 - 79 comments

Creating an App from Scratch: Part 5

Posted by Jason Lengstorf in Development
Creating a Web App from Scratch Part 5

Where Are We?

Now that we have a workflow put together and the HTML and CSS to make it look good, we can actually start building the classes that will run this puppy.

We'll focus this installment of the series on the user's account interactions. These include:

  • Creating an Account
  • Modifying Account Information
  • Resetting a Lost Password
  • Deleting an Account

Connecting to the Database

Before our class will be able to do much of anything, we need to connect to our database. To do this, we'll need to create a couple of small files.

Defining Site-Wide Constants

Our site won't require many constants, but in the interest of keeping them easy to maintain, we'll create a separate file to contain any information that is site-wide. This will be called constants.inc.php, and it will reside in a new folder called inc — this folder will contain our PHP classes as well.

Creating a constants file is a good idea for pieces of information that will be used often and in different scopes throughout a site. That way, if your database changes, you're able to change every database connection simply by swapping out the information in one file.

Inside constants.inc.php, we want to define our database credentials. Since we're starting out by developing locally, constants.inc.php will look like this:

<?php

    // Database credentials
    define('DB_HOST', 'localhost');
    define('DB_USER', 'root');
    define('DB_PASS', '');
    define('DB_NAME', 'cl_db');

?>

As we develop, we'll add more to this file.

Creating a PDO Object

Next, we want to create a connection so that our application can communicate with our database. This file will reside in the common folder along with the header, footer, and sidebar files. This file will create a database connection using PDO (PHP Data Objects), as well as setting up a couple other site-wide features: error reporting and opening a session.

The file will look like this when all's said and done:

<?php
    // Set the error reporting level
    error_reporting(E_ALL);
    ini_set("display_errors", 1);

    // Start a PHP session
    session_start();

    // Include site constants
    include_once "inc/constants.inc.php";

    // Create a database object
    try {
        $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
        $db = new PDO($dsn, DB_USER, DB_PASS);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
        exit;
    }
?>

Because we're in the development stage, we want to see any and every error that occurs on the site. By setting error_reporting() to E_ALL and changing the display_errors directive to 1 using ini_set(), we ensure that even notices will be displayed, which will keep our code cleaner and more secure.

Next, we use session_start() to start a PHP session. This will allow our users to stay logged in when we build that functionality later.

Finally, we include config.inc.php and create a PDO object using the constants defined within it. Note the use of the try-catch statement—this gives us the ability to use Exceptions, which help improve error handling. In this case, if the database connection fails, we're simply going to output the error message.

Why PDO?

The reason we're using PDO for this project is because of its support for prepared statements, which virtually eliminates the risk of SQL injection. There are other options that allow prepared statements, such as the MySQLi extension. However, PDO is not database-specific, so migrating the app to Oracle or PostgreSQL wouldn't require a full rewrite of our code.

Also, having used both MySQLi and PDO in projects, it's just my personal preference to use PDO. Feel free to use whatever method of connecting to the database you prefer, but keep in mind that all database interactions in this exercise are assuming the use of PDO, and as such will probably require some reworking to accommodate your changes.

Framing Out a User Interactions Class

As we discussed in Part 2 of this series, we'll be taking the object-oriented approach with this app. All of these actions will be contained within our ColoredListsUsers class. We'll also need to create several files that will display information to the user and interact with the class, which we'll cover as we get to them.

Building the Class

To get started, we need to create the file class.users.inc.php to contain the PHP class, which we'll place in the inc folder.

With the file created, let's build the skeleton of the class:

<?php

/**
 * Handles user interactions within the app
 *
 * PHP version 5
 *
 * @author Jason Lengstorf
 * @author Chris Coyier
 * @copyright 2009 Chris Coyier and Jason Lengstorf
 * @license   http://www.opensource.org/licenses/mit-license.html  MIT License
 *
 */
class ColoredListsUsers
{



}


?>

Connecting the Class to the Database

Before our class can do much of anything, it needs to have access to the database object we created in base.php. Our database connection within the object will be stored in a private property called $_db, and this property will be set by the class constructor, which will accept the instance of PDO created in base.php as an argument. If no instance of PDO is passed, one will be created by the constructor.

This ends up looking like this:

class ColoredListsUsers
{
    /**
     * The database object
     *
     * @var object
     */
    private $_db;

    /**
     * Checks for a database object and creates one if none is found
     *
     * @param object $db
     * @return void
     */
    public function __construct($db=NULL)
    {
        if(is_object($db))
        {
            $this->_db = $db;
        }
        else
        {
            $dsn = "mysql:host=".DB_HOST.";dbname=".DB_NAME;
            $this->_db = new PDO($dsn, DB_USER, DB_PASS);
        }
    }
}

Now we are able to create an instance of our ColoredListsUsers object and use it to communicate with our database. Next, let's start building user interactions!

Creating an Account

First and foremost, a user needs to be able to create an account. This will give them access to the rest of the site's functionality.

As it stands, when a user visits our app, they're greeted with our "sales" page, which encourages them to click the "Sign Up" button in the top right of their screen:

App home screen The home screen of our app

Clicking that "Sign Up" button directs the user to /signup.php—our first order of business should probably be to build that page.

Creating the Sign-Up Form

In our app's root directory, create a file called signup.php and place the following code inside:

<?php
    include_once "common/base.php";
    $pageTitle = "Register";
    include_once "common/header.php";

    if(!empty($_POST['username'])):
        include_once "inc/class.users.inc.php";
        $users = new ColoredListsUsers($db);
        echo $users->createAccount();
    else:
?>

        <h2>Sign up</h2>
        <form method="post" action="signup.php" id="registerform">
            <div>
                <label for="username">Email:</label>
                <input type="text" name="username" id="username" /><br />
                <input type="submit" name="register" id="register" value="Sign up" />
            </div>
        </form>

<?php
    endif;
    include_once 'common/close.php';
?>

To start, we include our common/base.php and common/header.php files. Also, notice that we're declaring a variable called $pageTitle just before we include the header. Remember in Part 4 when we built the header file and left that comment in the title tag?

<title>Colored Lists | <!-- Do Something Smart Here --></title>

We're going to replace that with a snippet of PHP that reads:

<title>Colored Lists | <?php echo $pageTitle ?></title>

That gives us the opportunity to post a different title for each page of our app.

With the proper files included, we can then create our sign-up form. The form will submit to signup.php—itself—so we need to place an if-else check to see if the form has been submitted. If so, we create a new ColoredListsUsers object and call the createAccount() method (which we'll write in the next section).

Finally, we close the if-else statement and include the footer. Our sign-up page should look like this:

The sign-up page The sign-up page.

Notice the use of alternative syntax for the if-else statement. Normally, I don't like to use this format, but in the case of outputting HTML, I prefer the way it ends with endif; instead of a closing curly brace (}), which helps with readability in the script.

Saving the User's Email Address

With our sign-up form ready, we need to write the createAccount() method that will be called when a user submits the form. This method will be public. Let's go back to inc/class.users.inc.php and declare this method:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space

    /**
     * Checks and inserts a new account email into the database
     *
     * @return string    a message indicating the action status
     */
    public function createAccount()
    {
        $u = trim($_POST['username']);
        $v = sha1(time());

        $sql = "SELECT COUNT(Username) AS theCount
                FROM users
                WHERE Username=:email";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(":email", $u, PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetch();
            if($row['theCount']!=0) {
                return "<h2> Error </h2>"
                    . "<p> Sorry, that email is already in use. "
                    . "Please try again. </p>";
            }
            if(!$this->sendVerificationEmail($u, $v)) {
                return "<h2> Error </h2>"
                    . "<p> There was an error sending your"
                    . " verification email. Please "
                    . "<a href="mailto:help@coloredlists.com">contact "
                    . "us</a> for support. We apologize for the "
                    . "inconvenience. </p>";
            }
            $stmt->closeCursor();
        }

        $sql = "INSERT INTO users(Username, ver_code)
                VALUES(:email, :ver)";
        if($stmt = $this->_db->prepare($sql)) {
            $stmt->bindParam(":email", $u, PDO::PARAM_STR);
            $stmt->bindParam(":ver", $v, PDO::PARAM_STR);
            $stmt->execute();
            $stmt->closeCursor();

            $userID = $this->_db->lastInsertId();
            $url = dechex($userID);

            /*
             * If the UserID was successfully
             * retrieved, create a default list.
             */
            $sql = "INSERT INTO lists (UserID, ListURL)
                    VALUES ($userID, $url)";
            if(!$this->_db->query($sql)) {
                return "<h2> Error </h2>"
                    . "<p> Your account was created, but "
                    . "creating your first list failed. </p>";
            } else {
                return "<h2> Success! </h2>"
                    . "<p> Your account was successfully "
                    . "created with the username <strong>$u</strong>."
                    . " Check your email!";
            }
        } else {
            return "<h2> Error </h2><p> Couldn't insert the "
                . "user information into the database. </p>";
        }
    }
}

This method follows several steps to create an account: first, it retrieves the posted email address from the form (stored in the $_POST superglobal) and generates a hard-to-guess verification code (the SHA1 hash of the current timestamp); second, it makes sure the supplied email address isn't already in use; third, it generates and sends a verification email to the user with instructions on how to verify their account (we'll define the method that does this in the next section); fourth, it stores the email address and verification code in the database; and finally, it creates a list for the user.

Each of these steps is monitored, and if any of them should fail, a specific error message is generated. Upon success, a message is generated to let the user know they should expect an email.

Generating and Sending a Verification Email

When the user creates an account, we need to send them an email with a link that will confirm their account. This is a precautionary measure that proves the user provided a real email address that they have access to and prevents a ton of spam accounts from being created easily.

To send the email, we'll be using the built-in mail() function. In inc/class.users.inc.php, create the private sendVerificationEmail() method by inserting the following code:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space

    /**
     * Sends an email to a user with a link to verify their new account
     *
     * @param string $email    The user's email address
     * @param string $ver    The random verification code for the user
     * @return boolean        TRUE on successful send and FALSE on failure
     */
    private function sendVerificationEmail($email, $ver)
    {
        $e = sha1($email); // For verification purposes
        $to = trim($email);

        $subject = "[Colored Lists] Please Verify Your Account";

        $headers = <<<MESSAGE
From: Colored Lists <donotreply@coloredlists.com>
Content-Type: text/plain;
MESSAGE;

        $msg = <<<EMAIL
You have a new account at Colored Lists!

To get started, please activate your account and choose a
password by following the link below.

Your Username: $email

Activate your account: http://coloredlists.com/accountverify.php?v=$ver&e=$e

If you have any questions, please contact help@coloredlists.com.

--
Thanks!

Chris and Jason
www.ColoredLists.com
EMAIL;

        return mail($to, $subject, $msg, $headers);
    }
}

The most important part of this method is the activation link, http://coloredlists.com/accountverify.php?v=$ver&e=$e. This link sends the user to our app's account verification file (which we'll write in the next step) and sends the user's hashed email address along with their verification code in the URI. This will allow us to identify and verify the user when they follow the link.

Verifying the User's Account

After our user follows the verification link in the email, we need to check that their email and verification code are valid, and then allow them to choose a password. After they choose a password, we need to update the database to reflect the user's new password, as well as setting the account's status to verified.

First, let's create a new file called accountverify.php in the root level of our app. Inside, place the following code:

<?php
    include_once "common/base.php";
    $pageTitle = "Verify Your Account";
    include_once "common/header.php";

    if(isset($_GET['v']) && isset($_GET['e']))
    {
        include_once "inc/class.users.inc.php";
        $users = new ColoredListsUsers($db);
        $ret = $users->verifyAccount();
    }
    elseif(isset($_POST['v']))
    {
        include_once "inc/class.users.inc.php";
        $users = new ColoredListsUsers($db);
        $ret = $users->updatePassword();
    }
    else
    {
        header("Location: /signup.php");
        exit;
    }

    if(isset($ret[0])):
        echo isset($ret[1]) ? $ret[1] : NULL;

        if($ret[0]<3):
?>

        <h2>Choose a Password</h2>

        <form method="post" action="accountverify.php">
            <div>
                <label for="p">Choose a Password:</label>
                <input type="password" name="p" id="p" /><br />
                <label for="r">Re-Type Password:</label>
                <input type="password" name="r" id="r" /><br />
                <input type="hidden" name="v" value="<?php echo $_GET['v'] ?>" />
                <input type="submit" name="verify" id="verify" value="Verify Your Account" />
            </div>
        </form>

<?php
        endif;
    else:
        echo '<meta http-equiv="refresh" content="0;/">';
    endif;

    include_once("common/ads.php");
    include_once 'common/close.php';
?>

Verifying the User's Email and Verification Code

Before we can allow our user to select a password, we need to make sure that their account exists, that their email matches their verification code, and that their account is unverified. To do that, we need a new method in inc/class.users.inc.php called verifyAccount():

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space

    /**
     * Checks credentials and verifies a user account
     *
     * @return array    an array containing a status code and status message
     */
    public function verifyAccount()
    {
        $sql = "SELECT Username
                FROM users
                WHERE ver_code=:ver
                AND SHA1(Username)=:user
                AND verified=0";

        if($stmt = $this->_db->prepare($sql))
        {
            $stmt->bindParam(':ver', $_GET['v'], PDO::PARAM_STR);
            $stmt->bindParam(':user', $_GET['e'], PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetch();
            if(isset($row['Username']))
            {
                // Logs the user in if verification is successful
                $_SESSION['Username'] = $row['Username'];
                $_SESSION['LoggedIn'] = 1;
            }
            else
            {
                return array(4, "<h2>Verification Error</h2>n"
                    . "<p>This account has already been verified. "
                    . "Did you <a href="/password.php">forget "
                    . "your password?</a>");
            }
            $stmt->closeCursor();

            // No error message is required if verification is successful
            return array(0, NULL);
        }
        else
        {
            return array(2, "<h2>Error</h2>n<p>Database error.</p>");
        }
    }
}

This method executes a query that loads the user name stored in the database with the verification code, hashed user name, and a verified status of 0. If a user name is returned, login credentials are stored. This method returns an array with an error code in the first index, and a message in the second. The error code 0 means nothing went wrong.

Updating the User's Password and Verified Status

Once the user has selected a password and submitted the form, the if-else statement will catch the verification code sent using the POST method and execute the updatePassword() method. This method needs to set the account status to verified and save the user's hashed password in the database. Let's build this method in ColoredListsUsers:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space

    /**
     * Changes the user's password
     *
     * @return boolean    TRUE on success and FALSE on failure
     */
    public function updatePassword()
    {
        if(isset($_POST['p'])
        && isset($_POST['r'])
        && $_POST['p']==$_POST['r'])
        {
            $sql = "UPDATE users
                    SET Password=MD5(:pass), verified=1
                    WHERE ver_code=:ver
                    LIMIT 1";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(":pass", $_POST['p'], PDO::PARAM_STR);
                $stmt->bindParam(":ver", $_POST['v'], PDO::PARAM_STR);
                $stmt->execute();
                $stmt->closeCursor();

                return TRUE;
            }
            catch(PDOException $e)
            {
                return FALSE;
            }
        }
        else
        {
            return FALSE;
        }
    }
}

Finally, since verifying an account logs a user in, we need to update common/header.php to recognize that a user is logged in and display different options. In Part 4, common/header.php featured a code snippet that looked like this:

<!-- IF LOGGED IN -->
                <p><a href="/logout.php" class="button">Log out</a> <a href="/account.php" class="button">Your Account</a></p>

<!-- IF LOGGED OUT -->
                <p><a class="button" href="/signup.php">Sign up</a> &nbsp; <a class="button" href="/login.php">Log in</a></p>
<!-- END OF IF STATEMENT -->

To make those comments into functional code, we need to modify this snippet with an if-else block:

<?php
    if(isset($_SESSION['LoggedIn']) && isset($_SESSION['Username'])
        && $_SESSION['LoggedIn']==1):
?>
                <p><a href="/logout.php" class="button">Log out</a> <a href="/account.php" class="button">Your Account</a></p>
<?php else: ?>
                <p><a class="button" href="/signup.php">Sign up</a> &nbsp; <a class="button" href="/login.php">Log in</a></p>
<?php endif; ?>

Notice that we store in the session both the user name ($_SESSION['Username']) and a flag that tells us if the user is logged in ($_SESSION['LoggedIn']).

Logging In

Next, let's build the login form and allow our user to log in. To start, let's create a new file named login.php at the root level of our app. Like our other publicly displayed files, this will include the base and header files. Then it checks if a user is already logged in, if the login form was submitted, or if the user needs to log in.

If logged in, the user is notified of this fact and asked if he or she wishes to log out.

If the form has been submitted, a new ColoredListsUsers object is created and the accountLogin() method is called. If the login succeeds, the user is directed to the home page, where his or her list will appear; otherwise, the login form is displayed again with an error.

If neither of the previous conditions exists, the login form is displayed.

Finally, the sidebar ads and footer are included to round out the file.

When the file is all put together, it should look like this:

<?php
    include_once "common/base.php";
    $pageTitle = "Home";
    include_once "common/header.php";

    if(!empty($_SESSION['LoggedIn']) && !empty($_SESSION['Username'])):
?>

        <p>You are currently <strong>logged in.</strong></p>
        <p><a href="/logout.php">Log out</a></p>
<?php
    elseif(!empty($_POST['username']) && !empty($_POST['password'])):
        include_once 'inc/class.users.inc.php';
        $users = new ColoredListsUsers($db);
        if($users->accountLogin()===TRUE):
            echo "<meta http-equiv='refresh' content='0;/'>";
            exit;
        else:
?>

        <h2>Login Failed&mdash;Try Again?</h2>
        <form method="post" action="login.php" name="loginform" id="loginform">
            <div>
                <input type="text" name="username" id="username" />
                <label for="username">Email</label>
                <br /><br />
                <input type="password" name="password" id="password" />
                <label for="password">Password</label>
                <br /><br />
                <input type="submit" name="login" id="login" value="Login" class="button" />
            </div>
        </form>
        <p><a href="/password.php">Did you forget your password?</a></p>
<?php
        endif;
    else:
?>

        <h2>Your list awaits...</h2>
        <form method="post" action="login.php" name="loginform" id="loginform">
            <div>
                <input type="text" name="username" id="username" />
                <label for="username">Email</label>
                <br /><br />
                <input type="password" name="password" id="password" />
                <label for="password">Password</label>
                <br /><br />
                <input type="submit" name="login" id="login" value="Login" class="button" />
            </div>
        </form><br /><br />
        <p><a href="/password.php">Did you forget your password?</a></p>
<?php
    endif;
?>

        <div style="clear: both;"></div>
<?php
    include_once "common/ads.php";
    include_once "common/close.php";
?>

Notice the "Did you forget your password?" links — we'll be building this functionality a little later on in the article.

Building the Login Method

Now we need to build the accountLogin() method. This method will compare the supplied user name and the MD5 hash of the supplied password to verify that there is a matching pair in the database. If a match is found, the user's name and a login flag are stored in the session and the method returns TRUE. If no match is found, the method returns FALSE.

Build this method in ColoredListsUsers by inserting the following code:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Checks credentials and logs in the user
     *
     * @return boolean    TRUE on success and FALSE on failure
     */
    public function accountLogin()
    {
        $sql = "SELECT Username
                FROM users
                WHERE Username=:user
                AND Password=MD5(:pass)
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':user', $_POST['username'], PDO::PARAM_STR);
            $stmt->bindParam(':pass', $_POST['password'], PDO::PARAM_STR);
            $stmt->execute();
            if($stmt->rowCount()==1)
            {
                $_SESSION['Username'] = htmlentities($_POST['username'], ENT_QUOTES);
                $_SESSION['LoggedIn'] = 1;
                return TRUE;
            }
            else
            {
                return FALSE;
            }
        }
        catch(PDOException $e)
        {
            return FALSE;
        }
    }
}

Logging Out

Next, our user needs to be able to log out. This is as easy as destroying the login data stored in the session and sending the user back to the login page.

Create a new file named logout.php at the root level of the app and place the following code inside:

<?php

    session_start();

    unset($_SESSION['LoggedIn']);
    unset($_SESSION['Username']);

?>

<meta http-equiv="refresh" content="0;login.php">

Modifying Account Information

Next, we need to allow our users to modify their account information. In order to do that, we need to provide an "Account" page that will give them options to change their user name or password, as well as the option to delete their account.

Create a file named account.php at the root level of the app. There's a lot going on here because we're essentially combining three app functions within one file.

First, we include the base file and check that the user is logged in. If not, he or she gets sent out to the main page.

If the user is logged in, we check if any actions have already been attempted and assemble the corresponding success or failure messages if any are found.

Then we load the user's ID and verification code using the method retrieveAccountInfo() and build three forms: one to update the user name (which is an email address, remember), one to change the account password, and one to delete the account.

Finally, we include the sidebar ads and the footer. Altogether, the file should look like this:

<?php
    include_once "common/base.php";
    if(isset($_SESSION['LoggedIn']) && $_SESSION['LoggedIn']==1):
        $pageTitle = "Your Account";
        include_once "common/header.php";
        include_once 'inc/class.users.inc.php';
        $users = new ColoredListsUsers($db);

        if(isset($_GET['email']) && $_GET['email']=="changed")
        {
            echo "<div class='message good'>Your email address "
                . "has been changed.</div>";
        }
        else if(isset($_GET['email']) && $_GET['email']=="failed")
        {
            echo "<div class='message bad'>There was an error "
                . "changing your email address.</div>";
        }

        if(isset($_GET['password']) && $_GET['password']=="changed")
        {
            echo "<div class='message good'>Your password "
                . "has been changed.</div>";
        }
        elseif(isset($_GET['password']) && $_GET['password']=="nomatch")
        {
            echo "<div class='message bad'>The two passwords "
                . "did not match. Try again!</div>";
        }

        if(isset($_GET['delete']) && $_GET['delete']=="failed")
        {
            echo "<div class='message bad'>There was an error "
                . "deleting your account.</div>";
        }

        list($userID, $v) = $users->retrieveAccountInfo();
?>

        <h2>Your Account</h2>
        <form method="post" action="db-interaction/users.php">
            <div>
                <input type="hidden" name="userid"
                    value="<?php echo $userID ?>" />
                <input type="hidden" name="action"
                    value="changeemail" />
                <input type="text" name="username" id="username" />
                <label for="username">Change Email Address</label>
                <br /><br />
                <input type="submit" name="change-email-submit"
                    id="change-email-submit" value="Change Email"
                    class="button" />
            </div>
        </form><br /><br />

        <form method="post" action="db-interaction/users.php"
            id="change-password-form">
            <div>
                <input type="hidden" name="user-id"
                    value="<?php echo $userID ?>" />
                <input type="hidden" name="v"
                    value="<?php echo $v ?>" />
                <input type="hidden" name="action"
                    value="changepassword" />
                <input type="password"
                    name="p" id="new-password" />
                <label for="password">New Password</label>
                <br /><br />
                <input type="password" name="r"
                    id="repeat-new-password" />
                <label for="password">Repeat New Password</label>
                <br /><br />
                <input type="submit" name="change-password-submit"
                    id="change-password-submit" value="Change Password"
                    class="button" />
            </div>
        </form>
        <hr />

        <form method="post" action="deleteaccount.php"
            id="delete-account-form">
            <div>
                <input type="hidden" name="user-id"
                    value="<?php echo $userID ?>" />
                <input type="submit"
                    name="delete-account-submit" id="delete-account-submit"
                    value="Delete Account?" class="button" />
            </div>
        </form>

<?php
    else:
        header("Location: /");
        exit;
    endif;
?>

<div class="clear"></div>

<?php
    include_once "common/ads.php";
    include_once "common/close.php";
?>

Creating the Method to Retrieve Account Info

In order to have the user's login name and verification code available to our account option forms, we need to build a new method that will load this information from the database. In inc/class.users.inc.php, create a new method in ColoredListsUsers called retrieveAccountInfo() and add the following code:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Retrieves the ID and verification code for a user
     *
     * @return mixed    an array of info or FALSE on failure
     */
    public function retrieveAccountInfo()
    {
        $sql = "SELECT UserID, ver_code
                FROM users
                WHERE Username=:user";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':user', $_SESSION['Username'], PDO::PARAM_STR);
            $stmt->execute();
            $row = $stmt->fetch();
            $stmt->closeCursor();
            return array($row['UserID'], $row['ver_code']);
        }
        catch(PDOException $e)
        {
            return FALSE;
        }
    }
}

Building the Interactions File

In account.php, all three forms direct to a file called db-interaction/users.php when submitted. This file helps relieve some of the clutter in account.php by determining form actions, creating a ColoredListsUsers object, and calling the appropriate methods to handle the action.

This file will be placed in a new folder called db-interaction, and it will be named users.php. Place the following code in the new file:

<?php

session_start();

include_once "../inc/constants.inc.php";
include_once "../inc/class.users.inc.php";
$userObj = new ColoredListsUsers();

if(!empty($_POST['action'])
&& isset($_SESSION['LoggedIn'])
&& $_SESSION['LoggedIn']==1)
{
    switch($_POST['action'])
    {
        case 'changeemail':
            $status = $userObj->updateEmail() ? "changed" : "failed";
            header("Location: /account.php?email=$status");
            break;
        case 'changepassword':
            $status = $userObj->updatePassword() ? "changed" : "nomatch";
            header("Location: /account.php?password=$status");
            break;
        case 'deleteaccount':
            $userObj->deleteAccount();
            break;
        default:
            header("Location: /");
            break;
    }
}
elseif($_POST['action']=="resetpassword")
{
    if($resp=$userObj->resetPassword()===TRUE)
    {
        header("Location: /resetpending.php");
    }
    else
    {
        echo $resp;
    }
    exit;
}
else
{
    header("Location: /");
    exit;
}

?>

Updating the Email Address

When a user submits a request to change their email address, the method updateEmail() is called. This function simply executes a query that changes the email address associated with an account. It returns TRUE if the email is successfully changed, and FALSE otherwise.

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Changes a user's email address
     *
     * @return boolean    TRUE on success and FALSE on failure
     */
    public function updateEmail()
    {
        $sql = "UPDATE users
                SET Username=:email
                WHERE UserID=:user
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(':email', $_POST['username'], PDO::PARAM_STR);
            $stmt->bindParam(':user', $_POST['userid'], PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();

            // Updates the session variable
            $_SESSION['Username'] = htmlentities($_POST['username'], ENT_QUOTES);

            return TRUE;
        }
        catch(PDOException $e)
        {
            return FALSE;
        }
    }
}

Updating the Password

Quite similarly to updateEmail(), updatePassword() is called if the user submits a request to change their password. The only difference in the methods is that this one will compare the password and the password confirmation to make sure they match before saving.

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Changes the user's password
     *
     * @return boolean    TRUE on success and FALSE on failure
     */
    public function updatePassword()
    {
        if(isset($_POST['p'])
        && isset($_POST['r'])
        && $_POST['p']==$_POST['r'])
        {
            $sql = "UPDATE users
                    SET Password=MD5(:pass), verified=1
                    WHERE ver_code=:ver
                    LIMIT 1";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(":pass", $_POST['p'], PDO::PARAM_STR);
                $stmt->bindParam(":ver", $_POST['v'], PDO::PARAM_STR);
                $stmt->execute();
                $stmt->closeCursor();

                return TRUE;
            }
            catch(PDOException $e)
            {
                return FALSE;
            }
        }
        else
        {
            return FALSE;
        }
    }
}

Deleting the Account

If the user wants to delete their account, we need to go through several steps. First, we need to double-check that the user is logged in, because we certainly don't want any accidental account deletions. If the user is logged in, we then delete their list items. If the list items are successfully deleted, we move on to delete the user's lists. Finally, if the lists are successfully deleted, we delete the user from the database, destroy their session information, and send them to a page called gone.php, which we'll build in a minute.

The method, when it's all written, will look like this:

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Deletes an account and all associated lists and items
     *
     * @return void
     */
    public function deleteAccount()
    {
        if(isset($_SESSION['LoggedIn']) && $_SESSION['LoggedIn']==1)
        {
            // Delete list items
            $sql = "DELETE FROM list_items
                    WHERE ListID=(
                        SELECT ListID
                        FROM lists
                        WHERE UserID=:user
                        LIMIT 1
                    )";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(":user", $_POST['user-id'], PDO::PARAM_INT);
                $stmt->execute();
                $stmt->closeCursor();
            }
            catch(PDOException $e)
            {
                die($e->getMessage());
            }

            // Delete the user's list(s)
            $sql = "DELETE FROM lists
                    WHERE UserID=:user";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(":user", $_POST['user-id'], PDO::PARAM_INT);
                $stmt->execute();
                $stmt->closeCursor();
            }
            catch(PDOException $e)
            {
                die($e->getMessage());
            }

            // Delete the user
            $sql = "DELETE FROM users
                    WHERE UserID=:user
                    AND Username=:email";
            try
            {
                $stmt = $this->_db->prepare($sql);
                $stmt->bindParam(":user", $_POST['user-id'], PDO::PARAM_INT);
                $stmt->bindParam(":email", $_SESSION['Username'], PDO::PARAM_STR);
                $stmt->execute();
                $stmt->closeCursor();
            }
            catch(PDOException $e)
            {
                die($e->getMessage());
            }

            // Destroy the user's session and send to a confirmation page
            unset($_SESSION['LoggedIn'], $_SESSION['Username']);
            header("Location: /gone.php");
            exit;
        }
        else
        {
            header("Location: /account.php?delete=failed");
            exit;
        }
    }
}

Resetting an Account Password

At this point, we're almost done. The last thing we need to do is allow a user to reset a forgotten password. To do this, we need to create the file password.php at the root level of our app and place the following code inside:

<?php
    include_once "common/base.php";
    $pageTitle = "Reset Your Password";
    include_once "common/header.php";
?>

        <h2>Reset Your Password</h2>
        <p>Enter the email address you signed up with and we'll send
        you a link to reset your password.</p>

        <form action="db-interaction/users.php" method="post">
            <div>
                <input type="hidden" name="action"
                    value="resetpassword" />
                <input type="text" name="username" id="username" />
                <label for="username">Email</label><br /><br />
                <input type="submit" name="reset" id="reset"
                    value="Reset Password" class="button" />
            </div>
        </form>
<?php
    include_once "common/ads.php";
    include_once "common/close.php";
?>

When a user visits this page, they'll be able to enter their email address. Submitting the form will return the account to unverified and send the user an email with a link to reset their password.

Returning the Account to "Unverified" Status

When the form in password.php is submitted, the information is sent to db-interaction/users.php and the resetPassword() method is called before sending the user to resetpending.php.

The resetPassword() method sets the verified field of our user's database entry to 0, then calls the sendResetEmail() method.

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Resets a user's status to unverified and sends them an email
     *
     * @return mixed    TRUE on success and a message on failure
     */
    public function resetPassword()
    {
        $sql = "UPDATE users
                SET verified=0
                WHERE Username=:user
                LIMIT 1";
        try
        {
            $stmt = $this->_db->prepare($sql);
            $stmt->bindParam(":user", $_POST['username'], PDO::PARAM_STR);
            $stmt->execute();
            $stmt->closeCursor();
        }
        catch(PDOException $e)
        {
            return $e->getMessage();
        }

        // Send the reset email
        if(!$this->sendResetEmail($_POST['username'], $v))
        {
            return "Sending the email failed!";
        }
        return TRUE;
    }
}

Building the Reset Pending Page

After the user's account is back in an unverified state and the email has been sent with their password reset link, we send them to resetpending.php to let them know what their next steps are. Create this file at the root level of the app and insert the following:

<?php
    include_once "common/base.php";
    $pageTitle = "Reset Pending";
    include_once "common/header.php";
?>

        <h2>Password Reset Requested</h2>
        <p>Check your email to finish the reset process.</p>
<?php
    include_once "common/ads.php";
    include_once "common/close.php";
?>

Generating a "Reset Password" Email

The sendResetEmail() method is very similar to the sendVerificationEmail() method. The main difference here is that the link sent to the user directs them to a page called resetpassword.php where they're able to choose a new password.

class ColoredListsUsers
{
    // Class properties and other methods omitted to save space


    /**
     * Sends a link to a user that lets them reset their password
     *
     * @param string $email    the user's email address
     * @param string $ver    the user's verification code
     * @return boolean        TRUE on success and FALSE on failure
     */
    private function sendResetEmail($email, $ver)
    {
        $e = sha1($email); // For verification purposes
        $to = trim($email);

        $subject = "[Colored Lists] Request to Reset Your Password";

        $headers = <<<MESSAGE
From: Colored Lists <donotreply@coloredlists.com>
Content-Type: text/plain;
MESSAGE;

        $msg = <<<EMAIL
We just heard you forgot your password! Bummer! To get going again,
head over to the link below and choose a new password.

Follow this link to reset your password:
http://coloredlists.com/resetpassword.php?v=$ver&e=$e

If you have any questions, please contact help@coloredlists.com.

--
Thanks!

Chris and Jason
www.ColoredLists.com
EMAIL;

        return mail($to, $subject, $msg, $headers);
    }
}

Resetting the Password

Our very last step in this part of the app is to create the file resetpassword.php in the root level of the site. This file is very similar to the accountverify.php file we created earlier. After including the base and header files, it checks if the user is just arriving from their reset email.

If so, we are able to use the verifyAccount() method we wrote earlier to ensure that their credentials are correct. After verifying their credentials, we display a form that allows them to choose a password and confirm it.

After submitting the form, our script will fire the updatePassword() method we created earlier to save the new password. Then we redirect the user to account.php, where they're shown a confirmation message letting them know that their password was changed.

Inside resetpassword.php, add the following code:

<?php
    include_once "common/base.php";

    if(isset($_GET['v']) && isset($_GET['e']))
    {
        include_once "inc/class.users.inc.php";
        $users = new ColoredListsUsers($db);
        $ret = $users->verifyAccount();
    }
    elseif(isset($_POST['v']))
    {
        include_once "inc/class.users.inc.php";
        $users = new ColoredListsUsers($db);
        $status = $users->updatePassword() ? "changed" : "failed";
        header("Location: /account.php?password=$status");
        exit;
    }
    else
    {
        header("Location: /login.php");
        exit;
    }

    $pageTitle = "Reset Your Password";
    include_once "common/header.php";

    if(isset($ret[0])):
        echo isset($ret[1]) ? $ret[1] : NULL;

        if($ret[0]<3):
?>

        <h2>Reset Your Password</h2>

        <form method="post" action="accountverify.php">
            <div>
                <label for="p">Choose a New Password:</label>
                <input type="password" name="p" id="p" /><br />
                <label for="r">Re-Type Password:</label>
                <input type="password" name="r" id="r" /><br />
                <input type="hidden" name="v" value="<?php echo $_GET['v'] ?>" />
                <input type="submit" name="verify" id="verify" value="Reset Your Password" />
            </div>
        </form>

<?php
        endif;
    else:
        echo '<meta http-equiv="refresh" content="0;/">';
    endif;

    include_once("common/ads.php");
    include_once 'common/close.php';
?>

Moving On...

This article covered a whole lot of ground, and I went over some of it pretty quickly. Please don't hesitate to ask for clarification in the comments!

In the next part of this series, our front-end designer will use some dummy lists to create AJAX effects. After he's finished with the dummy lists, we'll explore how to combine those AJAX effects with our back-end and build the list interactions class in part 7.

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.

November 24, 2009 - 23 comments

Creating an App from Scratch: Part 3

Posted by Jason Lengstorf in Development
Creating a Web App from Scratch Part 3

Developing a Workflow

We have a great start going on our list application at this point. The "big idea" is in place, we know how we want the lists to be displayed and interacted with, and we have some back-end structure in place to deal with users and all the data that goes along with these lists.

It was a good idea to start with the "meat" of the app, but there is a little bit more that goes into a full application. Because we have users, that means we need a sign up form and a log in area for returning users. Because users can be forgetful, we need a 'Lost Password' feature. Because users should be just as concerned about security as we are, users need to be able to change their passwords, change their login, and delete their accounts. Our one-page app has just turned into a four or five page app, so we're going to need to think about some workflow.

There will be two different states for the homepage: logged in and logged out. While logged out, people need a way to sign in and to register, and this will be essentially the "sales" page too, explaining the app. Logged in, the homepage will be the user's list itself. Logged in users will also need to do some ancillary stuff related to their account, like change their email, change their password, and delete their account, as well as a way to log out. These ancillary options are probably best served on an account page. So now we are looking at at least two new pages: Account Settings and Registration. Here is some flow:

Basic Workflow Basic app workflow

It's not pretty folks, but that's what sketching is. It's fast and it's just to help you think and plan for the things you need.

Bringing It to Life Photoshop

Our developer is already ahead of us, thinking about the data they need and how this app is going to actually work. So we'd better get started actually designing here.

Homepage (Logged In)

This is the meat of our application so let's start here. The list is obviously the most important thing, so let's keep the header small and keep the list front and center. List items are big colored blocks with buttons for their associated actions nearby. Below the list a box for entering new list items.

Home page logged in The home page as it appears when logged in

Homepage (Logged Out)

When logged out, the homepage is going to act more like a "sales" page. Not that we plan to charge for it, but just to explain and get people interested in using it. There isn't much to say about a list app, so we'll keep it simple.

Sales page When logged out, we'll encourage the visitor to sign up

Small Bits

We've been designing long enough to know we might as well make the little buttons into a separate file and keep them together as a sprite (a sprite is multiple images combined into one to save HTTP requests, in our case, also the rollover states). So we'll do that and throw together a favicon while we're at it.

Tab sprite All the list item tabs
Favicon Favicon

Registration

Our intention with registration is going to be extremely simple. We're going to ask for a user's email and that's it. They will be sent an email with a link in it to complete registration. The link in that email will "activate" their account and they can choose the password at that time. So, our registration page can be pretty darn simple.

Registration form The registration form

As small as this is, this registration page sets the stage for other forms. We have a label/input pair here that can be used for any input/pair in any of our site's forms.

Account

We'll use the same form design as the registration page here. It's not cheating or being lazy, it's good design through consistency!

Account controls Account controls

Buttons

Notice the change in buttons. They are now big, orange and rounded. Much more button-like don't you think? Again for consistency, let's make this the default for all buttons across the site.

Site buttons Site buttons, looking button-like

Moving on

The developer now has plenty to go on to start fleshing out the user interactions for the site. And we have plenty to go on to start getting the HTML and CSS for all this ready, and ultimately to AJAX this puppy up and get it working.

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.

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.

September 29, 2009 - 1 comment.

How to Implement Natural Sorting in MySQL

Posted by Jason Lengstorf in Development

A Workaround to Support Natural Sorting in MySQL

Several times in the last few projects I've built, I've come across the need to sort alphanumeric rows in a database by number (i.e. entry1, entry2, entry3, etc.). If you've ever tried to do this, you know that natural sorting in MySQL with alphanumeric entries is a pain.

The Data

For our purposes, we'll be using the following table:

Table: sorting_test
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
 -------------------------- -------------

The Problem

Sorting by the column integer, of course, presents no problems.

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY integer
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
 -------------------------- -------------

However, when we try sorting by the alphanumeric column, we get unexpected results:

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY alphanumeric
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
 -------------------------- -------------

Obviously, this is not the desired outcome. Since we're sorting alphabetically, the entries are actually in the correct order, but we need to find a way to sort numerically.

This is called "natural sorting" in MySQL. Actually, it's called natural sorting everywhere. What it means is, in layman's terms, a human being would naturally sort the items with the count in order.

However, as far as MySQL is concerned the above output is alphabetical. If you swap the numbers for letters, the way MySQL sorts these items makes much more sense:

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY alphanumeric
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| testA                    | 1           |
| testAA                   | 10          |
| testAB                   | 11          |
| testAC                   | 12          |
| testB                    | 2           |
| testC                    | 3           |
| testD                    | 4           |
| testE                    | 5           |
| testF                    | 6           |
| testG                    | 7           |
| testH                    | 8           |
| testI                    | 9           |
 -------------------------- -------------

The Solution

There are a whole lot of solutions out there if you hit up Google, and you can, of course, just use the natsort() function in PHP, but it's simple enough to accomplish natural sorting in MySQL: sort by length first, then the column value.

Query: SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY LENGTH(alphanumeric), alphanumeric
 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 2           |
| test3                    | 3           |
| test4                    | 4           |
| test5                    | 5           |
| test6                    | 6           |
| test7                    | 7           |
| test8                    | 8           |
| test9                    | 9           |
| test10                   | 10          |
| test11                   | 11          |
| test12                   | 12          |
 -------------------------- -------------

This works because the first nine entries are 5 characters long, with the tenth, eleventh, and twelfth entries at 6 characters; this creates two groups of entries that are then sub-sorted, resulting in our desired order. This is called "natural sorting".

Summary

This is a quick tip, but a useful one. Do you have a better way of forcing natural sorting in MySQL? Let me know in the comments!

July 2, 2009 - 73 comments

JSON: What It Is, How It Works, & How to Use It

Posted by Jason Lengstorf in Development

This week I want to cover a topic that I feel has become an important part of any developer's toolkit: the ability to load and manipulate JSON feeds from other sites via AJAX. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. This article will cover the following:

  • What is JSON?
  • Why does JSON matter?
  • How do we use JSON in a project?

We'll also use our newfound skills with JSON at the end of this project to build a quick app that loads photos from Flickr without requiring a page refresh.

What Is JSON?

JSON is short for JavaScript Object Notation, and is a way to store information in an organized, easy-to-access manner. In a nutshell, it gives us a human-readable collection of data that we can access in a really logical manner.

Storing JSON Data

As a simple example, information about me might be written in JSON as follows:

This creates an object that we access using the variable jason. By enclosing the variable's value in curly braces, we're indicating that the value is an object. Inside the object, we can declare any number of properties using a "name": "value" pairing, separated by commas. To access the information stored in jason, we can simply refer to the name of the property we need. For instance, to access information about me, we could use the following snippets:

Storing JSON Data in Arrays

A slightly more complicated example involves storing two people in one variable. To do this, we enclose multiple objects in square brackets, which signifies an array. For instance, if I needed to include information about myself and my brother in one variable, I might use the following:

To access this information, we need to access the array index of the person we wish to access. For example, we would use the following snippet to access info stored in family:

NOTE: This is beneficial if it will be necessary to loop through stored information, as it lends itself to a for loop with an automatically incrementing value.

Nesting JSON Data

Another way to store multiple people in our variable would be to nest objects. To do this, we would create something similar to the following:

Accessing information in nested objects is a little easier to understand; to access information in the object, we would use the following snippet:

Nested JSON and arrays can be combined as needed to store as much data as necessary.

Why Does JSON Matter?

With the rise of AJAX-powered sites, it's becoming more and more important for sites to be able to load data quickly and asynchronously, or in the background without delaying page rendering. Switching up the contents of a certain element within our layouts without requiring a page refresh adds a "wow" factor to our applications, not to mention the added convenience for our users. Because of the popularity and ease of social media, many sites rely on the content provided by sites such as Twitter, Flickr, and others. These sites provide RSS feeds, which are easy to import and use on the server-side, but if we try to load them with AJAX, we run into a wall: we can only load an RSS feed if we're requesting it from the same domain it's hosted on. An attempt to load my Flickr account's RSS feed via jQuery's $.ajax() method results in the following JavaScript error:

JSON allows us to overcome the cross-domain issue because we can use a method called JSONP that uses a callback function to send the JSON data back to our domain. It's this capability that makes JSON so incredibly useful, as it opens up a lot of doors that were previously difficult to work around.

How Do We Load JSON into a Project?

One of the easiest ways to load JSON data into our web applications is to use the $.ajax() method available in the jQuery library. The ease of retrieving data will vary based on the site providing the data, but a simple example might look like this:

This example would request the latest feed items in JSON format and output them to the browser. Obviously, we wouldn't want to output raw JSON data to the browser, but this example shows the basics of loading JSON from an external source.

A Practical Example: Loading Flickr Streams with JSON and jQuery

To show how JSON works in a real-world example, let's load photos from Flickr using jQuery and the JSON version of Flickr's "Latest" photo feed.

Step 1: Create the AJAX Request

Flickr's photostream feeds are relatively easy to access. All users have a unique ID number, which we will send as part of the request to this URL.

The request we need to send asks for the latest photos from the user in question, along with flags asking for a JSON-formatted response. The request we need to send will look like this:

In the above example, XXXXXXXX@NXX needs to be replaced with the user's ID. We'll be writing a function, so the user's ID will be passed as an argument called flickrID. Our function will be called loadFlickr(). Let's create the function that will load our JSON response:

The returned JSON data will look something like this (note that I've removed all but one of the returned photos for the sake of brevity):

Step 2: Process the JSON Data

What we're going to do is display the thumbnails of the latest 16 photos, which will link to the medium-sized display of the image. The Flickr JSON is a little confusing, and it doesn't provide a direct link to the thumbnail version of our photos, so we'll have to use some trickery on our end to get to it, which we'll cover in just a moment. Each photo entry is stored in an array called items, which we access in our AJAX call using feed.items. To get to the data about each entry, we'll loop through the items until we've either hit the last available photo or 16 total photos; whichever comes first. Let's modify our function and set up the loop:

The element we're interested in is the "m" element stored within the "media" element. This can be accessed within our loop using feed.items[i].media.m. We're going to run a regular expression on this value to get both the medium and thumbnail image paths, which we'll assemble into a linked thumbnail image. Then, we'll push the newly assembled HTML into the array of thumbs we created. After we've finished the loop, we'll combine all the images into one string of HTML and replace the contents of our display element with the loaded thumbnails. Let's add this functionality to our script:

Note that I've also added a function called addLB() to the end of this function; this adds the lightbox effect to our thumbnails, which is purely for aesthetics.

Step 3: Call Our Function

At this point, we're ready to call our function. To load my Flickr stream, we would need to call our function as follows:

The example posted will pull multiple users' photostreams into the containing box without causing a page refresh. Look at the source code on the demo to see how it was done. NOTE: Keep in mind that this demo was to show how to load JSON data, and not on how to implement the code to call the function. The JavaScript calls are inline, which should NOT be used in a production script.

Summary

Have you used JSON before? Is there anything you'd like to clarify or see further clarified about JSON? Let me know in the comments!