Displaying a Data Table | Spring Into PHP 5

We're ready to see some SQL and PHP action in a browser. Here, we're going to extract the fruit table from the produce database and display its data in an HTML table. We'll start by connecting, selecting the database, and getting an object named $result that corresponds to the fruit table:

 $connection = mysql_connect("localhost","root","")     or die ("Couldn't connect to server"); $db = mysql_select_db("produce", $connection)     or die ("Couldn't select database"); $query = "SELECT * FROM fruit"; $result = mysql_query($query)      or die("Query failed: " . mysql_error());

Now you can use the mysql_fetch_array function to get successive rows from the table in a while loop and get the name and number fields from each row by name like this:

 while ($row = mysql_fetch_array($result)) {     echo "<TR>";     echo "<TD>", $row['name'], "</TD><TD>", $row['number'], "</TD>";     echo "</TR>"; }

Not bad. All we need is the code to wrap the results in an HTML table, as you see in phpdatatable.php, Example 8-1. Note that we also close the connection at the end with mysql_close, which you should do when you're finished with a database.

Example 8-1. Displaying a database table, phpdatatable.php

 <HTML>     <HEAD>         <TITLE>             Displaying tables with MySQL         </TITLE>     </HEAD>     <BODY>         <CENTER>             <H1>Displaying tables with MySQL</H1>             <?php                  $connection = mysql_connect("localhost","root","")                      or die ("Couldn't connect to server");                  $db = mysql_select_db("produce", $connection)                      or die ("Couldn't select database");                  $query = "SELECT * FROM fruit";                  $result = mysql_query($query)                       or die("Query failed: ".mysql_error());                  echo "<TABLE BORDER='1'>";                  echo "<TR>";                  echo "<TH>Name</TH><TH>Number</TH>";                  echo "</TR>";                  while ($row = mysql_fetch_array($result))                  {                      echo "<TR>";                      echo "<TD>", $row['name'], "</TD><TD>", $row['number'],                          "</TD>";                      echo "</TR>";                  }                  echo "</TABLE>";                  mysql_close($connection);              ?>          </CENTER>     </BODY> </HTML>

The results appear in Figure 8-1, where you can see the whole fruit table. Very cool.

Figure 8-1. Displaying a database table.

That's itwe've been able to create a MySQL database and extract data from it in a web page.