Build Your Own Database Driven Web Site Using PHP & MySQL, Part 4: Publishing MySQL Data on the Web

Deleting Data from the Database

In this section, we’ll make one final enhancement to our joke database site. We’ll place next to each joke on the page a button labeled Delete that, when clicked, will remove that joke from the database and display the updated joke list.

If you like a challenge, you might want to take a stab at writing this feature yourself before you read on to see my solution. Although we’re implementing a brand new feature, we’ll mainly be using the same tools that we have for the previous examples in this chapter. Here are a few hints to start you off:

• You’ll still be able to do it all with a single controller script (index.php).
• You’ll need to use the SQL DELETE command, which I introduced in Chapter 2, Introducing MySQL.
• To delete a particular joke in your controller, you’ll need to identify it uniquely. The id column in the joke table was created to serve this purpose. You’re going to have to pass the ID of the joke to be deleted with the request to delete a joke. The easiest way to do this is to use a hidden form field.

At the very least, take a few moments to think about how you would approach this. When you’re ready to see the solution, read on!

To begin with, we need to modify the SELECT query that fetches the list of jokes from the database. In addition to the joketext column, we must also fetch the id column, so we can identify each joke uniquely:

$result = mysqli_query($link, 'SELECT id, joketext FROM joke');    
if (!$result)    
{    
 $error = 'Error fetching jokes: ' . mysqli_error($link);    
 include 'error.html.php';    
 exit();    
}

We must also modify the while loop that stores the database results in the $jokes array. Instead of simply storing the text of each joke as an item in the array, we must store both the ID and text of each joke. One way to do this is to make each item in the $jokes array an array in its own right:

while ($row = mysqli_fetch_array($result))    
{    
 $jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);    
}

Once this while loop runs its course, we’ll have the $jokes array, each item of which is an associative array with two items: the ID of the joke and its text. For each joke ($jokes[n]), we can therefore retrieve its ID ($jokes[n]['id']) and its text ($jokes[n]['text']).

Our next step, then, should be to update the jokes.html.php template to retrieve each joke’s text from this new array structure, and also to provide a Delete button for each joke:

<?php foreach ($jokes as $joke): ?>    
 <form action="?deletejoke" method="post">1    
   <blockquote>    
     <p>    
       <?php echo htmlspecialchars($joke['text'], ENT_QUOTES,2    
           'UTF-8'); ?>    
       <input type="hidden" name="id" value="<?php    
           echo $joke['id']; ?>"/>3    
       <input type="submit" value="Delete"/>4    
     </p>    
   </blockquote>    
 </form>5    
<?php endforeach; ?>

Here are the highlights of this updated code:

• Each joke will be displayed in a form, which, if submitted, will delete that joke. We signal this to our controller using the ?deletejoke query string in the action attribute.
• Since each joke in the $jokes array is now represented by a two-item array instead of a simple string, we must update this line to retrieve the text of the joke. We do this using $joke['text'] instead of just $joke.
• When we submit the form to delete this joke, we wish to send along the ID of the joke to be deleted. To do this, we need a form field containing the joke’s ID, but this is a field we’d prefer to keep hidden from the user. We therefore using a hidden form field (<input type="hidden"/>). The name of this field is id, and its value is the ID of the joke to be deleted ($joke['id']). Unlike the text of the joke, the ID is not a user-submitted value, so there’s no need to worry about making it HTML-safe with htmlspecialchars. We can rest assured it will be a number, since it’s automatically generated by MySQL for the id column when the joke is added to the database.
• This submit button (<input type="submit"/>) submits the form when clicked. Its value attribute gives it a label of Delete.

This Markup Could Be Better

If you know your HTML, you’re probably thinking those <input/> tags belong outside of the blockquote element, since they aren’t a part of the quoted text (the joke).

Strictly speaking, that’s true: the form and its inputs should really be either before or after the blockquote. Unfortunately, to make that tag structure display clearly requires a little Cascading Style Sheets (CSS) code that’s really beyond the scope of this book.

Rather than attempt to teach you CSS layout techniques in a book about PHP and MySQL, I’ve decided to go with this imperfect markup. If you plan to use this code in the real world, you should invest some time into learning CSS (or securing the services of a person who does) so that you can take complete control of your HTML markup without worrying about the CSS code required to make it look nice.

The figure below shows what the joke list looks like with the Delete buttons added.

All that remains to make this new feature work is to update the controller so that it can process the form submission that results from clicking one of our new Delete buttons:

if (isset($_GET['deletejoke']))    
{    
 $id = mysqli_real_escape_string($link, $_POST['id']);    
 $sql = "DELETE FROM joke WHERE id='$id'";    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error deleting joke: ' . mysqli_error($link);    
   include 'error.html.php';    
   exit();    
 }    
     
 header('Location: .');    
 exit();    
}

