A Look Back at First Principles
Before we leap forward, it's worth a brief look back to remind you of our ultimate goal. We have two powerful tools at our disposal: the PHP scripting language, and the MySQL database engine. It's important to understand how these will fit together.

The whole idea of a database-driven Website is to allow the content of the site to reside in a database, and for that content to be pulled from the database dynamically 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 request a page, and expects to receive a standard HTML document. On the other end you have the content of your site, which sits in one or more tables in a MySQL database that understands only how to respond to SQL queries (commands).

Figure 4.1. PHP retrieves MySQL data to produce Web pages.


PHP retrieves MySQL data to produce Web pages.
As shown in Figure 4.1, 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. At the points at which 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, Getting Started with MySQL—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 your database-driven Website:

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, so the server interprets the file using its PHP plug-in before responding to the page request.

Certain PHP commands (which you 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, then uses the now-familiar echo statement 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.

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

mysql_connect(address, username, password)

Here, address is the IP address or host name 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, Getting Started with MySQL.

You may remember that functions in PHP usually return (output) a value when they're 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 Chapter 3, Getting Started with PHP. In addition to doing something useful when they are called, most functions output a value; 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.

As the MySQL server is a completely separate piece of software, we must consider the possibility that the server may be unavailable or inaccessible due to a network outage, or because the user name/password combination you provided is not accepted by the server. In such cases, the mysql_connect function doesn't return a connection identifier, as 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 the @ symbol (also known as the error suppression operator) in front of the function name tells the function to fail silently, and allows us to display our own, friendlier error message.

Next, we put an exclamation mark (!) in front of the $dbcnx variable in the condition of the if statement. The exclamation mark 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 can be called with no parameters. When called this way, 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, Getting Started with MySQL, once a connection is established, the next step is to select the database with which you want to work. Let's say we want to work with the joke database we created in Chapter 2, Getting Started with MySQL. The database we created was called ijdb. Selecting that database in PHP is just a matter of another function call:

mysql_select_db('ijdb', $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('ijdb')) {
 exit('<p>Unable to locate the joke ' .
     'database at this time.</p>');
}

Note 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 whether the condition is true or false, PHP executes the function and then checks its return value—exactly what we need to happen.

Another short cut I've used here is to call exit with a string parameter. When called with a parameter, exit works just like an echo statement, except that the script exits after the string is output. So, calling exit this way is equivalent to an echo statement followed by a call to exit with no parameters, which is what we used for mysql_connect above.

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

Source: http://www.sitepoint.com/artic...l-data-web

Sending SQL Queries with PHP
In Chapter 2, Getting Started with MySQL, 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 joke table we created in Chapter 2, Getting Started with MySQL:

$sql = 'CREATE TABLE joke (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     joketext TEXT,
     jokedate DATE NOT NULL
   )';
if (@mysql_query($sql)) {
 echo '<p>joke table successfully created!</p>';
} else {
 exit('<p>Error creating joke 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 , Getting Started with MySQL to set the dates of all jokes that contained the word "chicken":

$sql = "UPDATE joke SET jokedate='1994-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 {
 exit('<p>Error performing update: ' . mysql_error() .
     '</p>');
}

SELECT queries are treated a little differently, as 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. Thus, 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) {
 exit('<p>Error performing query: ' . mysql_error() .
     '</p>');
}

Provided that no error was encountered in processing the query, the above code will place a number into the variable $result. This number corresponds to a result set that contains the text of all the jokes stored in the joke table. 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 that will 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 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 number as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array (see Chapter 3, Getting Started with PHP for a discussion of arrays). 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 you use the statement as a condition in the while loop. Since a while loop will keep looping until its condition evaluates to false, this 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 to figure out is how to get the values out of the $row variable each time the loop runs.

Rows of a result set returned by mysql_fetch_array are represented as associative arrays. The indices are named after the table columns in the 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:

Example 4.1. jokelist.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Our List of Jokes</title>
<meta http-equiv="content-type"
   content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php

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

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

