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.