Home arrow MySQL arrow Page 3 - Working with PHP and MySQL

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:
  1. Working with PHP and MySQL
  2. Fetching and Displaying
  3. Putting It All Together
  4. Using PEAR
By: O'Reilly Media
Rating: starstarstarstarstar / 15
May 24, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: