AJAX-ed Todo List With PHP, MySQL & jQuery

In this tutorial we are making a simple AJAX-ed Todo List App, with PHP, MySQL and jQuery. In the process we are going to demonstrate PHP's OOP capabilities, play with jQuery UI and implement some nice AJAX functionality.

Note: This tutorial is quite old and doesn't work in PHP7 and above. We are keeping it online only as a reference.

For a better understanding of the steps of this tutorial, you an go ahead and download the demo archive available from the button above.

Step 1 - PHP

As this is more of a developer-oriented tutorial, we are going to start with the PHP part. Unlike previous tutorials, this time we are leveraging the OOP features of PHP 5.

The author presumes you have a basic understanding of the core concepts behind object oriented programming, and PHP 5's OOP implementation. For a quick reference, check out these pages of PHP's documentation: OOP Basics, Static Methods, Exceptions, Magic Methods.

All of the functionality available to the end user - creating, editing, deleting and reordering the todo items - is implemented as different methods of a class, explained in detail below.

todo.class.php - Part 1

/* Defining the ToDo class */

class ToDo{

    /* An array that stores the todo item data: */

    private $data;

    /* The constructor */
    public function __construct($par){
        if(is_array($par))
            $this->data = $par;
    }

    /*
        This is an in-build "magic" method that is automatically called
        by PHP when we output the ToDo objects with echo.
    */

    public function __toString(){

        // The string we return is outputted by the echo statement

        return '
            <li id="todo-'.$this->data['id'].'" class="todo">

                <div class="text">'.$this->data['text'].'</div>

                <div class="actions">
                    <a href="" class="edit">Edit</a>
                    <a href="" class="delete">Delete</a>
                </div>

            </li>';
    }

The constructor takes the array passed as a parameter and stores it in the $data property of the class. This array is a row fetched from the database with mysql_fetch_assoc() and contains the id and the text of the todo item.

After this is the magic __toString() method, which is called internally when we attempt to echo out an object of this class. The string it returns contains the markup used by each todo item - a <li> element with a unique id and a classname "todo", inside of which we have the text of the todo and the two action hyperlinks.

todo.class.php - Part 2

 /*
        The edit method takes the ToDo item id and the new text
        of the ToDo. Updates the database.
    */

    public static function edit($id, $text){

        $text = self::esc($text);
        if(!$text) throw new Exception("Wrong update text!");

        mysql_query("   UPDATE tz_todo
                        SET text='".$text."'
                        WHERE id=".$id
                    );

        if(mysql_affected_rows($GLOBALS['link'])!=1)
            throw new Exception("Couldn't update item!");
    }

    /*
        The delete method. Takes the id of the ToDo item
        and deletes it from the database.
    */

    public static function delete($id){

        mysql_query("DELETE FROM tz_todo WHERE id=".$id);

        if(mysql_affected_rows($GLOBALS['link'])!=1)
            throw new Exception("Couldn't delete item!");
    }

    /*
        The rearrange method is called when the ordering of
        the todos is changed. Takes an array parameter, which
        contains the ids of the todos in the new order.
    */

    public static function rearrange($key_value){

        $updateVals = array();
        foreach($key_value as $k=>$v)
        {
            $strVals[] = 'WHEN '.(int)$v.' THEN '.((int)$k+1).PHP_EOL;
        }

        if(!$strVals) throw new Exception("No data!");

        // We are using the CASE SQL operator to update the ToDo positions en masse:

        mysql_query("   UPDATE tz_todo SET position = CASE id
                        ".join($strVals)."
                        ELSE position
                        END");

        if(mysql_error($GLOBALS['link']))
            throw new Exception("Error updating positions!");
    }

The definition of the class continues with a number of static methods. Those are special methods, which can be accessed without the need of an object of the class to be created. For example, you can call the edit method by writing: ToDo::edit($par1,$par2).

Notice how we are using exceptions to handle errors. When an exception occurs the script execution halts and it is up to the rest of the script to catch it and output the appropriate status.

Also you may find interesting the way we are updating the database with the new positions of the todo items. We are using the CASE operator, available in MySQL. This way, no matter how many todos are in the database, we execute only one query.

Saving queries and optimizing your scripts, although tedious, is hugely beneficial in the long run. You can read more about the CASE operator (among other interesting features) in MySQL's official documentation.

todo.class.php - Part 3

 /*
        The createNew method takes only the text of the todo as a parameter,
        writes to the database and outputs the new todo back to
        the AJAX front-end.
    */

    public static function createNew($text){

        $text = self::esc($text);
        if(!$text) throw new Exception("Wrong input data!");

        $posResult = mysql_query("SELECT MAX(position)+1 FROM tz_todo");

        if(mysql_num_rows($posResult))
            list($position) = mysql_fetch_array($posResult);

        if(!$position) $position = 1;

        mysql_query("INSERT INTO tz_todo SET text='".$text."', position = ".$position);

        if(mysql_affected_rows($GLOBALS['link'])!=1)
            throw new Exception("Error inserting TODO!");

        // Creating a new ToDo and outputting it directly:

        echo (new ToDo(array(
            'id'    => mysql_insert_id($GLOBALS['link']),
            'text'  => $text
        )));

        exit;
    }

    /*
        A helper method to sanitize a string:
    */

    public static function esc($str){

        if(ini_get('magic_quotes_gpc'))
            $str = stripslashes($str);

        return mysql_real_escape_string(strip_tags($str));
    }

} // closing the class definition

Accessing static methods from the same class can easily be done with the self:: keyword. This way we are using the esc() method to sanitize the incoming user data.

Also notice the createNew() method. In it, after running the INSERT query on the database, we use the returned auto-assigned unique id with mysql_insert_id() and create a new todo object, which is then echoed out to the front end.

Now lets take a look at how this class is used.

demo.php - Part 1

// Select all the todos, ordered by position:
$query = mysql_query("SELECT * FROM `tz_todo` ORDER BY `position` ASC");

$todos = array();

// Filling the $todos array with new ToDo objects:

while($row = mysql_fetch_assoc($query)){
    $todos[] = new ToDo($row);
}

After including todo.class.php in demo.php, we select the todo items and loop through the MySQL result set, filling in the $todos array with objects.

demo.php - Part 2

// Looping and outputting the $todos array. The __toString() method
// is used internally to convert the objects to strings:

foreach($todos as $item){
    echo $item;
}

Later in the page, these objects are echoed out. Thanks to the __toString() method discussed above, all the markup is automatically generated, so we do not have to deal with any of that.

The front end issues a number of different AJAX calls. Making a separate file to handle each of them would be a bit of overkill, so the best solution is to group them in a single AJAX handling file. This is done in ajax.php, which you can see below.

ajax.php

$id = (int)$_GET['id'];

try{

    switch($_GET['action'])
    {
        case 'delete':
            ToDo::delete($id);
            break;

        case 'rearrange':
            ToDo::rearrange($_GET['positions']);
            break;

        case 'edit':
            ToDo::edit($id,$_GET['text']);
            break;

        case 'new':
            ToDo::createNew($_GET['text']);
            break;
    }

}
catch(Exception $e){
//  echo $e->getMessage();
    die("0");
}

echo "1";

With the help of a switch statement, we decide which of the static methods of the ToDo class to execute. If an error occurs in one of these methods, an exception is dispatched. Because the whole switch is enclosed in a try statement, execution of the script halts and control is passed to the catch statement, which outputs a zero and exits the script.

You could potentially echo (or write to a log) exactly what kind of error occurred by uncommenting line 26.

Step 2 - MySQL

The tz_todo table holds and assigns the unique ids of the todo items (via the auto_increment setting of the field), the position, the text and the dt_added timestamp.

You can find the SQL that will recreate the table in table.sql in the download archive. Also, if you plan to run the demo on your own server, don't forget to fill in your login details in connect.php.

Step 3 - XHTML

As most of the markup is generated by PHP, we are left with taking care of the rest of the page's XHTML. First we need to include jQuery, jQuery UI, and the stylesheets in the document. It is considered a good practice to include the stylesheets to the head section, and the JavaScript files right before the closing </body> tag.

<link rel="stylesheet" href="jquery-ui.css" type="text/css" />
<link rel="stylesheet" type="text/css" href="styles.css" />

<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript" src="jquery-ui.min.js"></script>
<script type="text/javascript" src="script.js"></script>

After this we can move on to coding the rest of the page.

demo.php

<div id="main">

    <ul class="todoList">

    <?php

        // Looping and outputting the $todos array. The __toString() method
        // is used internally to convert the objects to strings:

        foreach($todos as $item){
            echo $item;
        }

        ?>

    </ul>

    <a id="addButton" class="green-button" href="">Add a ToDo</a>

</div>

<!-- This div is used as the base for the confirmation jQuery UI dialog box. Hidden by CSS. -->
<div id="dialog-confirm" title="Delete TODO Item?">Are you sure you want to delete this TODO item?</div>

Each todo is a li item inside of the todoList unordered list. This way, we can later use the sortable method of jQuery UI to easily convert it into an interactive sortable element. Also, in the process, we enhance the semantic value of the code.

Step 4 - CSS

Now lets move on to the styling of the todos. Only parts of the original stylesheet are given here for better readability. You can find the rest in styles.css in the download archive.

styles.css - Part 1

/* The todo items are grouped into an UL unordered list */

ul.todoList{
    margin:0 auto;
    width:500px;
    position:relative;
}

ul.todoList li{
    background-color:#F9F9F9;
    border:1px solid #EEEEEE;
    list-style:none;
    margin:6px;
    padding:6px 9px;
    position:relative;
    cursor:n-resize;

    /* CSS3 text shadow and rounded corners: */

    text-shadow:1px 1px 0 white;

    -moz-border-radius:6px;
    -webkit-border-radius:6px;
    border-radius:6px;
}

ul.todoList li:hover{
    border-color:#9be0f9;

    /* CSS3 glow effect: */
    -moz-box-shadow:0 0 5px #A6E5FD;
    -webkit-box-shadow:0 0 5px #A6E5FD;
    box-shadow:0 0 5px #A6E5FD;
}

The todoList ul is horizontally centered on the page and is assigned a relative positioning. The li elements inside it (the todo items) share a number of CSS3 rules. These, unfortunately, do not work in older browsers, but as they are solely for presentation purposes even browsers as old as IE6 can enjoy a fully working script, albeit not as pretty as intentioned.

styles.css - Part 2

/* The edit textbox */

.todo input{
    border:1px solid #CCCCCC;
    color:#666666;
    font-family:Arial,Helvetica,sans-serif;
    font-size:0.725em;
    padding:3px 4px;
    width:300px;
}

/* The Save and Cancel edit links: */

.editTodo{
    display:inline;
    font-size:0.6em;
    padding-left:9px;
}

.editTodo a{
    font-weight:bold;
}

a.discardChanges{
    color:#C00 !important;
}

a.saveChanges{
    color:#4DB209 !important;
}

In the second part of the code we style the input text box, shown when the todo item is edited, and the save and cancel links.

Step 5 - jQuery

Moving to the JavaScript code. Here we are using two of jQuery UI's user interface components - sortable, and dialog. These alone save us at least a couple of hours of development time, which is one of the benefits of using a nicely thought out library like jQuery.

script.js - Part 1

$(document).ready(function(){
    /* The following code is executed once the DOM is loaded */

    $(".todoList").sortable({
        axis        : 'y',              // Only vertical movements allowed
        containment : 'window',         // Constrained by the window
        update      : function(){       // The function is called after the todos are rearranged

            // The toArray method returns an array with the ids of the todos
            var arr = $(".todoList").sortable('toArray');

            // Striping the todo- prefix of the ids:

            arr = $.map(arr,function(val,key){
                return val.replace('todo-','');
            });

            // Saving with AJAX
            $.get('ajax.php',{action:'rearrange',positions:arr});
        }
    });

    // A global variable, holding a jQuery object
    // containing the current todo item:

    var currentTODO;

    // Configuring the delete confirmation dialog
    $("#dialog-confirm").dialog({
        resizable: false,
        height:130,
        modal: true,
        autoOpen:false,
        buttons: {
            'Delete item': function() {

                $.get("ajax.php",{"action":"delete","id":currentTODO.data('id')},function(msg){
                    currentTODO.fadeOut('fast');
                })

                $(this).dialog('close');
            },
            Cancel: function() {
                $(this).dialog('close');
            }
        }
    });

To display the dialog, we need to have a base div, which is going to be converted to a dialog. The contents of the div is going to be displayed as the text of the dialog, and the contents of the title attribute of the div will become the title of the dialog window. You can find this div (id=dialog-confirm) in demo.php.

script.js - Part 2

  // When a double click occurs, just simulate a click on the edit button:
    $('.todo').live('dblclick',function(){
        $(this).find('a.edit').click();
    });

    // If any link in the todo is clicked, assign
    // the todo item to the currentTODO variable for later use.

    $('.todo a').live('click',function(e){

        currentTODO = $(this).closest('.todo');
        currentTODO.data('id',currentTODO.attr('id').replace('todo-',''));

        e.preventDefault();
    });

    // Listening for a click on a delete button:

    $('.todo a.delete').live('click',function(){
        $("#dialog-confirm").dialog('open');
    });

    // Listening for a click on a edit button

    $('.todo a.edit').live('click',function(){

        var container = currentTODO.find('.text');

        if(!currentTODO.data('origText'))
        {
            // Saving the current value of the ToDo so we can
            // restore it later if the user discards the changes:

            currentTODO.data('origText',container.text());
        }
        else
        {
            // This will block the edit button if the edit box is already open:
            return false;
        }

        $('<input type="text">').val(container.text()).appendTo(container.empty());

        // Appending the save and cancel links:
        container.append(
            '<div class="editTodo">'+
                '<a class="saveChanges" href="">Save</a> or <a class="discardChanges" href="">Cancel</a>'+
            '</div>'
        );

    });

Notice the use of the jQuery live() method to bind events. We are using live(), instead of bind(), because live() can listen for events on any elements, even those who do not yet exist. This way we make sure that all the todo items added in the future to the page by the user, will also trigger the same event handlers, as the currently existing ones.

script.js - Part 3

  // The cancel edit link:

    $('.todo a.discardChanges').live('click',function(){
        currentTODO.find('.text')
                    .text(currentTODO.data('origText'))
                    .end()
                    .removeData('origText');
    });

    // The save changes link:

    $('.todo a.saveChanges').live('click',function(){
        var text = currentTODO.find("input[type=text]").val();

        $.get("ajax.php",{'action':'edit','id':currentTODO.data('id'),'text':text});

        currentTODO.removeData('origText')
                    .find(".text")
                    .text(text);
    });

    // The Add New ToDo button:

    var timestamp;
    $('#addButton').click(function(e){

        // Only one todo per 5 seconds is allowed:
        if(Date.now() - timestamp<5000) return false;

        $.get("ajax.php",{'action':'new','text':'New Todo Item. Doubleclick to Edit.'},function(msg){

            // Appending the new todo and fading it into view:
            $(msg).hide().appendTo('.todoList').fadeIn();
        });

        // Updating the timestamp:
        timestamp = Date.now();

        e.preventDefault();
    });

}); // Closing $(document).ready()

In the last part of the code we are binding events to the Save and Cancel links, which are added to the todo when editing it. We also set up an event listener for the "Add" button. Notice how we prevent flooding by limiting the submit rate of new todos to one every 5 seconds.

With this our AJAX-ed To Do List is complete!

Conclusion

Today we created a simple AJAX enabled ToDo web script with PHP, MySQL and jQuery. You can use it to create your own task management application or turn it into a fully fledged web app.

What do you think? How would you modify this code?