Selecting Records
In addition to using the MySQL 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:
SELECT value1,value2 FROM tablename WHERE field = value;
The following code is used to process the DirectorySearch.php form:
DirectorySearch.php
<?php
if (isset($_POST['submit']))
{
//Collect Form Data
$lastName = $_POST['SearchName'];
//Establish data connection
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Issue SQL SELECT Statement
$sql = "SELECT * FROM Directory WHERE LName = '$lastName'";
$rs = mysqli_query($conn, $sql);
}
?>
<!DOCTYPE html>
<html>
<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(isset($_POST['submit']))
{
while($row = mysqli_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " ";
echo $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
}
mysqli_close($conn);
}
?>
</div>
</body>
</html>
Unlike the INSERT example shown in the previous section, a single page is used containing both the PHP and HTML. With this structure, the <form> tags action attribute is set to DirectorySearch.php. This means the page posts to itself since it contains the HTML to collect the user input and PHP to process the input. This page also contains two PHP code blocks. The first is executed when the "Search" submit button is clicked or is set. The last name entered by the user is assigned to the scalar variable '$lastName'. 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 HTML 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 confirm that the submit button has been clicked. If this conditional statement is omitted, errors will occur because the mysqli_fetch_array will not contain any values.
Next a while loop is used to iterate through the recordset or set of records returned from the SQL query. During each iteration through the recordset, The mysqli_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 mysqli_close() function.
Below is an example of output generated after a search:
Company XYZ Directory
Name: Molly Douglas
Telephone: 5553
Email: mdouglas@php.net
Name: John Douglas
Telephone: 8883
Email: jdouglas@php.net
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.
DirectorySearch.php
<?php
if(isset($_POST['submit']))
{
while($row = mysqli_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " ";
echo $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
}
if (mysqli_num_rows($rs) == 0)
{
echo "No records found!";
}
mysqli_close($conn);
}
?>
</div>
</body>
</html>
The modified script shown above adds the mysqli_num_rows() function. This function requires one parameter - a reference to the current recordset $rs and determines the number contained by the recordset from last SQL SELECT operation. If the value return by mysql_num_rows() is 0, no rows were affected by the SQL SELECT statement. Therefore, no matching records were found.
Selecting Records Using a Query String
In many web applications, it is also common to search a database using a query string value. A query string is data or search parameters that are appended to the URL and are made up of name/value pairs. Query strings are added to the URL by the user or appended to the link within an anchor tag. The following is an example of a query string:
http://itwebtutorials.mga.edu/search.php?id=32453
The query string begins with the "?" and is followed by a name. The name value in the above query string is "id". The name is assigned a value – "32453". The value can be static, input directly by the user, or dynamic, retrieved from a database table. A query string can contain multiple name/value pairs:
http://itwebtutorials.mga.edu/search.php?id=32453&zip=31201&cid=45
Here the query string is made up of three name value pairs:
Name Value
Id 32453
Zip 31201
cid 45
In the example below, we have a page that displays a list of all employee names from the company directory. Each name is coded within an anchor "<a>" tag that contains a hyperlink to a page named "employeedetails.php" and also includes a query string value that uniquely identifies the employee. A PHP script on the employee details page will be used to retrieve the query string value and query the directory table to retrieve more detailed information about the employee.
Directory.php
//Establish data connection
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Issue SQL SELECT Statement
$sql = "SELECT id,FName,LName FROM Directory";
$rs = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($rs))
{
echo "<a href='employeedetails.php?eid=" . $row['id'] . "'>" . $row['FName'] . " " . $row['LName'] . "</a><br/>";
}
mysqli_close($conn);
Notice that a query string with the name "eid" is appended to the URL. The value of "eid" is set to the value of the "id" column, a primary key column, from the Directory table. This "eid" value is an ID value that uniquely identifies each user.When the user hovers over an employee name, a URL in the follow format is displayed in the browsers status bar:
http://itwebtutorials.net/employeedetails.php?eid=2
When the employeedetails.php page loads, we retrieve the "eid" value using the PHP $_GET[] array. This value is then used to query the directory table.
Employeedetails.php
//Establish data connection
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Retrieve Query String Value
if (isset($_GET['eid']))
{
$eid = $_GET['eid'];
//Issue SQL SELECT Statement
$sql = "SELECT FName,LName,Email,Telephone FROM directory WHERE id=$eid";
$rs = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " " . $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
}
mysqli_close($conn);
}
First, we verify that the query string value is set by using the isset() function. Next, we retrieve the value of the query string using the $_GET array - $_GET['eid'], and assign it to a PHP variable. This variable is then used to query the directory table to retrieve the employee’s name, email address, and telephone number.
While using query strings can be a convenient way of passing data, be aware of passing sensitive data since it is visible to the end user. It is also possible that the user may manipulate the URL and change the query string value. Suppose the user changes the "eid" value in the query string to an ID value that is not associated with a user in the directory table. This would result in a blank page when the employeedetails page loads. Also, it is possible for the user to directly access the employeedetails page without the query string included. This can also result in errors or the display of a blank page. Below is a slightly modified script that will handle these types of errors and display more helpful error output.
Employeedetails.php
//Establish data connection
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Retrieve Query String Value
if (isset($_GET['eid']))
{
$eid = $_GET['eid'];
//Issue SQL SELECT Statement
$sql = "SELECT FName,LName,Email,Telephone FROM directory WHERE id=$eid";
$rs = mysqli_query($conn, $sql);
if (mysqli_num_rows($rs) == 1)
{
while($row = mysqli_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " " . $row['LName'] . "<br>";
echo "Telephone: " . $row['Telephone'] . "<br>";
echo "Email: " . $row['Email'] . "<br>";
}
}
else
{
echo "Sorry, no users found. Please try again";
}
mysqli_close($conn);
}
If the user changes the "eid"" value in the query string to an ID that does not exist, we can use the mysqli_num_rows() function to determine if a record was returned. We expect 1 record to be requrned if a valid "eid" value is passed. If the "eid" value does not match an ID from the directory table, we display the error message – "Sorry, no users found. Please try again." The first "if" statement checks to see if a query string value was passed - if (isset($_GET['eid'])). If no "eid" value is passed, we can also display an error message or simply redirect the user back to the directory.php page using the PHP header() function.
Employeedetails.php
//Establish data connection
//Reference the mysqli_connection.php file created above
include('mysqli_connection.php');
//Retrieve Query String Value
if (isset($_GET['eid']))
{
$eid = $_GET['eid'];
//Issue SQL SELECT Statement
$sql = "SELECT FName,LName,Email,Telephone FROM directory WHERE id=$eid";
$rs = mysqli_query($conn, $sql);
if (mysqli_num_rows($rs) == 1)
{
while($row = mysqli_fetch_array($rs))
{
echo "Name: " .$row['FName'] . " " . $row['LName'] . "<br/>";
echo "Telephone: " . $row['Telephone'] . "<br/>";
echo "Email: " . $row['Email'] . "<br/>";
}
}
else
{
echo "Sorry, no users found. Please try again";
}
mysqli_close($conn);
}
else
{
header("Location:directory.php");
}