Dynamic Database search query generation on user selection of field data

Searching a database is not same as searching using keywords. Database search are applied to an existing table or linked tables. Here the search is applied to many fields and is not restricted to keyword search. The users depending on the knowledge or requirement, will use different combination of fields. For example in a student database search the user may be knowing the class of the student so a selection can be given to the form saying search within class five students. Same way search can be restricted to search for male or female student. This way search can be restricted or expanded by using different combinations available. We will learn how to develop query dynamically based on the selection ( combination ) of the user. We will use PHP to develop the dynamic query and restrict our self to query generation only as common data display process can be applied once query is available.

Search for Pin code and other details of an Indian location by entering keywords and selecting matching suggestion using JQuery autocomplete with database as source.
Autocomplete Search

Select Class	Select Gender
	Male Female Any
Name	Type of Name Match
	Match any where Exact Match
Mark of the student
Less thanGreater than

We will start with our student table, it has four fields. We will develop a drop down list for all class to be listed. A radio button selection for male female or Any selection. We will give a mark selection where user can enter minimum or maximum mark. Note that here all options we provided are optional for the user. User can try in any combination or may try with out any combination also.

Here is a demo of the search box how it will appear. On submit you can see how the query is generated based on the inputs. Try with different combination of selection.

Here this form will generate different query based on the selection of the user. You can see the query changes based on the user selection. We will be adding field name and its value to search only when we find the data is available. So before adding the field to the search query we will check the length of the data entered. If the length is more than zero then we assume that the user has selected that option for searching.

For example if class selection is Four then we can check the length of variable $class like this

////////// Including class field search ////

if(strlen($class) > 0 ){

$query.= " class='$class' and ";

}

//// End of class field search ///////////

You can see class field search is applied only if class is selected in the drop down list. Same way we have included other fields to search with checking the length of the variable.

Searching gender field

While searching if we know the student is male or female then we can use that. If we select Male as gender the variable \$sex will have the value of radio button used inside the form. Here is the code to include gender field in search query

////////// Checking of sex in the query ////
if(strlen($sex) > 0 ){
$query.= " sex='$sex' and "; 
}
//// End of sex  field search ///////////

Here is the code for form and search query generation. You can download this code with sql dump files at the end of this page.

<?Php
echo "<form method=post action=''><input type=hidden name=todo value=search>";
$q="select distinct class from student";
echo "<select name=class><option value=''>Any Class</option>";
foreach ($dbo->query($q) as $n) {
echo "<option value=$n[class]>$n[class]</option>";
}
echo "</select>";
echo "<input type=radio name=sex value=male>Male <input type=radio name=sex value=female>Female <input type=radio name=sex value='' checked> Any<br>";
echo "<br>Name Match<br><input type=text name=search_text ><br>
<input type=radio name=type value=any checked>Match any where <input type=radio name=type value=exact>Exact Match
<br><br>
Mark of the student <br> Less than<input type=text name=less size=2>Greater than <input type=text name=greater size=2>
<br><input type=submit value=Search>
</form>
";


///////////////////////
$todo=$_POST['todo'];
if(isset($todo) and $todo=="search"){
$less=$_POST['less'];
$greater=$_POST['greater'];
$class=$_POST['class'];
$sex=$_POST['sex'];
$search_text=$_POST['search_text'];
$type=$_POST['type'];

$query="select * from student where  ";


////////// Including mark less than field search ////
if(strlen($less) > 0 ){
$query.= " mark < $less and "; 
}
//// End of class field search ///////////

////////// Including mark less than field search ////
if(strlen($greater) > 0 ){
$query.= " mark > $greater and "; 
}
//// End of class field search ///////////

////////// Including class field search ////
if(strlen($class) > 0 ){
$query.= " class='$class' and "; 
}
//// End of class field search ///////////

////////// Checking of sex in the query ////
if(strlen($sex) > 0 ){
$query.= " sex='$sex' and "; 
}
//// End of sex  field search ///////////


////////////////////////// Key word search query /////////
$search_text=ltrim($search_text);
$search_text=rtrim($search_text);

if(strlen($search_text)>0){ 
	if($type<>"any"){
$query .=" name='$search_text'";
		}else{
$kt=preg_split("/[s,]+/",$search_text);//Breaking the string to array of words

// Now let us generate the sql 
			while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$query .= " name like '%$val%' or ";}

			}// end of while
$query=substr($query,0,(strLen($query)-3));
// this will remove the last or from the string. 
		} // end of if else based on type value
$query.=" and ";
}// end of if area , if search_text value is entered
///////////////End of adding key word search query //////////

$query=substr($query,0,(strLen($query)-4));

echo "<span style="background-color: #F0F000">$query</span>";
echo "<br><br>";
// Display records ////
foreach ($dbo->query($query) as $t) {
echo "$t[id] , $t[name],$t[class],$t[mark],$t[sex]<br>";
}
}
?>

Ajax based Keyword search on MySQL table

keyword search

Download sql dump of this student table

STRING Functions Case Condition Checking

Subscribe to our YouTube Channel here

swapnil`	19-09-2014
Good example provide from you this tutorial realy help for me thanks........

skechav	12-09-2015
Thank you for this!!!It helped me a lot using it as a base to develop a more complicated search form in combination with other useful tutorials I 've studied in your site..Though It happened 2 years ago, I ended up posting this comment now..with a...."small" delay ;-) p.s : Your content rocks by the way !!!

Jonathan	01-12-2015
How do I display records before running the query:?

smo1234	03-12-2015
You can't display records before. However if you want to display all the records first and then ask the user to filter it then you can simply try select query to display records.

Dynamic SQL generation for searching database on different fields

Searching gender field

Subscribe