BLOG DESIGN SOLUTIONS Save this code in your cms folder in a file called nav.inc. To include the file in your pages, add this line after the opening tag in index.php and addpost.php: Tart it up Finally, I m sure you ll agree that your CMS is looking a little ugly at the moment. What it needs is a little style. Try applying the following stylesheet, or feel free to build your own: BODY { background: white; color: #333; font-family: “lucida grande”, verdana, tahoma, sans-serif; font-size: 62.5%; } H1 { font-weight: normal; font-size: 1.8em; margin-left:10em; } P, UL, OL { font-size: 1.2em; margin-left: 15em; } UL, OL { padding-left: 2em; } #nav { float: left; width: 8em; margin: 3em 0 0 0; padding:1em 2em; background: #ddd; border: 1px solid #ccc; } .message { color: red; } Save this stylesheet in your cms folder as cms.css and import the styles into your pages by adding a style element into the head of index.php and addpost.php:

WRITE YOUR OWN BLOG ENGINE // If delete has a valid post_id $delete = (isset($_REQUEST[”delete”]))?$_REQUEST[”delete”]:”"; if (preg_match(”/^[0-9]+$/”, $delete)) { $sql = “DELETE FROM posts WHERE post_id = $delete LIMIT 1″; $result = mysql_query($sql); if (!$result) { $message = “Failed to delete post $delete. MySQL said . ” . mysql_error(); } else { $message = “Post $delete deleted.”; } } The script is almost identical to that used for updating a post. The only slight difference is the SQL query itself: DELETE FROM table WHERE condition is met LIMIT max num of records to act upon The WHERE clause works in an identical way to that used with UPDATE and SELECT. I have added a LIMIT clause as a safety measure to ensure that the DELETE command deletes only a single row. The LIMIT clause can also be used with UPDATE and SELECT statements. As I said earlier, MySQL has no inbuilt undo functionality so the DELETE command is dangerously simple. For example, this command will delete all the posts in your table! DELETE FROM table Note that when your post is deleted, the remaining post_ids do not renumber. So if you have three posts and delete post number 2, the remaining posts will still be numbered 1 and 3, and any subsequent new post will be numbered 4. This is a feature of MySQL that ensures database entries remain uniquely identified with the same id. Figure 7-10 shows the result. Figure 7-10. Your CMS index page showing the first post and delete link Finishing touches To tie together the CMS functionality, you need a way of navigating between the Index page and the Add a Post page, and back. A neat way to achieve this is with another include. The following code simply links between the two CMS pages you have created thus far:

• List of posts
• Add a post

295

BLOG DESIGN SOLUTIONS database. $myposts becomes false after there are no more posts to show, and at this point the while expression knows to stop looping. $post_id = $myposts[”post_id”]; $title = $myposts[”title”]; $dateattime = $myposts[”dateattime”]; echo “

\n”; Inside the do-while loop, the blog post information is extracted from the $myposts array and used to display a list item for each post. This list item links through to addpost.php, appending the post_id in the query string as you had to do in your browser earlier. Note that the post date element is accessed using the dateattime alias created in the SELECT statement. If the alias hadn t been created, the formatted date would be accessed using this rather clumsy code: $dateattime = $myposts[”DATE_FORMAT(postdate, ‘%e %b %Y at %H:%i’)”]; Enter http://127.0.0.1/cms/ in your browser and you should see the list of blog posts currently in your database. The resulting page should look something Figure 7-9. Figure 7-9. Your CMS homepage showing a list of all blog posts in the database Deleting a post The final operation required for your CMS is the capability to delete unwanted posts. This functionality will be built into your index.php page by adding a delete button next to the link for each post and adding a delete script. First, insert the delete link into each list item by amending your code as follows: echo “

\n”; This link sends delete=post_id as the query string so the delete script will know which post to delete. I have also added a JavaScript confirmation to help prevent any unwanted deletions because MySQL has no built-in undo functionality. Here s the delete code; insert it just after the database connection. That way, a deletion will occur before the SELECT query, and hence the post list will be updated correctly:

WRITE YOUR OWN BLOG ENGINE } else { echo “

There are no blog posts in the database.

“; } ?> Stepping through the code: first a connection to the database is opened as before. Then an SQL statement is constructed to pull all the posts out of the database: SELECT post_id, title, DATE_FORMAT(postdate, ‘%e %b %Y at %H:%i’) . AS dateattime FROM posts ORDER BY postdate DESC I introduced a couple of new functions here. First, the ORDER BY clause at the end of the query sorts all the posts into date order with the newest first. You can order by more than one field at a time by using a comma-separated list. For example this clause would sort by postdate first and then by title for those posts with the same date: ORDER BY postdate DESC, title ASC The other new function is DATE_FORMAT. Because the postdate field was created with a data type of DATETIME, MySQL can manipulate the date and time data it couldn t do this if the date were stored as normal text. DATE_FORMAT allows you to output a date and time exactly as you wish. In this case, the postdate, which is currently stored as something like 2005-04-03 15:30:00, is formatted and displayed as 3 Apr 2005 at 15:30. An alias for the formatted date is created using AS dateattime (this is for convenience, as will become apparent later in this section). More information about the date_format function can be found on the MySQL website at http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html. Moving on to the main body of the HTML document, I have inserted a chunk of PHP after the main heading. This script steps through all the posts retrieved from the database in the afore-mentioned SELECT query and displays them in a list with links through to the edit page. Here s how: \n”; Firstly the code checks that the $myposts array is not null; in other words, it checks that there are actually some posts to display. If so, it opens an HTML ordered list. do { .. } while ($myposts = mysql_fetch_array($result)); Then a do-while loops through all the posts returned by the SELECT query. A do-while loop repeats everything within it until the while expression is no longer true. The key here is that every time the mysql_fetch_array function is executed it advances to the next row in the query result thus populating the $myposts array with the next post pulled from the 293