?>
<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 joke');
if (!$result) {
 exit('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display the text of each joke in a paragraph
while ($row = mysql_fetch_array($result)) {
 echo '<p>' . $row['joketext'] . '</p>';
}

?>
</blockquote>
</body>
</html>

Figure 4.2 shows what this page looks like once you've added a couple of jokes to the database.

Figure 4.2. All my best material—in one place!



Inserting Data into the Database
In this section, we'll see how we can use the tools at our disposal to allow site visitors 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 little new material in this section, but it's mostly just a sample application that incorporates 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:

Example 4.2. jokes.php (excerpt)

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label>Type your joke here:<br />
<textarea name="joketext" rows="10" cols="40">
</textarea></label><br />
<input type="submit" value="SUBMIT" />
</form>

Figure 4.3 shows what this form looks like in a browser.

Figure 4.3. Another nugget of comic genius is added to the database.


Another nugget of comic genius is added to the database.

As we've seen before, when submitted, this form will load the very same page (because we used the $_SERVER['PHP_SELF'] variable for the form's action attribute) with one difference: a variable will be attached to the request. The variable, joketext, will contain the text of the joke as typed into the text area, and will appear in the $_POST and $_REQUEST arrays created by PHP.

To insert the submitted joke into the database, we use mysql_query to run an INSERT query, using the value stored in $_POST['joketext'] to fill in the joketext column in the query:

Example 4.3. jokes.php (excerpt)

if (isset($_POST['joketext'])) {
   $joketext = $_POST['joketext'];
   $sql = "INSERT INTO joke 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 example is shown in bold. The MySQL function CURDATE() is used here to assign the current date as the value of the jokedate column. MySQL actually has dozens of these functions, but we'll introduce them only as required. For a complete MySQL function reference, refer to Appendix B, MySQL Functions.

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. As most users will only want to view 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 multipurpose page. Here's the full code:

Example 4.4. jokes.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>The Internet Joke Database</title>
<meta http-equiv="content-type"
   content="text/html; charset=iso-8859-1" />
</head>
<body>

<?php if (isset($_GET['addjoke'])): // User wants to add a joke
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label>Type your joke here:<br />
<textarea name="joketext" rows="10" cols="40">
</textarea></label><br />
<input type="submit" value="SUBMIT" />
</form>

<?php else: // Default page display

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

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

 // If a joke has been submitted,
 // add it to the database.
 if (isset($_POST['joketext'])) {
   $joketext = $_POST['joketext'];
   $sql = "INSERT INTO joke 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 joke');
 if (!$result) {
   exit('<p>Error performing query: ' .
       mysql_error() . '</p>');
 }

 // 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="' . $_SERVER['PHP_SELF'] .
     '?addjoke=1">Add a Joke!</a></p>';

endif;
?>
</body>
</html>

Load this up and add a new joke or two to the database via your browser. The resulting page should look like Figure 4.4.

Figure 4.4. Look, Ma! No SQL!


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

Source: http://www.sitepoint.com/artic...data-web/2

A Challenge
As "homework", see if you can figure out how to place next to each joke on the page a link labelled Delete this joke 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 multipurpose page.
You'll need to use the SQL DELETE command, which we learned about in Chapter 2, Getting Started with MySQL.
This is the tough one: to delete a particular joke, you'll need to be able to identify it uniquely. The id column in the joke 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, turn the page.
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 $_GET['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 $_GET['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.

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

Example 4.5. challenge.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>The Internet Joke Database</title>
<meta http-equiv="content-type"
   content="text/html; charset=iso-8859-1" />
</head>
<body>
<?php if (isset($_GET['addjoke'])): // User wants to add a joke
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label>Type your joke here:<br />
<textarea name="joketext" rows="10" cols="40">
</textarea></label><br />
<input type="submit" value="SUBMIT" />
</form>

<?php else: // Default page display

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

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

 // If a joke has been submitted,
 // add it to the database.
 if (isset($_POST['joketext'])) {
   $joketext = $_POST['joketext'];
   $sql = "INSERT INTO joke 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($_GET['deletejoke'])) {
   $jokeid = $_GET['deletejoke'];
   $sql = "DELETE FROM joke
       WHERE id=$jokeid";
   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 joke');
 if (!$result) {
   exit('<p>Error performing query: ' .
       mysql_error() . '</p>');
 }

 // 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="' . $_SERVER['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="' . $_SERVER['PHP_SELF'] .
     '?addjoke=1">Add a Joke!</a></p>';

endif;
?>
</body>
</html>

Source: http://www.sitepoint.com/artic...data-web/3