How to do Multiple Condition in SQL query using PHP and Ajax

[vishalonne]

Hello

 

I have databse in MySQL with 21 fields which are listed below

Field Name Data Type NULL

status text No

roll_no text No

branch_id int(5) No

student_name text No

father_name text No

phone1 text No

phone2 text No

email text No

dob date No

city text No

course_id varchar(5) No

class_id int(2) No

program text No

duration text No

comment text No

admission_year int(4) No

admission_date text No

entryby text No

address text No

admission_no int(4) No

fees int(6) No

 

Now I am stuck with search process, I bit confused, how can I perform search for different types of conditions/criteria

 

Few Examples of combinations of condtions

1. Only those records of city=3

2. Only those branch_id=2

3. Only those admission_year='2013'

4. Only those course_id='15'

5. Only those branch_id='2' AND  course_id='15'

6. Only those branch_id='2' AND  course_id='15' AND city LIKE 'XYZ'

7. Only those admission_year='2012' AND  course_id='10' AND duration BETWEEN(2 AND 3)

8. Only those branch_id=2 AND  course_id='15' AND student_name LIKE 'XYZ' 

8. Only those course_id=7 AND  class_id=2 AND father_name LIKE 'XYZ' 

 

My search.php form page is designed, I attached the image of form design here but I am confused how can I implement this search options for different situations.  Or can view the page here

 

Please give me some guidance and show me the correct way to solve this issue.

[Psycho]

As long as you are going to treat all of the conditions as AND or OR conditions (which it correct based on your examples), it's simple.

 

All of the fields are going to be passed in the POST data, so you need to just ignore the ones that are blank. Then create conditions for the ones which are not blank and then concatenate them with AND conditions. This assumes that the "default" options for the select lists have an empty value.

 

Here is some sample code using just four fields as an example

 

//Preprocess the post data
$branch = isset($_POST['branch']) ? trim($_POST['branch']) : '';
$year = isset($_POST['year']) ? trim($_POST['year']) : '';
$course = isset($_POST['course']) ? trim($_POST['bcourse']) : '';
$class = isset($_POST['class']) ? trim($_POST['class']) : '';

//Determine WHERE conditions
$where = array();
if(!empty($branch))
{
    $where[] = "branch = '$branch'";
}
if(!empty($year))
{
    $where[] = "year = '$year'";
}
if(!empty($course))
{
    $where[] = "course = '$course'";
}
if(!empty($class))
{
    $where[] = "class = '$class'";
}

//Create query - concatenating all the WHERE clauses
$query = "SELECT *
          FROM table_name
          WHERE " . implode(' AND ', $where);

[vishalonne]

 

As long as you are going to treat all of the conditions as AND or OR conditions (which it correct based on your examples), it's simple.

 

All of the fields are going to be passed in the POST data, so you need to just ignore the ones that are blank. Then create conditions for the ones which are not blank and then concatenate them with AND conditions. This assumes that the "default" options for the select lists have an empty value.

 

Here is some sample code using just four fields as an example

 

//Preprocess the post data
$branch = isset($_POST['branch']) ? trim($_POST['branch']) : '';
$year = isset($_POST['year']) ? trim($_POST['year']) : '';
$course = isset($_POST['course']) ? trim($_POST['bcourse']) : '';
$class = isset($_POST['class']) ? trim($_POST['class']) : '';

//Determine WHERE conditions
$where = array();
if(!empty($branch))
{
    $where[] = "branch = '$branch'";
}
if(!empty($year))
{
    $where[] = "year = '$year'";
}
if(!empty($course))
{
    $where[] = "course = '$course'";
}
if(!empty($class))
{
    $where[] = "class = '$class'";
}

//Create query - concatenating all the WHERE clauses
$query = "SELECT *
          FROM table_name
          WHERE " . implode(' AND ', $where);

Can I make a function of this and call that function where I want search operation.

[Psycho]

Can I make a function of this and call that function where I want search operation.

 

I don't know, can you?

[jcbones]

Looking at your column names, you need to normalize your database.  Otherwise, you could run into collisions with that set up.  As it sits now, you will have to update a users address on each course they are taking.  This leads to the conclusion of bad database design.  You need to make some relational tables.