Selecting Individual Records

[back]

As well as showing the entire table, PHP can select individual records, or records which match certain criteria.

Method 1: Using PHP code

<?php
$username="username";
$password="password";
$database="your_database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM students WHERE last='smith'";
$result=mysql_query($query);

$num=mysql_numrows($result);
mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
echo "<p>"
?>

<table width=600 border=1>
<tr>
<th>first name</th>
<th>last name</th>
<th>grad year</th>
<th>student number</th>
</tr>

<?php
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$grad_year=mysql_result($result,$i,"grad_year");
$student_no=mysql_result($result,$i,"student_no");
?>

<?
++$i;
}
echo "</table>";

Explanation:

Notice that we are using the same PHP code used in the last exercise with one change.To display the whole table we used the query:
SELECT * FROM students

If we just wanted to select records that have the last name 'smith' you would use the following query:
SELECT * FROM students WHERE last='smith'

Using this method, we could search any of our four fields:
WHERE last='smith'
WHERE first=' '
WHERE grad_year=' '
WHERE student_no=' '

Create a PHP document that will let you select certain records. Save as display3.php

Method 2: Using an HTML search form

You can also pass a variable from an HTML form. Set up an HTML page called search.htm - you will also need to modify the display3.php page.

<html>
<head><title>search</title></head>
<body>

</body>
</html>

With that variable, you could execute the following piece of code:

$query="SELECT * FROM students WHERE last='$last_name'";

BEYOND THE BASICS

Adding a "No Record Message":

Add this to your opening code to give the user something to look at instead of just an empty table if there are no matches.

if ($num==0) {
echo "<h2><center>There are no results for that search.</center></h2>";
} else {
echo "<b><center><h2>Database Output</h2></center></b><br><br>";
}

MODIFYING THE SEARCH USING 'LIKE'

SELECT * FROM tablename WHERE fieldname LIKE '%$first_name%'

LIKE tells the database to expand its 'searching' feature. The % signs mean that any other data could appear in their place. They may be placed at the start, end or both, as in the above example.

example: if Dan was the variable sent, Daniel, Danielle, Danny would be returned.

ORDERING DATA

Not only can you output data based on the contents of a field, but you can also order the output. By default, the output from your queries will be in order of the id field, going from 1 upwards. You can sort it on any field, though.

For example, a useful sort would be to place all of the students in alphabetical order based on their last name. For those not familiar with standard databases, this would be in Ascending order as it goes from A to Z. (Ascending order is also for 1-10 etc. and descending order provides Z to A and 10-1). To do this you would use the following query:

SELECT * FROM studnts ORDER BY last ASC;

You could also use DESC to order the data in Descending order.