SQL PHP HTML ASP JavaScript articles and free scripts to download
 

Dynamic SQL generation for searching database on different fields

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 requirment, 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.

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.

Male Female Any

Name Match

Match any where Exact Match

Mark of the student
Less thanGreater than


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.

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.
<?

$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=split(" ",$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 $query;
echo "<br><br>";
$nt=mysql_query($query);
echo mysql_error();

// End if form submitted
}else{ echo "<form method=post action=''><input type=hidden name=todo value=search>"; $q=mysql_query("select distinct class from student"); echo "<select name=class><option value=''>Any Class</option>"; while($n=mysql_fetch_array($q)){ 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=rex 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> "; } ?>
Ajax based Keyword search on MySQL table
Download the zip file for this tutorial
keyword search
Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked


Join Our Email List
Email:  
For Email Newsletters you can trust



Searching Databse
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer