In addition to using the ODBC functions with the SQL INSERT statement to add records to a database, it is also possible to retrieve records from a database table using the SQL SELECT statement. A typical input form for selecting existing records from a Directory table is shown below. In this example a ficticious company, Company XYZ, has an online form that allows users to enter an employee's last name and search for the employee's full name, telephone number, and e-mail address.
The SQL SELECT Statement is shown below:
For more a more in depth look at the SELECT Statement see section 10.
The following code is used to process the DirectorySearch.php form:
<?php
if ($_POST[submit] == "Search")
{
//Collect Form Data
$string = $_POST['search'];
//Establish data connection
$conn = odbc_connect('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb','','');
//Issue SQL SELECT Statement
$sql = "SELECT * FROM Directory WHERE LName = '$string'";
$rs = odbc_exec($conn, $sql);
}
?>
<!DOCTYPE html PUBLIC "-//W3C/DTD/XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml11-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>A Web Page</title>
<style>
body {margin:15px;font:10pt Verdana}
td {vertical-align:top;border:solid 1px gray}
input,textarea{border:0px}
</style>
</head>
<body>
<form action="DirectorySearch.php" method="post">
<p>Enter a last name below and click the "Search" button to
locate employee telephone number and e-mail address</p>
<table>
<tr>
<td colspan="2">Company XYZ Directory</td>
</tr>
<td><input type="text" size="15" name="SearchName"/></td>
<td><input type="submit" value="Search" name="submit"/>
</tr>
</table>
</form>
<div>
<?php
if(!empty($_POST))
{
while($row = odbc_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " ";
echo $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
}
odbc_close($conn);
}
?>
</div>
</body>
</html>
This page contains two PHP code blocks. The first is executed when the "Search" submit button is clicked. The last name entered by the user is assigned to the scalar variable '$string'. Next, a connection to the database is established and an SQL SELECT statement is issued to select all (*) fields for the table records whose 'lastname' field is equal to the last name string entered by the user. Finally, the SQL statement is executed. If matching records are found the recordset is assigned to the '$rs' variable.
The second code block appears in the XHTML body section of the document. Code containing echo or print statements is normally placed between the opening and closing <body> tags so that it can be displayed or formatted in relation to other page elements. Echo and print statements appearing in PHP blocks coded above the <html> always appears at the top of the page and preceds all other page elements.
The purpose of this code block is to display the records retrieved when the SQL statement was executed in the previous code block. First, an if statement is used to determine if the $POST array variable is empty. If the array is empty this means the form has not been submitted and no records have been retrieved. If this conditional statement is omitted, errors will occur because the odbc_fetch_array will not contain any values.
Next a while loop is used to iterate through the recordset. During each iteration through the recordset, The odbc_fetch_array() function creates an associate array (here the array is called $row) containing the field values for the current record. The array indices correspond to the table field names and the array element corresponds to the value of the field. Each record is then displayed $row['FName'] - the value of the 'FirstName' field, $row['LName'] - the value of the 'LastName' field, $row['Telephone'] - the value of the 'Telephone' field, and $row['Email'] - the value of the 'Email' field. This process continues, each time the $row array containing new values, until the end of the recordset is reached.
After all records are displayed, the database connection is closed using the odbc_close() function.
Below is an example of output generated after a search:
If the user searches for a last name that does not exist in the database table, no records are displayed. To prevent confusion, the script above can be slightly modified so that a message is displayed if no matching records are found.
<?php
if(!empty($_POST))
{
$results = 0;
while($row = odbc_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " ";
echo $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
$results += 1;
}
if ($results == 0)
{
echo "No records found!";
}
odbc_close($conn);
}
?>
</div>
</body>
</html>
The modified script shown above adds a record counter, $results, to count the number of records displayed. Intially, the value of the counter is set to 0. If a recordset exists the while loop will iterate through each record and update the counter value by 1. After the loop ends, the value of the counter is checked. If the value is 0, no records were found and the message "No records found!" is displayed.