Scribd
Upload
41 views

Chapter10 Search Record Using SELECT

The document describes how to search a database table using SELECT statements in PHP. It provides examples of creating simple search forms to search by name or preferred criteria. Users can enter search terms into text boxes or select criteria from drop down lists. The form data is processed by PHP files that build SQL queries with WHERE clauses to match the search terms. Results are output in HTML tables. The document also gives examples of searching using combined criteria from multiple form fields by building the SQL query dynamically based on the submitted criteria.

Uploaded by

Khirulnizam Abd Rahman
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
41 views

Chapter10 Search Record Using SELECT

The document describes how to search a database table using SELECT statements in PHP. It provides examples of creating simple search forms to search by name or preferred criteria. Users can enter search terms into text boxes or select criteria from drop down lists. The form data is processed by PHP files that build SQL queries with WHERE clauses to match the search terms. Results are output in HTML tables. The document also gives examples of searching using combined criteria from multiple form fields by building the SQL query dynamically based on the submitted criteria.

Uploaded by

Khirulnizam Abd Rahman
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 11

Chapter 10: Search Record using SELECT

search record using SELECT

Simple search

Create a HTML form to receive the user’s request. In this example, we will be doing search
based on the first name, from the table employee of the database mycompanyhr.

Create the file below, and save it as searchform.php.


<html>
<head>
<title>Search by Name</title>
</head>
<body>
Search for name<br>

<form action="simplesearch.php" method="get" name="formsearch">


Enter a name <input name="txtsearch" type="text">
<input name="btnsearch" type="submit" value="Search">
</form>

</body>
</html>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:1
Chapter 10: Search Record using SELECT

Create another file to receive the name entered by the user. The filename if you refer to the
action attribute in the form is simplesearch.php.

<html>
<head>
<title>Search by Name</title>
</head>

<body>
<?php
$searchName=$_GET['txtsearch'];
include ("connection.php");

//Create SQL query, add WHERE clause to narrow listing


$query="select EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, PHONENO
from employee
where FIRSTNAME like '%$searchName%'";

//Execute the query


$qr=mysqli_query($db,$query);
if($qr==false){
echo ("Query cannot be executed!<br>");
echo ("SQL Error : ".mysqli_error($db));
}

//Check the record effected, if no record,


//display a message
if(mysqli_num_rows($qr)==0){
echo ("No record by that name: $searchName...<br>");
}//end no record
else{//there is/are record(s)
?>
<b>Searching for <?=$searchName?></b><br>
<table width="90%" border="1">
<tr align="center">
<td>Employee no.</td>
<td>First name</td>
<td>Last name</td>
<td>Department code</td>
<td>Phone no.</td>
</tr>

<?php
while ($rekod=mysqli_fetch_array($qr)){//redo to other records
?>
<tr>
<td><?=$rekod['EMPNO']?></td>
<td><?=$rekod['FIRSTNAME']?></td>
<td><?=$rekod['LASTNAME']?></td>
<td><?=$rekod['WORKDEPT']?></td>
<td><?=$rekod['PHONENO']?></td>
</tr>
<?php
}//end of records
?>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:2
Chapter 10: Search Record using SELECT

</table>
<?php
}//end if there are records
?>
</body>
</html>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:3
Chapter 10: Search Record using SELECT

Combine search form and the search result

It’s much more user friendly if we use a single page for the user’s request and the record
listing.

<html>
<head>
<title>Listing a Result Set in a Table</title>
</head>

<body>
Search for name<br>

<form action="simplesearch.php" method="get" name="formsearch">


Enter a name <input name="txtsearch" type="text"> 1
<input name="btnsearch" type="submit" value="Search">
</form>

<?php 2
$searchName=$_GET['txtsearch'];
//this will execute if a name is inserted for search
if( $searchName!= NULL){ 3
//Include the connection details
include ("connection.php");

//Create SQL query, add WHERE clause to narrow listing


$query="select EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, PHONENO
from employee
where FIRSTNAME like '%$searchName%'";
4
//Execute the query
$qr=mysqli_query($db,$query);
if($qr==false){
echo ("Query cannot be executed!<br>");
echo ("SQL Error : ".mysqli_error($db));
}

//Check the record effected, if no records,


//display a message
if(mysqli_num_rows($qr)==0){
echo ("No record by that name: $searchName...<br>");
}//end no record
else{//there is/are record(s)
?>
<b>Searching for <?=$searchName?></b><br>
5
<table width="90%" border="1">
<tr align="center">
<td>Employee no.</td>
<td>First name</td>
<td>Last name</td>
<td>Department code</td>
<td>Phone no.</td>
</tr>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:4
Chapter 10: Search Record using SELECT

<?php
while ($rekod=mysqli_fetch_array($qr)){//redo to other records
?>
<tr>
<td><?=$rekod['EMPNO']?></td>
<td><?=$rekod['FIRSTNAME']?></td>
<td><?=$rekod['LASTNAME']?></td>
<td><?=$rekod['WORKDEPT']?></td>
<td><?=$rekod['PHONENO']?></td>
</tr>
<?php
}//end of records
?>
</table>
<?php
}//end if there are records

}//end if a name is inserted


?>
</body>
</html>

The legend for the number in circle in the previous script.


1. The form for the user to enter the name to search.
2. To extract the value entered in the textbox of the form in 1.
3. If the value in 2 is NOT NULL (user keyed in some text), the search process will
execute. If the value is NULL, then nothing will happen.
4. Add the WHERE clause to select only the records that fulfill the condition.
5. Just a simple output to display the name searched by the user.
And the rest of the script is very much similar to the listing procedure in Chapter 9.

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:5
Chapter 10: Search Record using SELECT

Search by using preferred criteria

To provide a much easier searching facility for the user, we can prepare a multiple option to
choose. Instead of search by name only, user can choose search by their preferred field.

Create the form to receive user’s query. Prepare a text box and a combo box/dropdown list.
The drop down list will contain all criteria to choose.

This is the code for the form, save as formsearchcriteria.php.


<html>
<head>
<title>Search by Preferred Criteria </title>
</head>

<body>
Search for name<br>

<form action="selectcriteriasearch.php" method="get"


name="formsearch">
Enter a name <input name="txtsearch" type="text">
<?php
//Include the connection details
include ("connection.php");
//list all the fields in the table employee
$qfields="SHOW COLUMNS FROM employee"; 1
/*the sql query above will return only the fieldnames,
without any record*/
?>
<select name="cmbfield">
<?php
//create a combo box for list of fields
$rsfield=mysqli_query($db,$qfields);
while($field=mysqli_fetch_array($rsfield)){
$fieldname=$field['Field']; 2
echo "<option value='$fieldname'>$fieldname</option>\n";
}
?>
</select>

<input name="btnsearch" type="submit" value="Search">


</form>

</body>
</html>

The legend for the number in circle in the previous script.


1. This SQL clause is used to list all the fields from the employee table.
2. Create the combo-box / drop-down-list, form the field list in 1, for the user to select
their preferred criteria.

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:6
Chapter 10: Search Record using SELECT

The file to process the user query is named selectcriteriasearch.php


<html>
<head>
<title>List Searched Records by Preferred Criteria </title>
</head>

<body>
<?php
//fetch the search item
$searchitem=$_REQUEST['txtsearch'];
//fetch the search criteria
$searchcriteria=$_REQUEST['cmbfield'];

//Include the connection details


include ("connection.php");
//Create SQL query, add WHERE clause to narrow listing
$query="select EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, PHONENO
from employee
where $searchcriteria like '%$searchitem%'";

//Execute the query


$qr=mysqli_query($db,$query);
if($qr==false){
echo ("Query cannot be executed!<br>");
echo ("SQL Error : ".mysqli_error($db));
}

//Check the record effected, if no records,


//display a message
if(mysqli_num_rows($qr)==0){
echo ("No record by that query: $searchitem...<br>");
}//end no record
else{//there is/are record(s)

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:7
Chapter 10: Search Record using SELECT

?>
<b>Searching for <?=$searchitem?> using
<?=$searchcriteria?></b><br>
<table width="90%" border="1">
<tr align="center">
<td>Employee no.</td>
<td>First name</td>
<td>Last name</td>
<td>Department code</td>
<td>Phone no.</td>
</tr>

<?php
while ($rekod=mysqli_fetch_array($qr)){//redo to other records
?>
<tr>
<td><?=$rekod['EMPNO']?></td>
<td><?=$rekod['FIRSTNAME']?></td>
<td><?=$rekod['LASTNAME']?></td>
<td><?=$rekod['WORKDEPT']?></td>
<td><?=$rekod['PHONENO']?></td>
</tr>
<?php
}//end of records
?>
</table>
<?php
}//end if there are records
?>
</body>
</html>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:8
Chapter 10: Search Record using SELECT

Search by using combination of criteria

Search the employee table with the combination of first name, hired year and gender. Create
the first file to receive user’s request, filename formsearchcombinecriteria.php.
<html>
<head>
<title>Search by Combined Criteria </title>
</head>

<body>
Search for employee<br>

<form action="searchcombinecriteria.php" method="get"


name="formsearch">
Enter the first name <input name="txtfirstname" type="text"> AND
<br>
Choose the year hired
<select name="cmbyearhired"> This
<?php generates
for ($i=1945;$i<=date('Y');$i++){ the year list
echo "<option value='$i'> $i</option>"; from 1946 to
} the current
?> year.
</select> AND <br>
Choose the gender
<input name="rsex" type="radio" value="M"> Male
<input name="rsex" type="radio" value="F"> Female
<br>
<input name="btnsearch" type="submit" value="Search">
</form>

</body>
</html>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:9
Chapter 10: Search Record using SELECT

This is the page to receive the search criteria and list the match records. The filename is
searchcombinecriteria.php.

<html>
<head>
<title>List Searched Records by Combined Criteria </title>
</head>

<body>
<?php
$firstname=$_REQUEST['txtfirstname'];
$hiredyear=$_REQUEST['cmbyearhired'];
$sex=$_REQUEST['rsex'];

//Include the connection details


include ("connection.php");
//Create SQL query, add WHERE clause to narrow listing
$query="select EMPNO, FIRSTNAME, LASTNAME, WORKDEPT, PHONENO,
SEX, HIREDATE
from employee
where FIRSTNAME like '%$firstname%' AND
year(HIREDATE) = '$hiredyear' AND
SEX = '$sex'";
//Execute the query
$qr=mysqli_query($db,$query);
if($qr==false){
echo ("Query cannot execute!<br>");
echo ("SQL Error : ".mysqli_error($db));
}

//Check the record effected, if no records,


//display a message
if(mysqli_num_rows($qr)==0){
echo ("No record by that query...<br>");
}//end no record
else{//there is/are record(s)
?>
<b>Searching for combined criteria using
<br>first name: <?=$firstname?>,
hired year: <?=$hiredyear?> AND
gender : <?=$sex?> .
</b><br>
<table width="90%" border="1">
<tr align="center">
<td>Employee no.</td>
<td>First name</td>
<td>Last name</td>
<td>Department code</td>
<td>Phone no.</td>
</tr>

<?php
while ($rekod=mysqli_fetch_array($qr)){//redo to other records
?>
<tr>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:10
Chapter 10: Search Record using SELECT

<td><?=$rekod['EMPNO']?></td>
<td><?=$rekod['FIRSTNAME']?></td>
<td><?=$rekod['LASTNAME']?></td>
<td><?=$rekod['WORKDEPT']?></td>
<td><?=$rekod['PHONENO']?></td>
</tr>
<?php
}//end of records
?>
</table>
<?php
}//end if there are records
?>
</body>
</html>

PHP Manual (All rights reserved (2008) Khirulnizam Abd Rahman – kerul.blogspot.com) Chapter 10:11

You might also like