How to Code a Fun To-Do List With PHP and AJAX

For this week's Tuts+ Premium tutorial, we'll be working with many different technologies. Ultimately, we'll be building a to-do list that will allow you, or your user, to create, update, and delete items asynchronously. To accomplish our task, we'll be using PHP and jQuery's AJAX capabilities. I think you'll find that it's not quite as hard as you might initially think. I'll show you exactly how!

This tutorial includes a screencast available to Tuts+ Premium members.

Step 1: Creating a New Database

As you can imagine, we can't save, delete, and update records in a static environment. So, we must create a MySql database that will store the information.

If you're using PHPMyAdmin, access the control panel by visiting http://localhost/phpmyadmin.

Within the "Create New Database" textbox, type "db" and click "Create". Next, you'll need to create a table. Type "todo", and '3' for "number of fields".

Creating Our Columns

We'll now need to add the appropriate columns.

id : unique id to indentify each row.

title : The title of our item.

description : A description detailing what we need to do!

Make sure that the options of each field matches those shown in the following image.

Insert Test Rows

Now that we've created our database, let's quickly add some test rows. Click on your "db" database; then choose "Browse". You'll be brought to a screen that lists the contents of each row in your database. Obviously, this section is empty right now. Choose "Insert" and add a few columns. Type whatever you wish here.

Full Screencast

Step 2: The Db Class

Though not required by any means, I find that it's easiest to manage my functions when grouping them into a class. Considering this, we'll now create a "Db" class that will contain several functions.

__construct : This function automatically runs as soon as the object is instantiated.

delete_by_id() : Deletes the necessary row by passing in the row's unique id.

update_by_id() : Updates the row by passing in its unique id.

Open your code editor of choice, and create a new file called "db.php". Within this blank document, paste in the following lines of code.

Using only the code above, we've successfully created a new class. It doesn't do anything just yet, but it's a class nonetheless!

__construct()

The __construct() method (class talk for "function") is known as a "magic method". It will run immediately after a class is instantiated. We're going to use this method to make our initial connection to the MySql database.

If you're not familiar with OOP, it can be slightly daunting at first. Luckily, it's not too difficult to grasp. We want our mysql connection to be available to all of the methods in our class. Considering this, it wouldn't be a good idea to store the $mysql variable within a specific function. Instead, it should be a class property.

private $mysql;

Accessing Properties From Methods

Within a method, we can't just access our property by typing, "$mysql". We must first refer to the object.

$this->mysql

Be sure to take note of the fact that, when accessing a property, we can leave off the dollar sign.

Mysqli

It is preferable to use mysql improved (mysqli) rather than the traditional mysql_connect method when connecting to a database. Not only is it faster, but it also allows us to use an OOP approach.

When creating a new instance of the mysqli class, we must pass in four parameters.

host : 'localhost'

username : root

password : 'yourPassword'

database name : db

That should do it for now. We'll come back to our class over the course of this tutorial to append new methods. Just remember, when we create a new instance of this class...

require 'db.php';
$db = new Db();

... we automatically open a connection to our database, thanks to the __construct() magic method.

The Markup

Now, we need to create our markup for the home page. Add a new page to your solution, and save it as "index.php". Next, paste in the following.

Analysis

Within the head of our document, I'm referencing Google's CDN to access jQuery. This is easily the preferred method when using jQuery. Next, I'm referencing a 'scripts.js' file that we'll create later in this tutorial.

Analysis

Use 'require' to access our Db class.

Create a new instance of the Db class.

Create a query. This will retrieve all records from the "todo" table, and sort them in an ascending order.

We now must execute our query. $db->mysql->query($query). $db references the object. $mysql refers to the mysqli class. $query is a method of the mysqli class that allows us to pass in a query. Here, we're passing in the string that we just created.

$results->num_rows will return the total number of retrived rows from the database. If one or more are returned, we'll then use a while statement to loop through the rows.

Create a temporary variable called $row that will refer to the information, for each iteration. We then create three variables that refer to their respective counterparts within the database.

Each item will be wrapped within a div with a class of "item".

Next, we use heredocs to format our to-do item. Heredocs allow for an easy, and organized way to mix html and php. To learn more, be sure to review this screencast.

Wrap the title within h4 tags; the description within p tags.

The user needs a way to edit and delete each item. So, we've created two anchor tags that will allow us to do so. We'll come back to this later.

Echo out our heredocs info, and close out the ".item" div.

If zero rows were returned from the database, echo "There are zero items. Add one now!".

Hopefully, all of that made sense. At this point, you should have something like the following:

Step 6: Add a New Item

We also want the user to have the ability to insert new records. Let's create a form that will allow for this very thing.

This is your standard 'run-of-the-mill' form. We've added inputs for a title and description. When the submit button is clicked, the information entered will be posted to "addItem.php". Let's create that page now.

Step 7: AddItem.php

Create a new document, and save it as "addItem.php". Paste in the following code:

If the submit button with a name of "addEntry" exists, then run the following code.

Create a new query. You'll notice that I'm using question marks as the values. It is the preferred method to use prepared statements when updating our database. It's an excellent way to protect yourself against sql injection.

Prepare our mysql variable by passing in the query that we just created.

