Post
Ask Question

How to Make Dynamic Search Query in PHP MySql

As we know Search in Sql can be implemente using 'Like' clause using '%' and '_' operators. This is the simple way to implement search query. For Example





    

  1. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;   

This query will be ok when we already know what data we need accordingly. But suppose in situation where we don't know how many fields will be searched by the user. For example for the below table we have 6 input fields for the keyword to search by employee id, employee name, employee designation, employee salary, employee role, employee department and we dont know what will be the fields that user will search for, he may be search by id, by name or id and name both or maybe all fields. For this situation we will write the query below:

Employee id Employee Name Employee Designation Employee Salary Employee Role Employee Department
1 emp_1 backend developer 15,000 development it
2 emp_2 junior php developer 8,000 development it
3 emp_3 web designer 20,000 designing it
4 emp_4 asp developer 18,000 development it
5 emp_5 java deveolper 30,000 development it
6 emp_6 senior php developer 45,000 development it
7 emp_7 wordpress developer 9,000 development it
8 emp_8 project manager 50,000 management it

  1.  if(isset("$_POST['submit']")){   
  2.  if($search_by_id){   
  3.  $query= " and id=' $search_by_id' ";   
  4.  }   
  5.  if($search_by_name){   
  6.  $query.= " and name like '%$search_by_name%' ";   
  7.  }   
  8.  if($search_by_designation){    
  9. $query.= " and designation like '%$search_by_designation%' ";   
  10.  }  
  11.   if($search_by_salary){   
  12.  $query.= " and salary like '%$search_by_salary%'";   
  13.  }   
  14.  if($search_by_role){   
  15.  $query.= " and role like '%$search_by_role%' ";  
  16.   }  
  17.   if($search_by_department){   
  18.  $query.= " and id=' %$search_by_department%' ";  
  19.   }  
  20.  }   

Write your query like this

      mysql_query("Select * from tablename where 1=1 $query;");   

Here, "." operator in "$query." is used to concatinate $query itself. Suppose User enters the 5 in "search by id" and php developer in "search by role" fields and click submit . Then query will be:

  1. Select * from tablename where 1=1 and id='5' and role like '%php developer%';  

Advantage of this format of query is that firstly it is fully dynamic for example suppose in future we have to add one or more fields you can simply add,

  1. if($field_name){ $query.= " and column name like '%$field_name%'  "; }   

Feel Free to leave a comment for any doubt or questions :-