BLOG DESIGN SOLUTIONS Stepping through the code, the first line extracts the post_id from the query string: $post_id = (isset($_REQUEST[”post_id”]))?$_REQUEST[”post_id”]:”"; Next, the preg_match function uses a regular expression to check that the post_id is a number. This is important for two reasons. First, from a practical point of view, the only valid post_ids are numbers, so there is no point dealing with post_ids that are not numbers. Second, from a security point of view, anyone can type anything into a query string, so it s vital to filter out any potentially harmful input. So if a valid-looking post_id has been identified, a flag (by way of the variable editmode) is set so the script knows it is in edit mode (this will be used later): if (preg_match(”/^[0-9]+$/”, $post_id)) { $editmode = true; Using regular expressions is a way of searching and matching patterns in text. The syntax is complex and seemingly obscure, but they are nonetheless a fast and powerful method of performing extremely specific searches and replace- ments in text. If you want to learn more about regular expressions, I recommend Nathan A. Good s book Regular Expression Recipes (for more details see www.apress.com/book/bookDisplay.html?bID=396). Next, an SQL query is used to extract the post from the database. The query is constructed using a SELECT statement of this form: SELECT required fields FROM database table WHERE condition is met This query is then sent to the database using the mysql_query function. On the next line, the mysql_fetch_array function is used to put the first row of SQL query result into an array; because each post_id is unique, there will be only one row returned anyway. This array is stored in the variable $mypost. If the SELECT statement did not return any results, the value of $mypost is null, and a warning is written to $message. If $mypost is not null, the values of the $mypost array are put into individual variables. Note that the elements of the array correspond to the column names in the SELECT statement: $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.”; } The next step is to repopulate the form with the post pulled out of the database. For each input box, add a value attribute and echo the corresponding PHP variable:

WRITE YOUR OWN BLOG ENGINE yyyy-mm-dd hh:mm:ss

Summary:

Post:

Creating a screen for updating a post The user interface for updating a post will be almost identical to that for inserting a new post, so it makes sense to use the same file: addpost.php. The first thing this page needs to know is which post to update. Each post can be uniquely identified by its post_id, and you can use a query string to reference this. A query string is the list of name=value pairs in a URL that follow the filename you have seen them when using search engines and other such sites. Because you now have a post in your database table with a post_id of 1, the URL needed to reference the post is http://127.0.0.1/cms/addpost.php?post_id=1. As with a form post, the elements of a query string are available to PHP through a super- global variable. This time, you will use the $_REQUEST array, and the id of the post can be obtained by $_REQUEST[”post_id”]. With that information in hand, the following code can be added to the end of your script, just before the closing ?> delimiter. This code pulls the post back out of the database: // 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; $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; } 285

BLOG DESIGN SOLUTIONS // 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(”../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’.”; } } ?>

Add a post

$message

“; } ?>

WRITE YOUR OWN BLOG ENGINE Figure 7-6. Successfully adding a post in your CMS Figure 7-7. The new post as it appears in phpMyAdmin At this stage, the source code for addpost.php should look like this: $value) { $_POST[$key] = stripslashes($value); } } 283

BLOG DESIGN SOLUTIONS The form s submit button is named submitAdd, so if the form is submitted, the contents of $submitAdd will be the value of the submit button (currently Add post ). The next three lines create an SQL query in a variable called $sql: $sql = “INSERT INTO posts (title,postdate,summary,post) VALUES (’$db_title’, ‘$db_postdate’, ‘$db_summary’, ‘$db_post’)”; SQL as a language was designed to be human-readable and, for the most part, it is fairly straightforward. The query begins with INSERT INTO posts, which means you will insert a new row in the posts table. The contents of the new row is then defined by the following: (list of column names) VALUES (list of corresponding values) The column names are those defined earlier. The associated values were extracted from the form submission into variables at the start of your script. Finally, the mysql_query function is used to send the query to the MySQL server for processing: $result = mysql_query($sql); if (!$result) { $message = “Failed to insert post. MySQL said ” . mysql_error(); } else { $message = “Successfully inserted post ‘$title’.”; } If the query works, mysql_query() will return true, otherwise it returns false, so you can check the value of $result to ensure that the insert worked. If the value of $result is false, the $message variable is populated with an error message concatenated with the error text generated by MySQL. Otherwise, we populate $message with a confirmation including the title of the inserted post. The final step is to write the contents of $message to the screen, so the user can get some feedback. Insert this code after

Add a post

:

$message

“; } ?> This code checks that $message exists; if so, it writes the message to a paragraph with a class set to message, so it can be styled later. Now reload the page into your browser and try adding a new post (see Figure 7-6). After you have a success message, you can go to phpMyAdmin to verify that the insert worked. In phpMyAdmin, click the posts link in the left frame and then click the Browse tab in the right frame. You should see your new post in the posts table. Note that it has been automatically assigned a post_id of 1 (see Figure 7-7).

WRITE YOUR OWN BLOG ENGINE In this script, the mysql_connect() function opens up a connection to the MySQL server using your username and password, so you have to replace myUsername and myPassword with the username and password you used when setting up your development environment in Chapter 2. The mysql_select_db() function then instructs any subsequent SQL queries to be performed against the blog database you set up earlier. To make your addpost page refer to the db_connect include, and hence enable it to connect to the database, add the following include function to your addpost document (insert just before the closing ?> delimiter): // Open connection to database include(”../db_connect.php”); And now to actually insert the submitted blog post into the database. Add the following code to your addpost page, just after the include function: // 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’.”; } } Stepping through the script step by step, the first part prepares the form data for the database: $db_title = addslashes($title); $db_postdate = addslashes($postdate); $db_summary = addslashes($summary); $db_post = addslashes($post); Here I am using the addslashes function to escape any quote marks in the form submission. If this step were omitted, quote marks would cause the SQL query to fail or malfunction. Next, an if statement checks that the variable $submitAdd has been set: if ($submitAdd) { 281

BLOG DESIGN SOLUTIONS foreach ($_POST as $key => $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”]:”"; ?> The first part of this code looks to see whether a PHP setting called magic quotes is turned on (this will vary from installation to installation and may differ from your local machine to the live web server set up by your ISP). If magic quotes is turned on, any single or double quote marks in your form submission will be escaped by a forward slash. For your code to always deal with the same input, this function needs to be reversed, so if magic quotes are turned on, the next few lines in the code step through each element in the $_POST array and remove the slashes so your input is as it was. The next portion of the script extracts all the form data from the $_POST array and writes it to handy form variables, using a shorthand if statement and the isset function to check whether the value has been set in the $_POST array. For example, the following shorthand code: $title = (isset($_POST[”title”]))?$_POST[”title”]:”"; could also be written as follows: if(isset($_POST[”title”])) { $title=$_POST[”title”]; } else { $title=”"; } Now that your PHP script has the submitted form data in its grasp, you are ready to insert your post into the database. For PHP to be able to talk to MySQL, it must first open a connection to the database server. The following code does just that: Copy these two lines of code into a new document and save them to your root directory as db_connect.php. This script will be reused on every page that needs to connect to the database so it makes sense to save it as an include. This way, instead of typing the script into each page, the documents can simply refer to the code in a separate file.

WRITE YOUR OWN BLOG ENGINE

Summary:

Post:

Create a new folder called cms in your website folder and save this HTML document into it, naming the file addpost.php. The extension .php is important because it tells the server that the file contains PHP scripting to process. You can view this page in a browser by going to http://127.0.0.1/cms/addpost.php. PHP code can be placed anywhere in the HTML document. The beginning and end of each block of PHP code is indicated by delimiters. The PHP code in this document needs to do two jobs: it will collect the post content from the HTML form and insert the post into the database. Your first piece of PHP code will make the form submit to itself. Add the following code to the form:

BLOG DESIGN SOLUTIONS Building the administration site You now have a database table in which you can store blog posts. At this point you could just use phpMyAdmin to add new posts to the table. It s not the most convenient of interfaces, however, so now I will show you how to build an administration site for your blog. The administration site will enable you to add and edit blog posts, as well as a few other cool things such as creating a Really Simple Syndication (RSS) feed. Figure 7-5 shows a schematic diagram of how the administration site will fit together. All the pages are available for download on the book s website at http://www.friendsofed.com. List of blog posts Add blog post Edit blog post Figure 7-5. Site map of the administration site Creating a screen for adding a post Logically, the first screen to create for your administration site should enable the addition of a new post by building an HTML form into which you can type your post and then using PHP to submit the contents of the form into the database. Here s the HTML mark-up for your form:

Add a post

Title:

Date/time: yyyy-mm-dd hh:mm:ss

WRITE YOUR OWN BLOG ENGINE Now it really is time to create the table in your database. In phpMyAdmin, make sure the database blog is selected in the menu on the left side and then click the Structure tab. In the Create new table on database blog area, type posts into the Name input box and 6 into the Fields input box, and then click Go. On the next screen, input the table definition for each field, as shown in Figure 7-3. Note that for the post_id field you should also select auto_increment in the Extra column and tick the primary column, which will ensure that each new post automatically gets its own unique id number. Figure 7-3. Creating the posts table in phpMyAdmin Now click Save, and the table should be created you ll see posts added to the left column (see Figure 7-4). Figure 7-4. Successfully creating the table posts in phpMyAdmin 277