How to Make Dynamic Search Query in Php MySQL

In this blog you will learn 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,

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

if (isset("$_POST['submit']"))
{
    if ($search_by_id)
    {
        $query = " and id=' $search_by_id' ";
    }
    if ($search_by_name)
    {
        $query. = " and name like '%$search_by_name%' ";
    }
    if ($search_by_designation)
    {
        $query. = " and designation like '%$search_by_designation%' ";
    }
    if ($search_by_salary)
    {
        $query. = " and salary like '%$search_by_salary%'";
    }
    if ($search_by_role)
    {
        $query. = " and role like '%$search_by_role%' ";
    }
    if ($search_by_department)
    {
        $query. = " and id=' %$search_by_department%' ";
    }
}

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:

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,

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

Feel Free to leave a comment for any doubt or questions.