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> <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¤tListID=" currentListID "&startPos=" startPos "¤tPos=" 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.