Putting It All Together - MySQL

Last week, you began learning how to use PHP to display and modify data from a MySQL database. This week, you'll learn how to select the database, fetch and display data, and more. This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O'Reilly, 2006; ISBN: 0596101104). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:

By: O'Reilly Media

Poor Best

Rating:

/ 15

May 24, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Now you're going to take all of the steps and put them into a single PHP file that you'll call db_test.php. You should place the PHP script shown in Example 9-5 in the same directory as the db_login.php file.

Example 9-5. Displaying the books and authors

<?php
// Include our login information
include('db_login.php');
// Connect
$connection = mysql_connect( $db_host, $db_username, $db_password );
if (!$connection)
{
 die ("Could not connect to the database:
 ". mysql_error());
}
// Select the database $db_select=mysql_select_db($db_database); if (!$db_select)
{
 die ("Could not select the database:
 ". mysql_error());
}
// Assign the query
$query = "SELECT * FROM `books` NATURAL JOIN `authors`";
// Execute the query
$result = mysql_query( $query );
if (!$result)
{
 die ("Could not query the database:
 ". mysql_error());
}

// Fetch and display the results
while ($result_row = mysql_fetch_row(($result)))
{

echo 'Title: '.$result_row[1] . '
 ';
 echo 'Author: '.$result_row[4] . '
 ';
 echo 'Pages: '.$result_row[2] . '
 ';
}
/ /Close the connection
mysql_close($connection);
?>

Here's the output from Example 9-5:

Title: Linux in a Nutshell Author: Ellen Siever Pages: 476 
 Title: Linux in a Nutshell 
Author: Aaron Weber Pages: 476 
 Title: Classic Shell Scripting Author: Arnold Robbins Pages: 256 
 Title: Classic ShellScripting 
Author: Nelson H.F. Beebe Pages:
256

This displays in your browser as in Figure 9-3.

If you don't see the screen in Figure 9-3, then you'll see an error from whichever step in the process had a problem, giving you an idea of what went wrong and where it was wrong.

To make the display more appealing, you can put the information into a table, as shown in Example 9-6. You also add complete HTML headers.

Figure 9-3. How Example 9-5 displays in the browser

Example 9-6. Displaying the results of a query in an HTML table

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html401/loose.dtd"> <html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Displaying in an HTML table</title> </head>
<body>
<table border="1">
<tr>
<th>Title</th>
<th>Author</th>
<th>Pages</th>
</tr>
<?php
//Include our login information
include('db_login.php');
// Connect
$connection = mysql_connect($db_host, $db_username, $db_password);
if (!$connection){
die("Could not connect to the database:
 ". mysql_error());
}
// Select the database
$db_select = mysql_select_db($db_database); if (!$db_select){
die ("Could not select the database:
 ". mysql_error());
}
// Assign the query
$query = "SELECT * FROM `books` NATURAL JOIN `authors`";
// Execute the query
$result = mysql_query($query);
if (!$result){
die ("Could not query the database:
 ". mysql_error());
}
// Fetch and display the results
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$title = $row["title"];
$author = $row["author"];
$pages = $row["pages"];
echo "<tr>";
echo "<td>$title</td>";
echo "<td>$author</td>";
echo "<td>$pages</td>";
echo "</tr>";
}
// Close the connection
mysql_close($connection);
?>
</table>
</body>
</html>

Example 9-6. displays in your browser as shown in Figure 9-4.

Figure 9-4. The same data but in an HTML table

Notice that you made use of the MYSQL_ASSOC fetch type in Example 9-6. You're probably saying to yourself, "That's great, but how do I display the book titles with the authors all on one line?" This is where we talk about PEAR.

Next: Using PEAR >>