PHP & MySQL: Novice to Ninja, 5th Edition (2012)

Chapter 7. A Content Management System

To make the leap from a web page that displays information stored in a database to a completely database driven website, we need to add a content management system (CMS). Such a system usually takes the form of a series of web pages, access to which is restricted to users who are authorized to make changes to the site. These pages provide a database administration interface that allows a user to view and change the information stored in the database without bothering with the mundane details of SQL queries. We built the beginnings of a CMS at the end of Chapter 4, where we allowed site visitors to add and delete jokes using a web-based form and a Delete button, respectively. While impressive, these are features that you’d normally exclude from the interface presented to casual site visitors. For example, you’d want to prevent visitors from adding offensive material to your website without your knowledge. And you definitely don’t want just anyone to be able to delete jokes from your site. By relegating those dangerous features to the restricted-access site administration pages, you avoid the risk of exposing your data to the average user, and you maintain the power to manage the contents of your database without having to memorize SQL queries. In this chapter, we’ll expand on the capabilities of our joke management system to take advantage of the enhancements we made to our database in Chapter 5. Specifically, we’ll allow a site administrator to manage authors and categories, and assign these to appropriate jokes. As we have seen, these administration pages must be protected by an appropriate access-restriction scheme. One approach would be to configure your web server to protect the relevant PHP files by prompting users for valid usernames and passwords. On Apache servers, you can do this with an .htaccess file that lists authorized users. Another method protects the administration pages with PHP itself. This option is generally more flexible and produces a much slicker result, but it takes a bit more work to set up. I’ll show you how it’s done in Chapter 9. For now, let’s focus on building the pages that will make up your CMS.

The Front Page

At the end of Chapter 5, your database contained tables for three types of entities: jokes, authors, and joke categories. This database layout is represented in Figure 7.1. Note that we’re sticking with our original assumption that we’ll have one email address per author.

Figure 7.1. The structure of the finished ijdb database contains three entities

If you need to recreate this table structure from scratch, here are the SQL queries to do so, along with some sample data:

chapter7/sql/ijdb.sql

CREATE TABLE joke (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

joketext TEXT,

jokedate DATE NOT NULL,

authorid INT

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE author (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255),

email VARCHAR(255)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE category (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

CREATE TABLE jokecategory (

jokeid INT NOT NULL,

categoryid INT NOT NULL,

PRIMARY KEY (jokeid, categoryid)

) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

# Sample data

# We specify the IDs so they are known when we add related entries

INSERT INTO author (id, name, email) VALUES

(1, 'Kevin Yank', 'thatguy@kevinyank.com'),

(2, 'Joan Smith', 'joan@example.com');

INSERT INTO joke (id, joketext, jokedate, authorid) VALUES

(1, 'Why did the chicken cross the road? To get to the other side!',

↵ '2012-04-01', 1),

(2, 'Knock-knock! Who\'s there? Boo! "Boo" who? Don\'t cry; it\'s

↵ only a joke!', '2012-04-01', 1),

(3, 'A man walks into a bar. "Ouch."', '2012-04-01', 2),

(4, 'How many lawyers does it take to screw in a lightbulb? I can\'t

↵ say: I might be sued!', '2012-04-01', 2);

INSERT INTO category (id, name) VALUES

(1, 'Knock-knock'),

(2, 'Cross the road'),

(3, 'Lawyers'),

(4, 'Walk the bar');

INSERT INTO jokecategory (jokeid, categoryid) VALUES

(1, 2),

(2, 1),

(3, 4),

(4, 3);

The front page of the content management system, therefore, will contain links to pages that manage these three entities. The following HTML code produces the index page shown in Figure 7.2:

chapter7/admin/index.html

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Joke CMS</title>

</head>

<body>

<h1>Joke Management System</h1>

<ul>

<li><a href="jokes/">Manage Jokes</a></li>

<li><a href="authors/">Manage Authors</a></li>

<li><a href="categories/">Manage Joke Categories</a></li>

</ul>

</body>

</html>

Figure 7.2. The Joke CMS index page offers three links

Each of these links points to a different subdirectory in our code: jokes, authors, and categories. Each directory will contain the controller (index.php) and associated templates needed to manage the corresponding entities in our database.

Managing Authors

Let’s begin with the code that will handle adding new authors, and deleting and editing existing ones. All of this code will go in the authors subdirectory. The first information we’ll present to an administrator needing to manage authors is a list of all authors currently stored in the database. Code-wise, this is the same as listing the jokes in the database. As we’ll want to allow administrators to delete and edit existing authors, we’ll include buttons for these actions next to each author’s name. Just like the Delete buttons we added at the end of Chapter 4, these buttons will send the ID of the associated author, so that the controller knows which author the administrator wishes to edit or delete. Finally, we’ll provide an Add new author link that leads to a form similar in operation to the Add your own joke link we created in Chapter 4. Here’s the controller code to do this:

chapter7/admin/authors/index.php (excerpt)

// Display author list

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$result = $pdo->query('SELECT id, name FROM author');

}

catch (PDOException $e)

{

$error = 'Error fetching authors from the database!';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$authors[] = array('id' => $row['id'], 'name' => $row['name']);

}

include 'authors.html.php';

There should be no surprises for you in this code, but do note that the database connection is created using the shared include file (db.inc.php) stored in the includes directory under the document root. Here’s the template that the code uses to display the list of authors:

chapter7/admin/authors/authors.html.php (excerpt)

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>(1)

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Manage Authors</title>

</head>

<body>

<h1>Manage Authors</h1>

<p><a href="?add">Add new author</a></p>(2)

<ul>

<?php foreach ($authors as $author): ?>

<li>

<form action="" method="post">(3)

<div>

<?php htmlout($author['name']); ?>(4)

<input type="hidden" name="id" value="<?php

echo $author['id']; ?>">

<input type="submit" name="action" value="Edit">(5)

<input type="submit" name="action" value="Delete">

</div>

</form>

</li>

<?php endforeach; ?>

</ul>

<p><a href="..">Return to JMS home</a></p>

</body>

</html>

Again, this code should be fairly familiar to you by now. A few points of interest:

Figure 7.3 shows the list of authors produced by this template.

Figure 7.3. The maintenance of author details begins with the Manage Authors interface

Deleting Authors

When the user clicks one of the Delete buttons, our controller should remove the corresponding author from the database using the author’s ID submitted with the form. As we’ve seen before, this is frighteningly easy to do, but there’s added complexity here. Remember that our joke table has an authorid column that indicates the author responsible for any given joke. When we remove an author from the database, we must also remove any references to that author in all tables. Otherwise, our database might contain jokes associated with nonexistent authors. We have three possible ways to handle this situation:

· We prohibit users from deleting authors that are associated with jokes in the database.

· When we delete an author, we also delete any jokes attributed to that author.

· When we delete an author, we set the authorid of any jokes attributed to that author to NULL to indicate that they have no author.

When we take measures like these to preserve the relationships in our database, we are said to be protecting the database’s referential integrity . MySQL, like most database servers, supports a feature called foreign key constraints that can do this automatically. By setting up these constraints, you can instruct MySQL to take any of the steps listed in order to keep your data properly related. We’ll look at foreign key constraints in Chapter 10, but we won’t use them here. If we did we’d be defining some of the behavior of our CMS in our PHP code, while defining other aspects of its behavior in our database design. If we did this, and then later decided that we wanted to change how deleting an author worked (for example, preventing the user from deleting authors with any jokes to their name), we’d need to remember to make adjustments in both places. Instead, we can keep our author-deleting logic all in our PHP code, making life easier for whoever might need to make changes to your code in the future (even if it’s you!). Since most authors would like us to give credit when using their jokes, we’ll choose the second option: delete all associated jokes when we delete an author. This also saves us from having to handle jokes with NULL values in the authorid column when we display our library of jokes. As we’ll be deleting jokes, there’s yet another layer of complexity to consider. Jokes may be assigned to categories by means of entries in the jokecategory table. When we delete jokes, we must also ensure that such entries are removed from the database. In summary, our controller will delete an author, any jokes belonging to that author, and any category assignments that pertain to those jokes. The code to do all this is rather lengthy, as you might imagine. Take your time to read through it and see if you can understand how it works:

chapter7/admin/authors/index.php (excerpt)

if (isset($_POST['action']) and $_POST['action'] == 'Delete')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

// Get jokes belonging to author

try

{

$sql = 'SELECT id FROM joke WHERE authorid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error getting list of jokes to delete.';

include 'error.html.php';

exit();

}

$result = $s->fetchAll();

// Delete joke category entries

try

{

$sql = 'DELETE FROM jokecategory WHERE jokeid = :id';

$s = $pdo->prepare($sql);

// For each joke

foreach ($result as $row)

{

$jokeId = $row['id'];

$s->bindValue(':id', $jokeId);

$s->execute();

}

}

catch (PDOException $e)

{

$error = 'Error deleting category entries for joke.';

include 'error.html.php';

exit();

}

// Delete jokes belonging to author

try

{

$sql = 'DELETE FROM joke WHERE authorid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting jokes for author.';

include 'error.html.php';

exit();

}

// Delete the author

try

{

$sql = 'DELETE FROM author WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting author.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

Although this code will be mostly familiar to you, there are a few new twists, which are highlighted in bold. The first element that may seem unfamiliar is the if statement that triggers it all:

chapter7/admin/authors/index.php (excerpt)

if (isset($_POST['action']) and $_POST['action'] == 'Delete')

As we saw in the section called “Managing Authors”, the user asks for an author to be deleted by clicking the Delete button next to the author name. Since the button’s name attribute is set to action, we can detect this button click by checking if $_POST['action'] is set, and if so, check if its value is 'Delete'. Next, there’s this statement:

chapter7/admin/authors/index.php (excerpt)

$result = $s->fetchAll();

At this point in the script, we’ve just executed a SELECT query to retrieve all the jokes belonging to the author that we’re about to delete. With this list of jokes in hand, we’re going to execute a series of DELETE queries, one for each joke, to delete all the category entries for that joke. But that’s the thing—we’re yet to actually have the list of jokes in hand! Let me explain. Normally when we perform a SELECT query, we use the condition of a while loop or a foreach loop to retrieve each row of the results, one at a time:

while ($row = $result->fetch())

foreach ($result as $row)

When we process the result of a query this way, PHP actually retrieves each row from the database as the loop requests it, and throws it away when it moves on to the next row. This saves PHP from having to use a lot of memory to hold onto all the rows of results at once. Most of the time, we developers have no need to know that PHP is taking this clever shortcut. But every once in a while, we’ll want to send another SQL query to the MySQL server before we’ve worked through all the results of the previous query. That’s exactly what’s about to happen in this code if we aren’t careful: we’ve just run a SELECT query to ask for a list of all jokes belonging to a particular author, and as we work through that list, we’d like to perform a DELETE query for each one. The problem is, as far as MySQL knows, it will still be busy sending us the results of the SELECT query; we can’t just interrupt it and ask for it to start running DELETEs! Doing so would cause our DELETEs to fail with an error. That’s where the fetchAll method comes in. By calling this method on our prepared statement ($s), we ask PHP to retrieve the entire set of results for the query and store them in a PHP array ($result):

chapter7/admin/authors/index.php (excerpt)

$result = $s->fetchAll();

We can now loop through this array with a foreach loop just as we’d normally loop through a PDOStatement object to retrieve each row one at a time. The difference now is that PHP will hold onto all the results at once, which frees us up to send more queries to MySQL. And that’s where the third novel piece of code in our author-deleting script comes in:

chapter7/admin/authors/index.php (excerpt)

// Delete joke category entries

try

{

$sql = 'DELETE FROM jokecategory WHERE jokeid = :id';

$s = $pdo->prepare($sql);

// For each joke

foreach ($result as $row)

{

$jokeId = $row['id'];

$s->bindValue(':id', $jokeId);

$s->execute();

}

}

With this code, we run a DELETE query to remove the jokecategory entries for each joke from the database. Your first impulse might be to begin with a foreach loop, but instead we start by creating a single prepared statement. This code demonstrates the second big advantage of prepared statements (which we first learned about in Chapter 4).^[42] Once you’ve prepared a statement, you can execute it over and over again, assigning its placeholders different values each time. In this case, we want to execute essentially the same DELETE query, but using a different joke ID in theWHERE clause each time. By using the same prepared statement for each of these queries, we can save MySQL the trouble of scrutinizing the SQL code for each query and coming up with a plan for how to do what we’re asking. Instead, MySQL reads the SQL code once when we prepare the statement; it figures out the most efficient way to perform such a DELETE operation, and then it simply executes that same plan over and over, using each of the IDs that we send it in turn. With this in mind, look again at this fragment of code: it should make a lot more sense now. First, it creates a prepared statement from the SQL code with a placeholder in it. Then it uses a foreach loop to work through the result set of the preceding SELECT query. It executes the newly prepared DELETE query once for each joke using bindValue to set the :id placeholder in the query to the joke’s ID. Make sense? Don’t worry if you have to read all that a couple of times to understand it. It’s some of the most complex PHP code you’re going to see in this book! When you’re satisfied, go ahead and try deleting one of the authors from your database. Use phpMyAdmin to verify that all the author’s jokes and their category entries are also deleted. The categories themselves should remain, even if they have no jokes left in them.

Tip: Confirm on Delete

As a challenge, try adding a confirmation prompt to this process. If you’ve yet to dive in and try some coding, use the code in the code archive for this chapter as a starting point. Modify your controller to respond to the Delete button by simply displaying another template, this one prompting the user to confirm the action. When the user submits the form in this page, it should trigger the code in the controller that actually deletes the data. This second form will also have to submit in a hidden field the ID of the author to be deleted.

Adding and Editing Authors

You could implement the Add new author link at the top of the author list page the same way you did the Add your own joke link in Chapter 4. Instead of prompting the user for the text of the joke, you’d instead prompt for the author’s name and email address. But our author management page includes a new, related feature: the ability to edit existing authors. Since both features will require the user to fill in a similar form, let’s tackle both at once and kill two birds with one stone. Here’s the code for the form template that will be used for both adding and editing authors:

chapter7/admin/authors/form.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title><?php htmlout($pageTitle); ?></title>

</head>

<body>

<h1><?php htmlout($pageTitle); ?></h1>

<form action="?<?php htmlout($action); ?>" method="post">

<div>

<label for="name">Name: <input type="text" name="name"

id="name" value="<?php htmlout($name); ?>"></label>

</div>

<div>

<label for="email">Email: <input type="text" name="email"

id="email" value="<?php htmlout($email); ?>"></label>

</div>

<div>

<input type="hidden" name="id" value="<?php

htmlout($id); ?>">

<input type="submit" value="<?php htmlout($button); ?>">

</div>

</form>

</body>

</html>

Note the six PHP variables that are inserted into the content of this page:

$pageTitle

sets the title and top-level heading (<h1>) for this page

$action

sets the value passed in the query string when the form is submitted

$name

sets the initial value of the form field for the author’s name

$email

sets the initial value of the form field for the author’s email address

$id

sets the value of the hidden form field for the author’s database ID

$button

sets the label of the form’s submit button

These variables enable us to use the form for two purposes: creating new authors and editing existing ones. Table 7.1 shows the values we’d like to assign to every variable in each instance.

Table 7.1. Variable values for dual-mode author form

So, here’s the controller code that loads the form in “new author mode” when the Add new author link is clicked:

chapter7/admin/authors/index.php (excerpt)

<?php

include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/magicquotes.inc.php';

if (isset($_GET['add']))

{

$pageTitle = 'New Author';

$action = 'addform';

$name = '';

$email = '';

$id = '';

$button = 'Add author';

include 'form.html.php';

exit();

}

When the user submits the form in this mode, you can detect it by watching for $_GET['addform']:

chapter7/admin/authors/index.php (excerpt)

if (isset($_GET['addform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'INSERT INTO author SET

name = :name,

email = :email';

$s = $pdo->prepare($sql);

$s->bindValue(':name', $_POST['name']);

$s->bindValue(':email', $_POST['email']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted author.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

When the user clicks one of the Edit buttons in the author list, you can use the same form, but this time you need to load the author’s existing details from the database:

chapter7/admin/authors/index.php (excerpt)

if (isset($_POST['action']) and $_POST['action'] == 'Edit')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'SELECT id, name, email FROM author WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error fetching author details.';

include 'error.html.php';

exit();

}

$row = $s->fetch();

$pageTitle = 'Edit Author';

$action = 'editform';

$name = $row['name'];

$email = $row['email'];

$id = $row['id'];

$button = 'Update author';

include 'form.html.php';

exit();

}

You can detect the form submitted in this mode by watching for $_GET['editform']. The code for processing this form submission is very similar to how you add a new author, but instead of issuing an INSERT query, it issues an UPDATE query:

chapter7/admin/authors/index.php (excerpt)

if (isset($_GET['editform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'UPDATE author SET

name = :name,

email = :email

WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->bindValue(':name', $_POST['name']);

$s->bindValue(':email', $_POST['email']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error updating submitted author.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

That’ll do the trick! Go ahead and try the completed author management system, which includes our new dual-mode form template shown in Figure 7.4. Make sure you can add, edit, and delete authors smoothly. If you see any error messages, go back and make sure you typed the code exactly as it appears here. If you become stuck, try using the completed code from the code archive and then compare it with your own.

Figure 7.4. I’ll bet she’s funny …

Managing Categories

The roles of the authors and joke categories in the database really are very similar. They both reside in tables of their own, and they both serve to group jokes together in some way. As a result, categories can be handled with code very similar to what we just developed for authors, but with one important exception. When we delete a category, we must avoid simultaneously deleting any jokes that belong to that category, because those jokes may also belong to other categories. We could check each joke to see if it belonged to any other categories, and only delete those that did not, but rather than engage in such a time-consuming process, let’s allow for the possibility of including jokes in our database with no assigned category. These jokes might be invisible to our site’s visitors (depending on how we decide to display jokes), but would remain in the database in case we wanted to assign them to a category later on. Thus, to delete a category, we also need to delete any entries in the jokecategory table that refer to that category:

chapter7/admin/categories/index.php (excerpt)

// Delete joke associations with this category

try

{

$sql = 'DELETE FROM jokecategory WHERE categoryid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error removing jokes from category.';

include 'error.html.php';

exit();

}

// Delete the category

try

{

$sql = 'DELETE FROM category WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting category.';

include 'error.html.php';

exit();

}

Other than this one detail, category management is functionally identical to author management. The complete code for the four files involved will follow. This code also relies on the shared include files db.inc.php, magicquotes.inc.php, and helpers.inc.php introduced in Chapter 6:

chapter7/admin/categories/index.php

<?php

include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/magicquotes.inc.php';

if (isset($_GET['add']))

{

$pageTitle = 'New Category';

$action = 'addform';

$name = '';

$id = '';

$button = 'Add category';

include 'form.html.php';

exit();

}

if (isset($_GET['addform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'INSERT INTO category SET

name = :name';

$s = $pdo->prepare($sql);

$s->bindValue(':name', $_POST['name']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted category.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

if (isset($_POST['action']) and $_POST['action'] == 'Edit')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'SELECT id, name FROM category WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error fetching category details.';

include 'error.html.php';

exit();

}

$row = $s->fetch();

$pageTitle = 'Edit Category';

$action = 'editform';

$name = $row['name'];

$id = $row['id'];

$button = 'Update category';

include 'form.html.php';

exit();

}

if (isset($_GET['editform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'UPDATE category SET

name = :name

WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->bindValue(':name', $_POST['name']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error updating submitted category.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

if (isset($_POST['action']) and $_POST['action'] == 'Delete')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

// Delete joke associations with this category

try

{

$sql = 'DELETE FROM jokecategory WHERE categoryid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error removing jokes from category.';

include 'error.html.php';

exit();

}

// Delete the category

try

{

$sql = 'DELETE FROM category WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting category.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

// Display category list

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$result = $pdo->query('SELECT id, name FROM category');

}

catch (PDOException $e)

{

$error = 'Error fetching categories from database!';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$categories[] = array('id' => $row['id'], 'name' => $row['name']);

}

include 'categories.html.php';

chapter7/admin/categories/categories.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Manage Categories</title>

</head>

<body>

<h1>Manage Categories</h1>

<p><a href="?add">Add new category</a></p>

<ul>

<?php foreach ($categories as $category): ?>

<li>

<form action="" method="post">

<div>

<?php htmlout($category['name']); ?>

<input type="hidden" name="id" value="<?php

echo $category['id']; ?>">

<input type="submit" name="action" value="Edit">

<input type="submit" name="action" value="Delete">

</div>

</form>

</li>

<?php endforeach; ?>

</ul>

<p><a href="..">Return to JMS home</a></p>

</body>

</html>

chapter7/admin/categories/form.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title><?php htmlout($pageTitle); ?></title>

</head>

<body>

<h1><?php htmlout($pageTitle); ?></h1>

<form action="?<?php htmlout($action); ?>" method="post">

<div>

<label for="name">Name: <input type="text" name="name"

id="name" value="<?php htmlout($name); ?>"></label>

</div>

<div>

<input type="hidden" name="id" value="<?php

htmlout($id); ?>">

<input type="submit" value="<?php htmlout($button); ?>">

</div>

</form>

</body>

</html>

chapter7/admin/categories/error.html.php

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Script Error</title>

</head>

<body>

<p>

<?php echo $error; ?>

</p>

</body>

</html>

Managing Jokes

Along with adding, deleting, and modifying jokes in our database, we also have to be able to assign categories and authors to our jokes. Furthermore, we’re likely to have many more jokes than authors or categories. To try to display a complete list of jokes, as we did for the authors and categories, could result in an unmanageably long list with no easy way to spot the joke we’re after. We need to create a more intelligent method of browsing our library of jokes.

Searching for Jokes

Sometimes we may know the category, author, or some of the text in a joke with which we want to work, so let’s support all these methods for finding jokes in our database. When we’re done, it should work like a simple search engine. The form that prompts the administrator for information about the desired joke must present lists of categories and authors. Let’s start with the controller code that fetches these details from the database:

chapter7/admin/jokes/index.php (excerpt)

// Display search form

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$result = $pdo->query('SELECT id, name FROM author');

}

catch (PDOException $e)

{

$error = 'Error fetching authors from database!';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$authors[] = array('id' => $row['id'], 'name' => $row['name']);

}

try

{

$result = $pdo->query('SELECT id, name FROM category');

}

catch (PDOException $e)

{

$error = 'Error fetching categories from database!';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$categories[] = array('id' => $row['id'], 'name' => $row['name']);

}

include 'searchform.html.php';

This code builds two arrays for use by the searchform.html.php template: $authors and $categories. We’ll use each of these arrays to build a drop-down list in our search form:

chapter7/admin/jokes/searchform.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Manage Jokes</title>

</head>

<body>

<h1>Manage Jokes</h1>

<p><a href="?add">Add new joke</a></p>

<form action="" method="get">

<p>View jokes satisfying the following criteria:</p>

<div>

<label for="author">By author:</label>

<select name="author" id="author">

<option value="">Any author</option>

<?php foreach ($authors as $author): ?>

<option value="<?php htmlout($author['id']); ?>"><?php

htmlout($author['name']); ?></option>

<?php endforeach; ?>

</select>

</div>

<div>

<label for="category">By category:</label>

<select name="category" id="category">

<option value="">Any category</option>

<?php foreach ($categories as $category): ?>

<option value="<?php htmlout($category['id']); ?>"><?php

htmlout($category['name']); ?></option>

<?php endforeach; ?>

</select>

</div>

<div>

<label for="text">Containing text:</label>

<input type="text" name="text" id="text">

</div>

<div>

<input type="hidden" name="action" value="search">

<input type="submit" value="Search">

</div>

</form>

<p><a href="..">Return to JMS home</a></p>

</body>

</html>

As you can see in each select list, we generate a series of option items using a PHP foreach loop. The value of each option is the author or category ID, and the text label of each option is the author or category name. Each of the drop-downs begins with an option with no value, which can be left alone to leave the corresponding field out of the search criteria. Note also that the form’s method attribute is set to get so that it’s possible to bookmark the results of a search, since the form values will be submitted in the URL query string. You should generally apply this method to any search form you write. The finished form appears in Figure 7.5. It’s up to the controller to use the values submitted by this form to build a list of jokes that satisfies the criteria specified. Obviously, this will be done with a SELECT query, but the exact nature of that query will depend on the search criteria specified. Because the building of this SELECT statement is a fairly complicated process, let’s work through the controller code responsible a little at a time.

Figure 7.5. Search for a classic

To start, we define a few strings that, when strung together, form the SELECT query we’d need if no search criteria whatsoever had been selected in the form:

chapter7/admin/jokes/index.php (excerpt)

if (isset($_GET['action']) and $_GET['action'] == 'search')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

// The basic SELECT statement

$select = 'SELECT id, joketext';

$from = ' FROM joke';

$where = ' WHERE TRUE';

You might find the WHERE clause in this code a little confusing. The idea here is for us to be able to build on this basic SELECT statement, depending on the criteria selected in the form. These criteria will require us to add to the FROM and WHERE clauses (portions) of the SELECT query. But, if no criteria were specified (that is, the administrator wanted a list of all jokes in the database), there would be no need for a WHERE clause at all! Because it’s difficult to add to a WHERE clause that’s nonexistent, we need to come up with a “do nothing” WHERE clause that will have no effect on the results unless added to. Since TRUE is always true, WHERE TRUE fits the bill nicely.^[43] Our next task is to check each of the possible constraints (author, category, and search text) that may have been submitted with the form, and adjust the three components of our SQL query accordingly. First, we deal with the possibility that an author was specified. The blank option in the form was given a value of "", so if the value of that form field (stored in $_GET['author']) is not equal to '' (the empty string), an author has been specified and we must adjust our query:

chapter7/admin/jokes/index.php (excerpt)

$placeholders = array();

if ($_GET['author'] != '') // An author is selected

{

$where .= " AND authorid = :authorid";

$placeholders[':authorid'] = $_GET['author'];

}

As we’ve seen before, .= (the append operator) is used to tack a new string onto the end of an existing one. In this case, we add to the WHERE clause the condition that the authorid in the joke table must match the value of a placeholder, :authorid. We’re going to want the value of that placeholder to be $_GET['author'], but we can’t use bindValue to set it, because we’re yet to have a prepared statement object to call it on; at this stage, our query is still spread across our three strings ($select, $from, and $where). Eventually, we’ll combine these strings together to create a prepared statement; in the meantime, we’ll store our placeholders in a PHP array variable ($placeholders), with the name of each placeholder as the array index. Next, we handle the specification of a joke category:

chapter7/admin/jokes/index.php (excerpt)

if ($_GET['category'] != '') // A category is selected

{

$from .= ' INNER JOIN jokecategory ON id = jokeid';

$where .= " AND categoryid = :categoryid";

$placeholders[':categoryid'] = $_GET['category'];

}

As the categories associated with a particular joke are stored in the jokecategory table, we need to add this table to the query to create a join. To do this, we simply tack INNER JOIN jokecategory ON id = jokeid onto the end of the $from variable. This joins the two tables on the condition that the id column (in the joke table) matches the jokeid column (in jokecategory). With the join in place, we can then apply the criterion specified in the form submission—that the joke belongs to the specified category. By adding to the $where variable, we can require the categoryid column (injokecategory) to match a particular category ID (:categoryid). Again, we store the value that we want to assign to this placeholder ($_GET['category']) in the $placeholders array. Handling search text is fairly simple thanks to the LIKE SQL operator that we learned way back in Chapter 2:

chapter7/admin/jokes/index.php (excerpt)

if ($_GET['text'] != '') // Some search text was specified

{

$where .= " AND joketext LIKE :joketext";

$placeholders[':joketext'] = '%' . $_GET['text'] . '%';

}

We add percent signs (%) to the start and end of $_GET['text'] to get our placeholder value in this case. Remember that LIKE treats percent signs as wildcards, so in this case we’re looking for joketext values that contain the value of $_GET['text'], but which may contain any other text before or after that value. Now that we’ve assembled the component parts of our SQL query, we can put them together and use it to retrieve and display our jokes:

chapter7/admin/jokes/index.php (excerpt)

try

{

$sql = $select . $from . $where;

$s = $pdo->prepare($sql);

$s->execute($placeholders);

}

catch (PDOException $e)

{

$error = 'Error fetching jokes.';

include 'error.html.php';

exit();

}

foreach ($s as $row)

{

$jokes[] = array('id' => $row['id'], 'text' =>

$row['joketext']);

}

include 'jokes.html.php';

exit();

}

Take particular notice of the line highlighted in bold. Because we have the values of all our placeholders stored in a PHP array variable ($placeholders), we can use a handy feature of the execute method: it lets us supply an array containing the values that we want to assign to the placeholders in our prepared statement, rather than having to use bindValue separately for each one. Slick! The template to display these jokes will include Edit and Delete buttons for each joke. To keep the page as organized as possible, it will structure the results using an HTML table:

chapter7/admin/jokes/jokes.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title>Manage Jokes: Search Results</title>

</head>

<body>

<h1>Search Results</h1>

<?php if (isset($jokes)): ?>

<table>

<tr><th>Joke Text</th><th>Options</th></tr>

<?php foreach ($jokes as $joke): ?>

<tr>

<td><?php htmlout($joke['text']); ?></td>

<td>

<form action="?" method="post">

<div>

<input type="hidden" name="id" value="<?php

htmlout($joke['id']); ?>">

<input type="submit" name="action" value="Edit">

<input type="submit" name="action" value="Delete">

</div>

</form>

</td>

</tr>

<?php endforeach; ?>

</table>

<?php endif; ?>

<p><a href="?">New search</a></p>

<p><a href="..">Return to JMS home</a></p>

</body>

</html>

The search results will display as shown in Figure 7.6.

Figure 7.6. A classic is found

Tip: Nothing to Report

If you’re up for a challenge, try adding a little code to this template to gracefully handle the case where no jokes satisfy the criteria specified in the search form. Right now, the template simply outputs nothing where the search results table should be.

Adding and Editing Jokes

At the top of the joke search form, we have our usual link to create a new joke:

chapter7/admin/jokes/searchform.html.php (excerpt)

<p><a href="?add">Add new joke</a></p>

Let’s implement this feature now. The code will be very similar to what we used to create new authors and categories, but in addition to specifying the joke text, the page must allow an administrator to assign an author and categories to a joke. As with authors and categories, we can use the same form template for creating new jokes and editing existing jokes. Let’s take a look at the important elements of this form. We begin with a standard text area into which we can type the text of the joke. If we’re editing an existing joke, we’ll populate this field with that joke’s text ($text):

chapter7/admin/jokes/form.html.php (excerpt)

<div>

<label for="text">Type your joke here:</label>

<textarea id="text" name="text" rows="3" cols="40"><?php

htmlout($text); ?></textarea>

</div>

Next, we’ll prompt the administrator to select the author who wrote the joke:

chapter7/admin/jokes/form.html.php (excerpt)

<div>

<label for="author">Author:</label>

<select name="author" id="author">

<option value="">Select one</option>

<?php foreach ($authors as $author): ?>

<option value="<?php htmlout($author['id']); ?>"<?php

if ($author['id'] == $authorid)

{

echo ' selected';

}

?>><?php htmlout($author['name']); ?></option>

<?php endforeach; ?>

</select>

</div>

Again, we’ve seen this kind of drop-down before (for example, in the joke search form), but the important difference is that we want to control the initial selection in the drop-down menu when we’re editing an existing joke. The code in bold inserts the attribute selected into the <option> tag if the ID of the corresponding author ($author['id']) matches the author ID of the existing joke ($authorid). Next, we need to prompt the administrator to select the categories the joke should belong to. A drop-down list is unsuitable because we want the administrator to be able to selectmultiple categories. Thus, we’ll use a series of checkboxes (<input type="checkbox">)—one for each category. Since we have no way of knowing in advance the number of checkboxes we’ll need, the matter of setting their name attribute becomes an interesting challenge. What we’ll do is use a single variable for all the checkboxes; thus, all the checkboxes will have the same name. To be able to receive multiple values from a single variable name, we must make that variable an array. Recall from Chapter 3 that an array is a single variable with compartments, each of which can hold a value. To submit a form element as part of an array variable, we simply add a pair of square brackets to the end of the name attribute (making it categories[] in this case).

Tip: A Multiple Selection List

Another way to submit an array is with a <select multiple="multiple"> tag. Again, you’d set the name attribute to end with square brackets. What will be submitted is an array of all the option values selected from the list by the user. Feel free to experiment with this approach by modifying the form to present the categories as a list of option elements; however, be aware that many users won’t realize that they’re able to select multiple options from the list by holding down Ctrl (⌘ on a Mac).

With all of our checkboxes named the same, we’ll need a way to identify which particular checkboxes have been selected. To this end, we assign a different value to each checkbox—the ID of the corresponding category in the database. Thus, the form submits an array containing the IDs of all the categories to which the new joke should be added. Again, since we need to edit an existing joke, we’ll include some code to output selected if the joke already belongs to the corresponding category. This we’ll indicate in our controller by setting $category['selected'] to TRUE:

chapter7/admin/jokes/form.html.php (excerpt)

<fieldset>

<legend>Categories:</legend>

<?php foreach ($categories as $category): ?>

<div><label for="category<?php htmlout($category['id']);

?>"><input type="checkbox" name="categories[]"

id="category<?php htmlout($category['id']); ?>"

value="<?php htmlout($category['id']); ?>"<?php

if ($category['selected'])

{

echo ' checked';

}

?>><?php htmlout($category['name']); ?></label></div>

<?php endforeach; ?>

</fieldset>

Other than these details, the form will work just like the other add/edit forms we’ve built. Here’s the complete code:

chapter7/admin/jokes/form.html.php

<?php include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/helpers.inc.php'; ?>

<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="utf-8">

<title><?php htmlout($pageTitle); ?></title>

<style type="text/css">

textarea {

display: block;

width: 100%;

}

</style>

</head>

<body>

<h1><?php htmlout($pageTitle); ?></h1>

<form action="?<?php htmlout($action); ?>" method="post">

<div>

<label for="text">Type your joke here:</label>

<textarea id="text" name="text" rows="3" cols="40"><?php

htmlout($text); ?></textarea>

</div>

<div>

<label for="author">Author:</label>

<select name="author" id="author">

<option value="">Select one</option>

<?php foreach ($authors as $author): ?>

<option value="<?php htmlout($author['id']); ?>"<?php

if ($author['id'] == $authorid)

{

echo ' selected';

}

?>><?php htmlout($author['name']); ?></option>

<?php endforeach; ?>

</select>

</div>

<fieldset>

<legend>Categories:</legend>

<?php foreach ($categories as $category): ?>

<div><label for="category<?php htmlout($category['id']);

?>"><input type="checkbox" name="categories[]"

id="category<?php htmlout($category['id']); ?>"

value="<?php htmlout($category['id']); ?>"<?php

if ($category['selected'])

{

echo ' checked';

}

?>><?php htmlout($category['name']); ?></label></div>

<?php endforeach; ?>

</fieldset>

<div>

<input type="hidden" name="id" value="<?php

htmlout($id); ?>">

<input type="submit" value="<?php htmlout($button); ?>">

</div>

</form>

</body>

</html>

Figure 7.7 shows what the form will look like.

Figure 7.7. The hits just keep on coming

Let’s now turn our attention back to the controller, which will display and then handle the submission of this form in both its modes. When the user clicks the Add new joke link, we need to display the form with all its fields blank. None of this code should be unfamiliar. Take your time, look over it, and make sure it all makes sense to you. If you’re unsure what a particular variable is for, go find it in the form template and identify its purpose:

chapter7/admin/jokes/index.php (excerpt)

<?php

include_once $_SERVER['DOCUMENT_ROOT'] .

'/includes/magicquotes.inc.php';

if (isset($_GET['add']))

{

$pageTitle = 'New Joke';

$action = 'addform';

$text = '';

$authorid = '';

$id = '';

$button = 'Add joke';

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

// Build the list of authors

try

{

$result = $pdo->query('SELECT id, name FROM author');

}

catch (PDOException $e)

{

$error = 'Error fetching list of authors.';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$authors[] = array('id' => $row['id'], 'name' => $row['name']);

}

// Build the list of categories

try

{

$result = $pdo->query('SELECT id, name FROM category');

}

catch (PDOException $e)

{

$error = 'Error fetching list of categories.';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$categories[] = array(

'id' => $row['id'],

'name' => $row['name'],

'selected' => FALSE);

}

include 'form.html.php';

exit();

}

Note especially that we’re setting the 'selected' item in each of the arrays stored in the $categories array to FALSE. As a result, none of the category checkboxes in the form will be selected by default. When the user clicks the Edit button next to an existing joke, the controller must instead load the form with its fields populated with the existing values. This code is similar in structure to the code we used to generate the empty form:

chapter7/admin/jokes/index.php (excerpt)

if (isset($_POST['action']) and $_POST['action'] == 'Edit')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

try

{

$sql = 'SELECT id, joketext, authorid FROM joke WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error fetching joke details.';

include 'error.html.php';

exit();

}

$row = $s->fetch();

$pageTitle = 'Edit Joke';

$action = 'editform';

$text = $row['joketext'];

$authorid = $row['authorid'];

$id = $row['id'];

$button = 'Update joke';

// Build the list of authors

try

{

$result = $pdo->query('SELECT id, name FROM author');

}

catch (PDOException $e)

{

$error = 'Error fetching list of authors.';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$authors[] = array('id' => $row['id'], 'name' => $row['name']);

}

// Get list of categories containing this joke

try

{

$sql = 'SELECT categoryid FROM jokecategory

↵WHERE jokeid = :id';(1)

$s = $pdo->prepare($sql);

$s->bindValue(':id', $id);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error fetching list of selected categories.';

include 'error.html.php';

exit();

}

foreach ($s as $row)

{

$selectedCategories[] = $row['categoryid'];(2)

}

// Build the list of all categories

try

{

$result = $pdo->query('SELECT id, name FROM category');

}

catch (PDOException $e)

{

$error = 'Error fetching list of categories.';

include 'error.html.php';

exit();

}

foreach ($result as $row)

{

$categories[] = array(

'id' => $row['id'],

'name' => $row['name'],

'selected' => in_array($row['id'], $selectedCategories));(3)

}

include 'form.html.php';

exit();

}

In addition to fetching the details of the joke (ID, text, and author ID), this code fetches a list of categories to which the joke in question belongs:

That takes care of generating the form in each of its two modes; now let’s look at the controller code that processes the form submissions. Since we’re submitting an array for the first time (the list of selected category checkboxes), the code processing this form will feature a couple of new tricks as well. It starts off fairly simply as we add the joke to the joke table. As an author is required, we make sure that $_POST['author'] contains a value. This prevents the administrator from choosing the Select One option in the author select list (that choice has a value of "", the empty string):

chapter7/admin/jokes/index.php (excerpt)

if (isset($_GET['addform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

if ($_POST['author'] == '')

{

$error = 'You must choose an author for this joke.

Click ‘back’ and try again.';

include 'error.html.php';

exit();

}

try

{

$sql = 'INSERT INTO joke SET

joketext = :joketext,

jokedate = CURDATE(),

authorid = :authorid';

$s = $pdo->prepare($sql);

$s->bindValue(':joketext', $_POST['text']);

$s->bindValue(':authorid', $_POST['author']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted joke.';

include 'error.html.php';

exit();

}

$jokeid = $pdo->lastInsertId();

The last line in the above code uses a method that we’ve yet to see: lastInsertId. This method returns the number assigned to the last inserted entry by the AUTO_INCREMENT feature in MySQL. In other words, it retrieves the ID of the newly inserted joke, which we’ll need in a moment. I expect you’re a little foggy on how to write the code that adds the entries to jokecategory based on which checkboxes were checked. First of all, we’ve never seen how a checkbox passes its value to a PHP variable before. Additionally, we need to deal with the fact that these particular checkboxes will submit into an array variable. A typical checkbox will pass its value to a PHP variable if it’s checked, and will do nothing when it’s unchecked. Checkboxes without assigned values pass 'on' as the value of their corresponding variables when they’re checked. However, we’ve assigned values to our checkboxes (the category IDs), so this isn’t an issue. The fact that these checkboxes submit into an array actually adds quite a measure of convenience to our code. In essence, we’ll receive from the submitted form either:

· an array of category IDs to which we’ll add the joke

· nothing at all (if none of the checkboxes were checked)

In the latter case, there’s nothing to do—no categories were selected, so we have nothing to add to the jokecategory table. If we do have an array of category IDs to process, however, we’ll use a foreach loop to issue an INSERT query for each ID (using a single prepared statement):

chapter7/admin/jokes/index.php (excerpt)

if (isset($_POST['categories']))

{

try

{

$sql = 'INSERT INTO jokecategory SET

jokeid = :jokeid,

categoryid = :categoryid';

$s = $pdo->prepare($sql);

foreach ($_POST['categories'] as $categoryid)

{

$s->bindValue(':jokeid', $jokeid);

$s->bindValue(':categoryid', $categoryid);

$s->execute();

}

}

catch (PDOException $e)

{

$error = 'Error inserting joke into selected categories.';

include 'error.html.php';

exit();

}

}

header('Location: .');

exit();

}

Note the use of the $jokeid variable, which we obtained from lastInsertId. That takes care of adding new jokes. The form processing code for editing existing jokes is predictably similar, with two important differences:

· It uses an UPDATE query instead of an INSERT query to store the joke’s details in the joke table.

· It removes all existing entries for the joke from the jokecategory table before INSERTing entries for the selected checkboxes in the form.

Here’s the code. Take the time to read through it and make sure it all makes sense to you:

chapter7/admin/jokes/index.php (excerpt)

if (isset($_GET['editform']))

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

if ($_POST['author'] == '')

{

$error = 'You must choose an author for this joke.

Click ‘back’ and try again.';

include 'error.html.php';

exit();

}

try

{

$sql = 'UPDATE joke SET

joketext = :joketext,

authorid = :authorid

WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->bindValue(':joketext', $_POST['text']);

$s->bindValue(':authorid', $_POST['author']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error updating submitted joke.';

include 'error.html.php';

exit();

}

try

{

$sql = 'DELETE FROM jokecategory WHERE jokeid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error removing obsolete joke category entries.';

include 'error.html.php';

exit();

}

if (isset($_POST['categories']))

{

try

{

$sql = 'INSERT INTO jokecategory SET

jokeid = :jokeid,

categoryid = :categoryid';

$s = $pdo->prepare($sql);

foreach ($_POST['categories'] as $categoryid)

{

$s->bindValue(':jokeid', $_POST['id']);

$s->bindValue(':categoryid', $categoryid);

$s->execute();

}

}

catch (PDOException $e)

{

$error = 'Error inserting joke into selected categories.';

include 'error.html.php';

exit();

}

}

header('Location: .');

exit();

}

Deleting Jokes

The last feature to implement is the Delete button displayed next to each joke. The controller code responsible for this feature mirrors the code we wrote for the author and category Delete buttons, with only minor adjustments. For example, besides deleting the selected joke from the joketable, it must also remove any entries in the jokecategory table for that joke. Here’s the code. There’s nothing new here, but take a look and make sure you’re comfortable with everything that’s going on:

chapter7/admin/jokes/index.php (excerpt)

if (isset($_POST['action']) and $_POST['action'] == 'Delete')

{

include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

// Delete category assignments for this joke

try

{

$sql = 'DELETE FROM jokecategory WHERE jokeid = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error removing joke from categories.';

include 'error.html.php';

exit();

}

// Delete the joke

try

{

$sql = 'DELETE FROM joke WHERE id = :id';

$s = $pdo->prepare($sql);

$s->bindValue(':id', $_POST['id']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error deleting joke.';

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

Summary

There are still a few minor tasks that our content management system is incapable of. For example, it’s unable to provide a listing of just the jokes that don’t belong to any category—and this listing could be very handy as the number of jokes in the database grows. You might also like to sort the joke lists by various criteria. These particular capabilities require a few more advanced SQL tricks that we’ll see in Chapter 11.

Note: Some Code We’ve Left Behind

If you were to scrutinize closely the code archive for this chapter, you might notice that I’ve also tweaked the joke list page (in the joke folder) to remove the Add your own joke link and the Delete buttons. Because these features weren’t designed to work with the new database structure we developed in this chapter, I’ve removed them for now. In the section called “A Challenge: Joke Moderation” in Chapter 9, I challenge you to find a way to handle user-submitted jokes in an elegant way.

If we ignore these little details for the moment, you’ll see that you now have a system that allows a person without SQL or database knowledge to administer your database of jokes with ease! Together with a set of PHP-powered pages through which regular site visitors can view the jokes, this CMS allows us to set up a complete database driven website that can be maintained by a user with absolutely no database knowledge. And if you think that sounds like a valuable commodity to businesses looking to be on the Web today, you’re right! In fact, only one aspect of our site requires users to have special knowledge (beyond the use of a web browser): content formatting. If we wanted to enable administrators to include rich-text formatting in the jokes they entered, we could invite them to type the necessary HTML code directly into the New Joke form. To preserve this formatting, we’d then echo out the content of our jokes “raw” instead of using our htmlout function. This is unacceptable for two reasons: first, we’d have to stop accepting joke submissions from the general public, otherwise we’d be opening the door to attackers submitting harmful code in their jokes; our site would then display these unfiltered, since we’d no longer be passing our content through htmlspecialchars. Second, as we stated way back in the introduction to this book, one of the most desirable features of a database driven website is that people can be responsible for adding content despite being unfamiliar with technical mumbo jumbo like HTML. If we require knowledge of HTML for a task as simple as dividing a joke into paragraphs, or applying italics to a word or two, we’ll have failed to achieve our goal. In Chapter 8, I’ll show you how to use some of the features of PHP that make it simpler for your users to format content without knowing the ins and outs of HTML. We’ll also revisit the Submit your own joke form, and discover how we can safely accept content submissions from casual site visitors.