Create dynamic sites with PHP & MySQL - Searching our data
(Page 15 of 16 )
Information is useless if you can't find the data you require from a wealth of information. We need a way to search our database, so let's implement a search function. The page will show a static form initially and will show the search result when we have something submitted.
<HTML>
<?php
if ($searchstring)
{
$sql="SELECT * FROM personnel WHERE $searchtype LIKE '%$searchstring%' ORDER BY firstname ASC";
$db = mysql_connect("localhost", "root", "");
mysql_select_db("learndb",$db);
$result = mysql_query($sql,$db);
echo "<TABLE BORDER=2>";
echo"<TR><TD><B>Full Name</B><TD><B>Nick Name</B><TD><B>Options</B></TR>";
while ($myrow = mysql_fetch_array($result))
{
echo "<TR><TD>".$myrow["firstname"]." ".$myrow["lastname"]."<TD>".$myrow["nick"];
echo "<TD><a href=\"view.php?id=".$myrow["id"]."\">View</a>";
}
echo "</TABLE>";
}
else
{
?>
<form method="POST" action="<?php $PHP_SELF ?>">
<table border="2" cellspacing="2">
<tr><td>Insert you search string here</td>
<td>Search type</td></tr>
<tr>
<td><input type="text" name="searchstring" size="28"></td>
<td><select size="1" name="searchtype">
<option selected value="firstname">First Name</option>
<option value="lastname">Last Name</option>
<option value="nick">Nick Name</option>
<option value="email">Email</option>
</select></td>
</tr>
</table>
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<?php
}
?>
</HTML> |
The script checks whether a search string exists. If $searchstring contains a value, then we have something to search; otherwise, we just show an HTML form. The part of code that searches is similar to our viewdb2.php. The SQL command deserves a bit of explanation here. Let's look at it closely. The SQL command is:
"SELECT * FROM personnel WHERE $searchtype LIKE '%$searchstring%' ORDER BY firstname ASC" |
Two news things are introduced here, "LIKE" and "ORDER BY". LIKE simply means 'sounds like'. The '%' sign represents any possible combination of characters (numbers or letters). So to find people whose first name starts with 'J' we would use the SQL command:
"SELECT * FROM personnel WHERE firstname LIKE 'J%'" |
To find those people with a name ending with J we have to use '%J'. If we wish find people with 'J' anywhere in their name (first, middle, or last) we have to use '%J%'.'ORDER BY' simply orders the records in ascending or descending order. The syntax is: "ORDER BY fieldname order_ method" where order_ method is ASC or DESC allowing the ordering to be done in ASCending or DESCending order.
Next: Tips for common tasks >>
More Database Articles Articles
More By Codewalkers
|
| · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | | |
|
/ 25 
