Building a Database-Driven Web Site Using PHP and MySQL

Inserting Data into the Database

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 reading 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 we want to let visitors to our site type in new jokes, we obviously need a form. Here's the code for a form that will fit the bill:

<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>

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 ("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>");
  }
}

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 such functions, but we'll only be introducing them as required. For a complete function reference, refer to the MySQL Reference Manual.

We now have the code to 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:

<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>");
      }
    }
  
    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 containing a little PHP code we are able to view jokes in and add jokes to our MySQL database.

A Challenge

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