PHP MySQL
Create & Test MySQL Database
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 at the Beginning.
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.
Copy & Save as: 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
You should complete all of the lessons, before you begin to modify the scripts.
MySQL Tutorial
To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com