PHP MySQL
Interactive Website Design

Displaying the Data

In the script below we'll see how to use the mysql_query function to retrieve records or rows from our birthdays table.

We'll also be introduced to 2 new mysql functions num_rows and fetch_row.

Note: If you have accessed this web page via a search engine, you should go back and start on our home page. This tutorial is designed to be viewed and executed in sequence. Learn to build your database right on your PC and Export it to your website.

Most of the script is a repetition of syntax you've already learned. The standard procedures of connecting to the server and selecting the database will not be explained.

The first line of code we'll look at (excluding the error trap) uses the mysql_query function to SELECT all rows and fields from the table birthdays.

The asterick is used as a wild card. It is used to retrieve all fields in the table.

The data retrieved is stored in the variable $result.

$result = mysql_query( "SELECT * FROM birthdays" )

If we wanted to retrieve only the last names from our database we would change the code accordingly to:
$result = mysql_query( "SELECT lastname FROM birthdays" )

The next line of code for interpretation uses a new mysql function: mysql_num_rows().

This function simply returns the number of rows or records that have been added to the birthdays table.

Notice that it accesses the information retrieved by the previous mysql_query stored as $result.
$num_rows = mysql_num_rows($result);

The line following this syntax in the script prints the result to the screen.

Embedded in the code that prints the results in table form we find another new mysql function.

The mysql_fetch_row() function grabs an individual record or row from $result and divides it into the original fields. (id,firstname, lastname, birthday)
$get_info = mysql_fetch_row($result)

A foreach loop is used to print the fields in the cells of our table.

foreach ($get_info as $field)

This script can be used to return the rows of any table or database.

Just replace the database name and table name with the appropriate information.

birthdays_display_records.php

<html>
<head><title>Display Records</title>
</head>
<body>

<?php
/* Change next two lines  if using online*/
$db="newdb";
$link = mysql_connect('localhost', 'root', '');

if (! $link) die(mysql_error());
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM birthdays" )
          or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<br>";

print "<table width=600 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td>$field</td>\n";
print "</tr>\n";
}
print "</table>\n";
mysql_close($link);
?>
<br>

<form method="POST" action="birthdays_dbase_interface.php">
<input type="submit" value="Dbase Interface">
</form>

</body>
</html>

**Notice the error trapping used in this script. First it checks for a server connection. Next it checks to see if the database newdb exists. Then it checks to see if the table, birthdays exists.

Table Displays As:

There are 2 records.

1	Peggy	Donahue	June 4, 1956
2	Mark	Ambrose	March 27, 1971

Please Note

If you have completed the lessons in order, all of the scripts in the birthdays_db download package are active. They can be run from the birthdays_dbase_interface.php script, with the exception of the birthdays_add_fields.php script. Don't run it until you've completed all the lessons.

You should complete all of the lessons, before you begin to modify the scripts.

Building Your Database on Your PC

In order to build your database on your PC and later Export it to your website requires setting up a localhost server on your PC and finding an HTML editor that will sync up with the server.

If you use Windows, the process of setting up and running a localhost so that you can test and edit scripts and build databases is very easy.

I recommend the IndigoAMPP web server and the HTMLPad 2010 HTML editor.

I recently installed IndigoAMPP for Windows on my Vista system and it ran on the first try after installation. Setting up HTMLPad 2010 to work with it is also an easy process.

I created a little tutorial that shows the whole process including how to start the server after you install it. Go to Tutorial
Note: The server is FREE and you can try the HTML editor for 30 sessions or 30 days free. If you can't learn to build your database in that amount of time, don't buy the editor. NO RISK!!