BLOG DESIGN SOLUTIONS Creating a screen for listing posts At this point, you can add and edit blog posts, but when it comes to editing a post you still have to know its post_id and type the number into your browser. Not exactly convenient, I m sure you ll agree. Far more useful would be a list showing all your blog posts so you can select which post to edit. And that s what I ll show you now. All that is required is one SELECT statement and a PHP loop. Here s the entire code; save it in your cms directory as index.php (because you ll want it to be the default page in your CMS):

All blog posts

“.$message.”

“;} if($myposts) { echo “

\n”; do { $post_id = $myposts[”post_id”]; $title = $myposts[”title”]; $dateattime = $myposts[”dateattime”]; echo “

1. “; echo “$title . posted $dateattime”; echo “

\n”; } while ($myposts = mysql_fetch_array($result)); echo “

“;

WRITE YOUR OWN BLOG ENGINE

$message

“; } ?>

291

BLOG DESIGN SOLUTIONS title=’$db_title’, postdate=’$db_postdate’, summary=’$db_summary’, post=’$db_post’ WHERE post_id = $post_id”; $result = mysql_query($sql); if (!$result) { $message = “Failed to update post. MySQL said ” . mysql_error(); } else { $message = “Successfully update post ‘$title’.”; } } $sql = “SELECT title, postdate, summary, post FROM posts . WHERE post_id=$post_id”; $result = mysql_query($sql); $mypost = mysql_fetch_array($result); if($mypost) { $title = $mypost[”title”]; $postdate = $mypost[”postdate”]; $summary = $mypost[”summary”]; $post = $mypost[”post”]; } else { $message = “No post matching that post_id.”; } } else { $editmode = false; } ?>

WRITE YOUR OWN BLOG ENGINE Here is the final code for the addpost.php page: $value) { $_POST[$key] = stripslashes($value); } } // Extract form submission $title = (isset($_POST[”title”]))?$_POST[”title”]:”"; $postdate = (isset($_POST[”postdate”]))?$_POST[”postdate”]:”"; $summary = (isset($_POST[”summary”]))?$_POST[”summary”]:”"; $post = (isset($_POST[”post”]))?$_POST[”post”]:”"; $submitAdd = (isset($_POST[”submitAdd”]))?$_POST[”submitAdd”]:”"; // Open connection to database include($_SERVER[”DOCUMENT_ROOT”] . “/db_connect.php”); // Prepare data for database $db_title = addslashes($title); $db_postdate = addslashes($postdate); $db_summary = addslashes($summary); $db_post = addslashes($post); // If form has been submitted, insert post into database if ($submitAdd) { $sql = “INSERT INTO posts (title,postdate,summary,post) VALUES (’$db_title’, ‘$db_postdate’, ‘$db_summary’, ‘$db_post’)”; $result = mysql_query($sql); if (!$result) { $message = “Failed to insert post. MySQL said ” . mysql_error(); } else { $message = “Successfully inserted post ‘$title’.”; } } // Get post_id from query string $post_id = (isset($_REQUEST[”post_id”]))?$_REQUEST[”post_id”]:”"; // If post_id is a number, get post from database if (preg_match(”/^[0-9]+$/”, $post_id)) { $editmode = true; // If form has been submitted, update post if (isset($_POST[”submitUpdate”])) { $sql = “UPDATE posts SET 289

BLOG DESIGN SOLUTIONS summary=’$db_summary’, post=’$db_post’ WHERE post_id = $post_id”; $result = mysql_query($sql); if (!$result) { $message = “Failed to update post. MySQL said ” . mysql_error(); } else { $message = “Successfully update post ‘$title’.”; } } This script is very similar to the insert post code. To start off, the script checks that the Update post button has been clicked. Then an SQL query to update the post is constructed using an UPDATE statement with this syntax: UPDATE table SET column1=value, column2=value WHERE condition is met Note that WHERE clauses in UPDATE statements work in exactly the same way as in SELECT statements. Now you can add new posts and update existing ones. Reload http://127.0.0.1/cms/ addpost.php?post_id=1 in your browser and try updating the text of the post you added earlier. Your result should look something like Figure 7-8. Figure 7-8. Successfully updating a blog post in your CMS

WRITE YOUR OWN BLOG ENGINE

And this: <?php if (isset($post)) {echo $post;} ?> If you now enter http://127.0.0.1/cms/addpost.php?post_id=1 into your browser, the form should populate with the first post you added. However the submit button will still say Add a post (and if you click it, that s exactly what will happen you will get a second post identical to the first, but with a post_id of 2). To deal with this, I will introduce case switching in PHP. Replace the submit button with this code: “; echo ““; break; case false: echo ““; break; } ?> The switch statement is similar to a series of if statements on the same expression. On many occasions, you might want to compare the same variable with many different values and execute a different piece of code depending on which value it equals to. This is exactly what the switch statement is for. In this case, you are echoing a different submit button depending on the value of $editmode. You can apply a similar approach to the title and heading of your page. Note that I have also added a hidden form field to the Update post button so the form also submits the post_id of the current post. The script will then know which post to update. And finally the SQL script to update the post. Insert this code just after where you set $editmode=true: // If form has been submitted, update post if (isset($_POST[”submitUpdate”])) { $sql = “UPDATE posts SET title=’$db_title’, postdate=’$db_postdate’, 287