Build Your Own Database Driven Website Using PHP & MySQL: Pt. 4

Thursday Jan 24th 2002 by Kevin Yank
Share:

The final installment of our series covers publishing MySQL data on the Web, connecting to MySQL with PHP, sending SQL queries with PHP, inserting data into the database, and an included 'homework' assignment.

This is it -- the stuff you signed up for! In this chapter, you'll learn how to take information stored in a database and display it on a Web page for all to see. So far you have installed and learned the basics of MySQL, a relational database engine, and PHP, a server-side scripting language. Now you'll see how to use these two new tools together to create a true database-driven Web site!

A Look Back at First Principles

Before we leap forward, it's worth a brief look back to remind ourselves of the goal we're working toward. We have two powerful, new tools at our disposal: the PHP scripting language, and the MySQL database engine. It's important to understand how these two will fit together.

The whole idea of a database-driven Web site is to allow the content of the site to reside in a database, and for that content to be dynamically pulled from the database to create Web pages for people to view with a regular Web browser. So on one end of the system you have a visitor to your site who uses a Web browser to load http://www.yoursite.com/, and expects to view a standard HTML Web page. On the other end you have the content of your site, which sits in one or more tables in a MySQL database that only understands how to respond to SQL queries (commands).

The interaction of MySQL, PHP, and the Web server

As shown in the diagram above, the PHP scripting language is the go-between that speaks both languages. It processes the page request and fetches the data from the MySQL database, then spits it out dynamically as the nicely-formatted HTML page that the browser expects. With PHP, you can write the presentation aspects of your site (the fancy graphics and page layouts) as "templates" in regular HTML. Where the content belongs in those templates, you use some PHP code to connect to the MySQL database and -- using SQL queries just like those you used to create a table of jokes in Chapter 2 -- retrieve and display some content in its place.

Just so it's clear and fresh in your mind, this is what will happen when someone visits a page on our database-driven Web site:

  • The visitor's Web browser requests the Web page using a standard URL.
  • The Web server software (Apache, IIS, or whatever) recognizes that the requested file is a PHP script, and so the server interprets the file using its PHP plug-in, before responding to the page request.
  • Certain PHP commands (which we have yet to learn) connect to the MySQL database and request the content that belongs in the Web page.
  • The MySQL database responds by sending the requested content to the PHP script.
  • The PHP script stores the content into one or more PHP variables, and then uses the now-familiar echo function to output the content as part of the Web page.
  • The PHP plug-in finishes up by handing a copy of the HTML it has created to the Web server.
  • The Web server sends the HTML to the Web browser as it would a plain HTML file, except that instead of coming directly from an HTML file, the page is the output provided by the PHP plug-in.

Before you can get content out of your MySQL database for inclusion in a Web page, you must first know how to establish a connection to MySQL from inside a PHP script. Back in Chapter 2, you used a program called mysql that allowed you to make such a connection. PHP has no need of any special program, however; support for connecting to MySQL is built right into the language. The following PHP function call establishes the connection:

mysql_connect(address, username, password);

Here, address is the IP address or hostname of the computer on which the MySQL server software is running ("localhost" if it's running on the same computer as the Web server software), and username and password are the same MySQL user name and password you used to connect to the MySQL server in Chapter 2.

You may or may not remember that functions in PHP usually return (output) a value when they are called. Don't worry if this doesn't ring any bells for you -- it's a detail that I glossed over when I first discussed functions. In addition to doing something useful when they are called, most functions output a value, and that value may be stored in a variable for later use. The mysql_connect function shown above, for example, returns a number that identifies the connection that has been established. Since we intend to make use of the connection, we should hold onto this value. Here's an example of how we might connect to our MySQL server.

$dbcnx = mysql_connect("localhost", "root", "mypasswd");

As described above, the values of the three function parameters may differ for your MySQL server. What's important to see here is that the value returned by mysql_connect (which we'll call a connection identifier) is stored in a variable named $dbcnx.

Since the MySQL server is a completely separate piece of software, we must consider the possibility that the server is unavailable, or inaccessible due to a network outage, or because the username/password combination you provided is not accepted by the server. In such cases, the mysql_connect function doesn't return a connection identifier (since no connection is established). Instead, it returns false. This allows us to react to such failures using an if statement:

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

