Working with PHP and MySQL - Putting It All Together (
Page 3 of 4 )
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:
<br />". mysql_error());
}
// Select the database $db_select=mysql_select_db($db_database); if (!$db_select)
{
die ("Could not select the database:
<br />". 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:
<br />". mysql_error());
}
// Fetch and display the results
while ($result_row = mysql_fetch_row(($result)))
{
echo 'Title: '.$result_row[1] . '
<br />';
echo 'Author: '.$result_row[4] . '
<br />';
echo 'Pages: '.$result_row[2] . '
<br />';
}
/ /Close the connection
mysql_close($connection);
?>
Here's the output from Example 9-5:
Title: Linux in a Nutshell<br />Author: Ellen Siever<br /> Pages: 476<br />
<br />Title: Linux in a Nutshell<br />
Author: Aaron Weber<br /> Pages: 476<br />
<br />Title: Classic Shell Scripting<br> Author: Arnold Robbins<br /> Pages: 256<br />
<br />Title: Classic ShellScripting<br />
Author: Nelson H.F. Beebe<br /> Pages:
256<br /><br />
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:
<br />". mysql_error());
}
// Select the database
$db_select = mysql_select_db($db_database); if (!$db_select){
die ("Could not select the database:
<br />". 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:
<br />". 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.
/ 14 