If it was prepared successfully, bind the appropriate parameters. The first parameter asks for the data types for each item. I've used 's' to refer to "string". The second two parameters grab the title and description values from the POST super global array.

Execute the statement.

Finally, redirect the user back to the home page.

Step 7: Update Items

Using jQuery's AJAX capabilities, let's allow the user to update each item without a postback. Create a new file within a "js" folder, and call it "scripts.js". Remember, we've already referenced this file in our markup.

Now, we need to allow the user to enter a new description. That is why they clicked on "Edit Entry", isn't it!? We find the description P tag, empty it, and then append a textarea. We use "empty()" to make sure that we get rid of all the text; it's not needed anymore. The value of this textarea will be equal to the oldText - as a convenience.

$('.newDescription').blur(function() {

Find this new textarea, and when the user leaves the textbox, run a function.

Call the .ajax function, and pass in a few parameters. The type will be "POST". The url to access is "updateEntry.php". The data to pass to this page is the newText that the user entered, and the unique id from that row in the database. When the update is performed successfully, run a function, and update the old text with the new text!

As before, we're referencing our db class, and then we instantiate it. Next, we're creating a new variable, called $response, and are making it equal to whatever is returned from the "update_by_id()" method. We haven't created this method just yet. Now is a good time to do so.

This method accepts two parameters: the id, and the description of the item. So, when we call this method, we must remember to pass in those two parameters! We begin by creating our query: update the "todo" table and change the description to whatever is passed in - but only update the row where the id is equal to the parameter passed in.

Like last time, we'll use prepared statements to update our database. It's the safest way! Prepare our query, bind the parameters (string and integer, or 'si'), and execute. We're returning a generic string, but it really isn't required at all. Now our update should work perfectly!

Step 8: Delete Items

Let's also create a nice asynchronous way for the user to delete entries. When they click the delete button for an item, we'll fade the div out and update the database to reflect the deletion. Open your javascript file and add the following:

Just like last time, we pass in a few parameters that access "delete.php". Rather than hardcoding the page in the url's value, I'm accessing attr('href') - which equals 'delete.php?id=$id'.

<a class="deleteEntryAnchor" href="delete.php?id=$id">D</a>

We don't need a "DATA" parameter, because all of the appropriate information is within the url's querystring. Once the deletion is performed successfully, we find the parent '.item' div, and fade it out slowly.

Delete.php

We've called our delete page with jQuery, but we haven't created the PHP yet. Create your new page and add the following code.

You should be used to these procedures by now. Create a new instance of our class, and call the "delete_by_id" method. Once that has been completed successfully, redirect the user back to "index.php". As you might have guessed, we need to create a new method within our db class. Return to db.php and add your new function.

Delete_by_id() Method

This method will accept one parameter - the id. Remember: in order to update a row, we MUST know that row's unique id. Otherwise, it will update every row. We're deleting all rows from the table, where the id is equal to what is passed in. As each row has its own unique id, only one will be affected. Next, we pass this query to our mysql object. Once again, the return is unnecessary; it's just for fun.

Step 9: Extra jQuery

We've finished all of our PHP work! The final step is to add a bit of jQuery to make everything work just a bit better. At the top of your Javascript file, just after the document.ready method, add the following code:

// Don't display the addNewEntry tab when the page loads.
$('#addNewEntry').css('display', 'none');
// We're using jQuery to create our tabs. If Javascript is disabled, they won't work. Considering
// this, we should append our tabs, so that they won't show up if disabled.
$('#tabs').append('<li id="newitem_tab"><a href="#">New Item</a></li>');
// Hide the description for each to-do item. Only display the h4 tag for each one.
$('div.item').children().not('h4').hide();
// The entire item div is clickable. To provide that feedback, we're changing the cursor of the mouse.
// When this div is clicked, we're going to toggle the display from visible to hidden each time it's clicked.
// However, when the user clicks the "update" button, the div will close when they click inside the textarea
// to edit their description. This code detects if the target of the click was the textarea. If it was,
// we do nothing.
$('div.item').css('cursor', 'pointer').click(function(e) {
if (!$(e.target).is('textarea')) {
$(this).children().not('h4').slideToggle();
$(this).children('h4').toggleClass('expandDown');
}
});

Step 10: Wait! the Layout Is Weird in IE6.

We can't call it a day just yet! That fun 'ole Internet Explorer 6 is causing a few layout problems.

The background pngs are 24 bit. IE6 doesn't natively support this. We'll need to import a script to fix it.

The navigation tabs aren't showing up in the right spot.

Each div.item isn't displaying correctly when expanded.

Our edit, and delete buttons are too far to the right of our div.

The Solution

Though we might like to, we can't ignore this browser just yet. Luckily, you'll find that most IE6 issues can be fixed quite easily. First, we need to import a script that will fix our alpha transparency issue. Dean Martin has a fantastic Javascript file that brings IE6 up to standards compliant. Simply by adding "-trans" to the end of our 24 bit png filenames, we can fix our problem. Be sure to visit the images folder, and edit the names.

You're Done!

There was A LOT to cover here. Hopefully, I explained myself thoroughly enough. If not, that's what the associated screencast is for! Be sure to review it to clear any blurry areas. If you still have questions, just ask me! Thanks so much for reading.