Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Selecting Data
Now that we have some data in the database, we can get it back out again. We'll start simple by just listing the events that are planned for a particular date, as shown in Listing 4.
Listing 4—Selecting Data from the Database (showday.php)
<html>
<head><title>Events for the day</title></head>
<body>
<?php
// Connect to database.
$connection = mysql_connect ( "localhost", "myusername", "mypassword" ) ||
die ( "Error connecting to database!" );
$month=12;
$day=14;
$year=2002;
$select_statement = "SELECT eventid, eventTitle from events ".
"where eventMonth=$month and eventDay=$day and eventYear=$year";
echo("The results for the SQL statement: <br />");
echo($select_statement);
echo("<br />are:");
$results = mysql_db_query ( "mysql", $select_statement );
$event = mysql_fetch_array ( $results );
echo("<p>eventid = <b>".$event["eventid"]."</b></p>");
echo("<p>eventTitle = <b>".$event["eventTitle"]."</b></p>");
// Free resources.
mysql_free_result ( $results );
?>
</body>
</html>
As the example (saved as showday.php) shows, displaying database information on a PHP page requires the following steps:
Create the select statement. In this case, the date has been set arbitrarily.
Execute the statement and store the results in a variable. In this case, the variable is $results.
Extract a record from the result set and store it as an array. In this case, the array is $events.
Access the data as part of the array, with the column name as the index. In this case, we retrieved the eventid and eventTitle columns.
Destroy the result set to free resources, using the mysql_free_result() function.
Here we grabbed only a single record, as shown in Figure 4, but $results actually holds all of the records returned by the select statement (if any). We can retrieve each of the records in turn using the mysql_fetch_array() function, as shown in Listing 5.
)
Figure 4 Fetching a single record.
Listing 5—Retrieving Multiple Records (showday.php)
...
$results = mysql_db_query ( "mysql", $select_statement );
while ($event = mysql_fetch_array ( $results )) {
echo("<p>eventid = <b>".$event["eventid"]."</b><br />");
echo("eventTitle = <b>".$event["eventTitle"]."</b></p>");
}
// Free resources.
mysql_free_result ( $results );
...
Each time mysql_fetch_array() executes, it moves to the next record in the result set, providing new values. When there are no more records, the assignment can't be made successfully, so the while loop exits. The results are shown in Figure 5.
)
Figure 5 Fetching multiple records.