Building a Database-Driven Web Site Using PHP and MySQL

Challenge Solution

Here's the solution to the "homework" challenge posed above. The following changes were required to add a "Delete this Joke" link next to each joke:

Previously, we passed an $addjoke variable with our "Add a Joke!" link at the bottom of the page to signal that our script should display the joke entry form instead of the usual list of jokes. In similar fashion, we pass a $deletejoke variable with our "Delete this Joke" link to indicate our desire to have a joke deleted.
We fetch the ID column from the database for each joke along with the JokeText column, so that we have the ID associated with each joke in the database.
We set the value of the $deletejoke variable to the ID of the joke being deleted. This was done by inserting the ID value fetched from the database into the HTML code for the "Delete this Joke" link of each joke.
Using an if statement, we watch to see if $deletejoke is set to some value (using the isset function) when loading the page. If it is, we use the value that it is set to (the ID of the joke to be deleted) in an SQL DELETE statement to delete the joke in question.

Here's the complete code. If you have any questions, don't hesitate to post them in the SitePoint.com forums!

<HTML>
...
<BODY>
<?php
  // If the user wants to add a joke
  if (isset($addjoke)):
?>

<FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
<P>Type your joke here:<BR>
<TEXTAREA NAME="joketext" ROWS=10 COLS=40 WRAP>
</TEXTAREA><BR>
<INPUT TYPE=SUBMIT NAME="submitjoke" VALUE="SUBMIT">
</FORM>
<?php
  else:

    // Connect to the database server
    $dbcnx = @mysql_connect(
               "localhost", "root", "mypasswd");
    if (!$dbcnx) {
      echo( "<P>Unable to connect to the " .
            "database server at this time.</P>" );
      exit();
    }

    // Select the jokes database
    if (! @mysql_select_db("jokes") ) {
      echo( "<P>Unable to locate the joke " .
            "database at this time.</P>" );
      exit();
    }

    // If a joke has been submitted,
    // add it to the database.
    if ("SUBMIT" == $submitjoke) {
      $sql = "INSERT INTO Jokes SET " .
             "JokeText='$joketext', " .
             "JokeDate=CURDATE()";
      if (mysql_query($sql)) {
        echo("<P>Your joke has been added.</P>");
      } else {
        echo("<P>Error adding submitted joke: " .
             mysql_error() . "</P>");
      }
    }

    // If a joke has been deleted,
    // remove it from the database.
    if (isset($deletejoke)) {
      $sql = "DELETE FROM Jokes " .
             "WHERE ID=$deletejoke";
      if (mysql_query($sql)) {
        echo("<P>The joke has been deleted.</P>");
      } else {
        echo("<P>Error deleting joke: " .
             mysql_error() . "</P>");
      }
    }
  
    echo("<P> Here are all the jokes " .
         "in our database: </P>");
  
    // Request the ID and text of all the jokes
    $result = mysql_query(
                "SELECT ID, JokeText FROM Jokes");
    if (!$result) {
      echo("<P>Error performing query: " .
           mysql_error() . "</P>");
      exit();
    }
  
    // Display the text of each joke in a paragraph
    // with a "Delete this Joke" link next to each.
    while ( $row = mysql_fetch_array($result) ) {
      $jokeid = $row["ID"];
      $joketext = $row["JokeText"];
      echo("<P>$joketext " .
           "<A HREF='$PHP_SELF?deletejoke=$jokeid'>" .
           "Delete this Joke</A></P>");
    }
  
    // When clicked, this link will load this page
    // with the joke submission form displayed.
    echo("<P><A HREF='$PHP_SELF?addjoke=1'>" .
         "Add a Joke!</A></P>");
  
  endif;
  
?>
</BODY>
</HTML>

SitePoint.com is a fast growing Web Developer Community. Kevin Yank is the Editor of the SitePoint TechTimes, a fresh, technically oriented newsletter for the serious Webmaster.

Related Pages:

Building a Database-Driven Web Site Using PHP and MySQL