There are three new tricks in the above code fragment. First, we have placed an @ symbol in front of the mysql_connect function. Many functions, including mysql_connect, automatically display ugly error messages when they fail. Placing an @ symbol in front of the function name tells the function to fail silently, allowing us to display our own, friendlier error message.

Next, we put an exclamation point in front of the $dbcnx variable in the condition of the if statement. The exclamation point is the PHP negation operator, which basically flips a false value to true, or a true value to false. Thus, if the connection fails and mysql_connect returns false, !$dbcnx will evaluate to true, and cause the statements in the body of our if statement to be executed. Alternatively, if a connection was made, the connection identifier stored in $dbcnx will evaluate to true (any number other than zero is considered "true" in PHP), so !$dbcnx will evaluate to false, and the statements in the if statement will not be executed.

The last new trick is the exit function, which is the first example that we've encountered of a function that takes no parameters. All this function does is cause PHP to stop reading the page at this point. This is a good response to a failed database connection, because in most cases the page will be unable to display any useful information without that connection.

As in Chapter 2, the next step, once a connection is established, is to select the database you want to work with. Let's say we want to work with the joke database we created in Chapgter 2. The database we created was called jokes. Selecting that database in PHP is just a matter of another function call:

mysql_select_db("jokes", $dbcnx);

Notice we use the $dbcnx variable that contains the database connection identifier to tell the function which database connection to use. This parameter is actually optional. When it's omitted, the function will automatically use the link identifier for the last connection opened. This function returns true when it's successful and false if an error occurs. Once again, it's prudent to use an if statement to handle errors:

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

Notice that this time, instead of assigning the result of the function to a variable and then checking if the variable is true or false, I have simply used the function call itself as the condition. This may look a little strange, but it's a very commonly used shortcut. To check if the condition is true or false, PHP executes the function and then checks its return value -- exactly what we need to happen.

With a connection established and a database selected, we are now ready to begin using the data stored in the database.

In Chapter 2, we connected to the MySQL database server using a program called mysql that allowed us to type SQL queries (commands) and view the results of those queries immediately. In PHP, a similar mechanism exists: the mysql_query function.

mysql_query(query, connection_id);

Here query is a string that contains the SQL command we want to execute. As with mysql_select_db, the connection identifier parameter is optional.

What this function returns will depend on the type of query being sent. For most SQL commands, mysql_query returns either true or false to indicate success or failure respectively. Consider the following example, which attempts to create the Jokes table we created in Chapter 2:

$sql = "CREATE TABLE Jokes (
         ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
         JokeText TEXT,
         JokeDate DATE NOT NULL
       )";
if ( @mysql_query($sql) ) {
  echo("<p>Jokes table successfully created!</p>");
} else {
  echo("<p>Error creating Jokes table: " . mysql_error() . "</p>");
}

Again, we use the @ trick to suppress any error messages produced by mysql_query, and instead print out a friendlier error message of our own. The mysql_error function used here returns a string of text that describes the last error message that was sent by the MySQL server.

For DELETE, INSERT, and UPDATE queries (which serve to modify stored data), MySQL also keeps track of the number of table rows (entries) that were affected by the query. Consider the SQL command below, which we used in Chapter 2 to set the dates of all jokes that contained the word "chicken":

$sql = "UPDATE Jokes SET JokeDate='1990-04-01'
        WHERE JokeText LIKE '%chicken%'";

When we execute this query, we can use the mysql_affected_rows function to view the number of rows that were affected by this update:

if ( @mysql_query($sql) ) {
  echo("<p>Update affected " . mysql_affected_rows() . " rows.</p>");
} else {
  echo("<p>Error performing update: " . mysql_error() . "</p>");
}

SELECT queries are treated a little differently, since they can retrieve a lot of data, and PHP must provide ways to handle that information.

For most SQL queries, the mysql_query function returns either true (success) or false (failure). For SELECT queries this just isn't enough. You'll recall that SELECT queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. As a result, when it processes a SELECT query, mysql_query returns a number that identifies a "result set", which contains a list of all the rows (entries) returned from the query. False is still returned if the query fails for any reason.

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

Provided no error was encountered in processing the query, the above code will place a result set that contains the text of all the jokes stored in the Jokes table into the variable $result. As there's no practical limit on the number of jokes in the database, that result set can be pretty big.

We mentioned before that the while loop is a useful control structure for dealing with large amounts of data. Here's an outline of the code to process the rows in a result set one at a time:

while ( $row = mysql_fetch_array($result) ) {
  // process the row...
}

The condition for the while loop probably doesn't much resemble the conditions you're used to, so let me explain how it works. Consider the condition as a statement all by itself:

$row = mysql_fetch_array($result);

The mysql_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array. If you're not familiar with the concept of arrays, don't worry: we'll discuss it in a moment. When there are no more rows in the result set, mysql_fetch_array instead returns false.

Now, the above statement assigns a value to the $row variable, but at the same time the whole statement itself takes on that same value. This is what lets us use the statement as a condition in our while loop. Since while loops keep looping until their condition evaluates to false, the loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes. All that's left is to figure out how to get the values out of the $row variable each time the loop runs.

Rows of a result set are represented as arrays. An array is a special kind of variable that contains multiple values. If you think of a variable as a box that contains a value, then an array can be thought of as a box with compartments, where each compartment is able to store an individual value. In the case of our database row, the compartments are named after the table columns in our result set. If $row is a row in our result set, then $row["JokeText"] is the value in the JokeText column of that row. So here's what our while loop should look like if we want to print the text of all the jokes in our database:

while ( $row = mysql_fetch_array($result) ) {
  echo("<p>" . $row["JokeText"] . "</p>");
}

To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the text of all the jokes in the database, and display them in HTML paragraphs. The code of this example is available as jokelist.php in the code archive.

<html>
<head>
<title> Our List of Jokes </title>
<head>
<body>
<?php
  // 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();
  }
?>
<p> Here are all the jokes in our database: </p>
<blockquote>
<?php

   // 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>");
  }
?>
</blockquote>
</body>
</html>

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!

Here's the solution to the "homework" challenge posed above. These changes were required to insert 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 a similar fashion, we pass a $deletejoke variable with our "Delete this Joke" link to indicate our desire to have a joke deleted.
  • For each joke, we fetch the ID column from the database, along with the JokeText column, so that we know which ID is associated with each joke in the database.
  • We set the value of the $deletejoke variable to the ID of the joke that we're deleting. To do this, we insert 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 a particular value (through the isset function) when the page loads. If it is, we use the value to which it is set (the ID of the joke to be deleted) in an SQL DELETE statement that deletes the joke in question.

The complete code for challege.php is available to download free when you purchase the book.

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

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

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 Jokes database online, and allowed visitors to add jokes of their own to it.

The next 9 chapters and the complete code archive for all examples demonstrated are available in my book Build your own Database Driven Website Using PHP & MySQL.

The book explores several applications of your database in more depth. In Chapter 5, we'll 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.

Later chapters go on to build a complete content management system based on the humble beginnings we've just discussed. You'll learn how to build an ecommerce shopping cart, build a Web-based file repository, utilize sessions and cookies to track visitors, maintain database security and plenty more! And as I mentioned above, to make the job of applying the applications to your Website extra-easy, I've also included a complete, downloadable code archive, which contains all the sample PHP & MySQL code that you'll use with the book.

If you're serious about taking PHP & MySQL to the next level, I'd encourage you to grab yourself a copy of Build your own Database Driven Website Using PHP & MySQL.

It's reasonably priced at only $34.95 (plus $6.95 shipping to anywhere in the World) and if you're not 100% satisfied with your purchase, simply return it to SitePoint, who will be more than happy to refund the purchase price of the book.

Okay I've seen enough, show me the book NOW please...

The book contains not only the four chapters you just read, but also eight more chapters that cover advanced database concepts, the design of a complete content management system, MySQL server administration, and much, much more! The book also includes a complete set of appendices to make it the ideal desk reference for PHP and MySQL Web development. For more information, see the book page.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved