Article Publication Date: November 27, 2001
Article URL: http://www.zend.com/zend/tut/tutorial-yank.php
Author URL: n/a

Back to Article

Build your own Database Driven Website using PHP & MySQL

November 27, 2001

By Kevin Yank

Intended Audience
Overview
Learning Objectives
Definitions
Background Information
Prerequisites
A Look Back at First Principles
Connecting to MySQL with PHP
Sending SQL Queries with PHP
Handling SELECT Result Sets
Inserting Data Into the Database
A Challenge
Summary
”Homework” Solution
About The Author

Intended Audience


This tutorial is aimed at intermediate or advanced Web designers looking to make the leap into server-side programming. You'll be expected to be comfortable with simple HTML, as the tutorial makes use of it without much in the way of explanation. A teensy bit of JavaScript may serve us well at some point as well, but this is kept simple for the uninitiated.

Overview
A hands-on guide to learning all the tools, principles and techniques needed to build a fully functional database-driven Website using PHP & MySQL. This series covers everything from installing PHP & MySQL under Windows or Linux, through to building a live Web-based content management system. The book version covers more advanced topics such as the storage of binary data in MySQL, and cookies and sessions in PHP, and comes complete with a set of PHP and MySQL reference tables that include PHP and MySQL syntax, functions, column types and more. This book not only provides easy access to all the code samples demonstrated, but, more importantly, it leaves you with the confidence and know-how to adapt the principles and techniques to your own Web design projects.

Learning Objectives


By the end of this tutorial, you can expect to have a grasp of what's involved in setting up and building a database-driven Web site. If you follow the examples, you’ll also learn the basics of PHP and Structured Query Language as supported by MySQL, one of the most popular free database engines available today. Most importantly, you'll come away with everything you need to get started on your very own database-driven site in no time!

Definitions


PHP – a server-side scripting language that allows you to do a lot more than access a database easily
Structured Query Language (SQL) –the standard language for interacting with relational databases
MySQL – a free relational database management system (RDBMS) available on most platforms under the GPL

Background Information


More often than not, the people who provide the content for a site are not the same people who handle its design. Oftentimes, the content provider doesn't even know HTML. How, then, is the content to get from the provider onto the Web site? Not every company can afford to staff a full-time Webmaster, and most Webmasters have better things to do than copying Word files into HTML templates anyway.

Maintenance of a content-driven site can be a real pain, too. Many sites feel locked into a dry, outdated design because rewriting those hundreds of HTML files to reflect a new design would take forever. Server-side includes (SSI’s) can help alleviate the burden a little, but you still end up with hundreds of files that need to be maintained should you wish to make a fundamental change to your site.

The solution to these headaches is database-driven site design. By achieving complete separation between your site's design and the content you want to present, you can work with each without disturbing the other. Instead of writing an HTML file for every page of your site, you only need to write a page for each kind of information you want to be able to present. Instead of endlessly pasting new content into your tired page layouts, create a simple content management system that allows the writers to post new content themselves without a lick of HTML!

Prerequisites


Basic knowledge of HTML is all that is required for this tutorial.

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



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:


Connecting to MySQL with PHP

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

Sending SQL Queries with PHP

 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.

Handling SELECT Result Sets

 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 http://www.sitepoint.com/books 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>

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 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 http://www.sitepoint.com/books/ 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:

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


Summary

 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.

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 $9.95 shipping to anywhere in the World) and if you're not 100% satisfied with your purchase, SitePoint are more than happy to refund the purchase price of the book.


"Homework" Solution

 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:


Here's the complete code, which is also available as challege.php in the http://www.sitepoint.com/books/ code archive. If you have any questions, don't hesitate to post them in the http://www.sitepointforums.com 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>

About The Author


Kevin Yank is the Technical Content Director for SitePoint.com, author of many well received tutorials and articles, and editor of the SitePoint Tech Times, an extremely popular technically-oriented email newsletter for Web Developers.

Before graduating from McGill University in Montreal with a Bachelor of Computer Engineering, Kevin was not only a budding Web Developer himself, but also an active advisor for the Sausage Software Web Development Forums and writer of several practical guides on advanced HTML and JavaScript.

These days, when he’s not discovering new technologies, writing books, or catching up on sleep, Kevin can be found helping other up-and-coming Web Developers in the SitePoint Forums.