This chunk of code works exactly like the one we added to process the “Add Joke” code earlier in this chapter. We start by using mysqli_real_escape_string to sanitize the submitted value of $_POST['id'] before using it in a database query—this time, a DELETE query. You might think it’s unnecessary to sanitize this value, since it’s produced by a hidden form field that the user is unable to see. In fact, however, all form fields—even hidden ones—are ultimately under the user’s control. There are widely distributed browser add-ons, for example, that will make hidden form fields visible and available for editing by the user. Remember: any value submitted by the browser is ultimately suspect when it comes to protecting your site’s security. Once that query is executed, we use the PHP header function to ask the browser to send a new request to view the updated list of jokes.

Why Not a Link?

If you tackled this example yourself, your first instinct might have been to provide a Delete hyperlink for each joke, instead of going to the trouble of writing an entire HTML form containing a Delete button for each joke on the page. Indeed, the code for such a link would be much simpler:

<?php foreach ($jokes as $joke): ?>    
 <blockquote>    
   <p>    
     <?php echo htmlspecialchars($joke['text'], ENT_QUOTES,    
         'UTF-8'); ?>    
     <a href="?deletejoke&amp;id=<?php echo $joke['id'];    
         ?>">Delete</a>    
   </p>    
 </blockquote>    
<?php endforeach; ?>

In short, hyperlinks should never be used to perform actions (like deleting a joke); hyperlinks should only be used to provide a link to some related content. The same goes for forms with method="get", which should only be used to perform queries of existing data. Actions should only ever be performed as a result of a form with method="post" being submitted.

The reason is that forms with method="post" are treated differently by browsers and related software. If you submit a form with method="post" and then click the Refresh button in your browser, for example, the browser will ask if you’re certain you wish to resubmit the form. Browsers have no similar protection against resubmission when it comes to links and forms with method="get".

Similarly, web accelerator software (and some modern browsers) will automatically follow hyperlinks present on a page in the background, so that the target pages will be available for immediate display if the user clicks one of those links. If your site deleted a joke as a result of a hyperlink being followed, you could find your jokes getting deleted automatically by your users’ browsers!

Here’s the complete code of the finished controller. If you have any questions, make sure to post them in the SitePoint Forums!

<?php    
if (get_magic_quotes_gpc())    
{    
 function stripslashes_deep($value)    
 {    
   $value = is_array($value) ?    
       array_map('stripslashes_deep', $value) :    
       stripslashes($value);    
   
   return $value;    
 }    
   
 $_POST = array_map('stripslashes_deep', $_POST);    
 $_GET = array_map('stripslashes_deep', $_GET);    
 $_COOKIE = array_map('stripslashes_deep', $_COOKIE);    
 $_REQUEST = array_map('stripslashes_deep', $_REQUEST);    
}    
   
if (isset($_GET['addjoke']))    
{    
 include 'form.html.php';    
 exit();    
}    
   
$link = mysqli_connect('localhost', 'root', 'password');    
if (!$link)    
{    
 $error = 'Unable to connect to the database server.';    
 include 'error.html.php';    
 exit();    
}    
   
if (!mysqli_set_charset($link, 'utf8'))    
{    
 $output = 'Unable to set database connection encoding.';    
 include 'output.html.php';    
 exit();    
}    
   
if (!mysqli_select_db($link, 'ijdb'))    
{    
 $error = 'Unable to locate the joke database.';    
 include 'error.html.php';    
 exit();    
}    
   
if (isset($_POST['joketext']))    
{    
 $joketext = mysqli_real_escape_string($link, $_POST['joketext']);    
 $sql = 'INSERT INTO joke SET    
     joketext="' . $joketext . '",    
     jokedate=CURDATE()';    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error adding submitted joke: ' . mysqli_error($link);    
   include 'error.html.php';    
   exit();    
 }    
     
 header('Location: .');    
 exit();    
}    
   
if (isset($_GET['deletejoke']))    
{    
 $id = mysqli_real_escape_string($link, $_POST['id']);    
 $sql = "DELETE FROM joke WHERE id='$id'";    
 if (!mysqli_query($link, $sql))    
 {    
   $error = 'Error deleting joke: ' . mysqli_error($link);    
   include 'error.html.php';    
   exit();    
 }    
   
 header('Location: .');    
 exit();    
}    
   
$result = mysqli_query($link, 'SELECT id, joketext FROM joke');    
if (!$result)    
{    
 $error = 'Error fetching jokes: ' . mysqli_error($link);    
 include 'error.html.php';    
 exit();    
}    
   
while ($row = mysqli_fetch_array($result))    
{    
 $jokes[] = array('id' => $row['id'], 'text' => $row['joketext']);    
}    
   
include 'jokes.html.php';    
?>

Mission Accomplished

In this chapter, you learned some new PHP functions that allow you to interface with a MySQL database server. Using these functions, you built your first database driven web site, which published the ijdb database online, and allowed visitors to add jokes to it and delete jokes from it.

In a way, you could say this chapter achieved the stated mission of this book, to teach you how to build a database driven web site. Of course, the example in this chapter contains only the bare essentials. In the rest of this book, I’ll show you how to flesh out the skeleton you learned to build in this chapter.

In Chapter 5, Relational Database Design, we go back to the MySQL command line. We’ll learn how to use relational database principles and advanced SQL queries to represent more complex types of information, and give our visitors credit for the jokes they add!

But to read that of course, you'll have to buy the book. What are you waiting for?