dcsimg

Inserting Data into the Database - Page 5

January 24, 2002

In this section, we'll see how we can use all the tools at our disposal to allow visitors to our site to add their own jokes to the database. If you enjoy a challenge, you might want to try to figure this out on your own before you read any further. There is precious little new material in this section. It's mostly just a sample application of everything we've learned so far.

If you want to let visitors to your site type in new jokes, you'll obviously need a form. Here's the code for a form that will fit the bill:

<form action="<?=$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" /></p>
</form>

As we've seen before, this form, when submitted, will load the very same page (due to the use of the $PHP_SELF variable for the form's action attribute), but with two variables attached to the request. The first, $joketext, will contain the text of the joke as typed into the text area. The second, $submitjoke, will always contain the value "SUBMIT", which can be used as a sign that a joke has been submitted.

To insert the submitted joke into the database, we just use mysql_query to run an INSERT query, using the $joketext variable for the value to be submitted:

if ($submitjoke == "SUBMIT") {
  $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>");
  }
}

The one new trick in this whole example appears in the SQL code here. Note the use of the MySQL function CURDATE() to assign the current date as the value of the JokeDate column to be inserted into the database. MySQL actually has dozens of these functions, but we'll only introduce them as required. For a complete function reference, refer to the MySQL Reference Manual.

We now have the code that will allow a user to type a joke and add it to our database. All that remains is to slot it into our existing joke viewing page in a useful fashion. Since most users will only want to view our jokes, we don't want to mar our page with a big, ugly form unless the user expresses an interest in adding a new joke. For this reason, our application is well suited for implementation as a multi-purpose page. Here's the code (available as jokes.php in the code archive):

<html>
<head>
<title> The Internet Joke Database </title>
</head>
<body>
<?php
  if (isset($addjoke)): // If the user wants to add a joke
?>
<form action="<?=$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" /></p>
</form>
<?php
  else: // Default page display
    // 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 ($submitjoke == "SUBMIT") {
      $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>");
      }
    }

     echo("<p> Here are all the jokes in our database: </p>");

     // Request the text of all the jokes
    $result = @mysql_query("SELECT JokeText FROM Jokes");
    if (!$result) {
      echo("<p>Error performing query: " . mysql_error() . "</p>");
      exit();
    }

     // Display the text of each joke in a paragraph
    while ( $row = mysql_fetch_array($result) ) {
      echo("<p>" . $row["JokeText"] . "</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>

There we go! With a single file that contains a little PHP code we're able to view existing jokes, and add jokes to, our MySQL database.

A Challenge

As homework, see if you can figure out how to put a link labeled "Delete this Joke" next to each joke on the page that, when clicked, will remove that joke from the database and display the updated joke list. Here are a few hints to get you started:

  • You'll still be able to do it all in a single multi-purpose page.
  • You'll need to use the SQL DELETE command, which we learned about in Chapter 2.
  • This is the tough one. To delete a particular joke, you'll need to be able to uniquely identify it. The ID column in the Jokes table was designed 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 query string of the "Delete this Joke" link is a perfect place to put this value.

If you think you have the answer, or if you'd just like to see the solution, advance to the next page. Good luck!








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers