In this lesson, we're going to dive right in and create some simple yet useful pages using PHP and MySQL. Let's start by displaying the database we created yesterday, but with a little more panache.
First, let's query our database using the following code.
<html>
<body>
<?php
$db = mysql_connect("localhost", "root");
mysql_select_db("mydb",$db);
$result = mysql_query("SELECT * FROM employees",$db);
echo "<table border=1>\n";
echo "<tr><td>Name</td><td>Position</tr>\n";
while ($myrow = mysql_fetch_row($result)) {
printf("<tr><td>%s %s</td><td>%s</td></tr>\n",
$myrow[1], $myrow[2], $myrow[3]);
}
echo "</table>\n";
?>
</body>
</html>
You probably noticed that we introduced a couple of new features here. Most obvious is the while() loop. The loop says that as long as there are new rows of data to be grabbed (using the mysql_fetch_row() function), assign that row to the $myrow variable. Then execute the instructions between the curly brackets ({}). Take a look for a second, and this should make sense.
The mysql_fetch_row() function bears a closer look. One small problem with mysql_fetch_row() is that it returns an array that supports only numeric references to the individual fields. So the first field is referred to as 0, the second as 1, and so on. With complex queries this can become something of a nightmare.
Now let's examine the loop in more detail. The first few lines you'll recognize from the example in Lesson 1. Then in the while() loop we fetch a row from the result and assign it to the array $myrow. Then we print the contents of the array on the screen with the printf function. After that it loops around again, and another row is assigned to $myrow. It will do this until it runs out of rows to grab.
The great thing about a while() loop is that if your query returns no records, you won't get an error message. The first time through there won't be any data to assign to $myrow, and the program will just move on.
But if the query returns no data, we have no way of letting the user know, and we should probably provide some sort of message. This is possible, so let's do it.
next page»
