Home Ask Login Register

Developers Planet

Your answer is one click away!

Richie February 2016

Multiple WHERE conditions in SQL query

I have students details in my database. php firstname lastname DOB gender status (Active, Not Active) fee_paid

I also have a search form on the views that I want to use to search students based on different conditions.

For example, I may want to search these 1. All female students who were born between date1 to date2 and have paid x amount of fees. 2. All students called Ali who have paid x amount of fees. 3. All Active students who have not paid any fees.

My question, how can I simply check, if a field is set, then use it in a query, otherwise don't include it in the query. Also for DOB, search is by date range.

Answers


daxro February 2016

Use isset() and loops to compile your SQL-query. Check this post: Search MySQL Database with Multiple Fields in a Form


René Juul Askjær February 2016

So even though the question doesn't say, its tagged in laravel, so here goes:

firstly read up on the where clauses: https://laravel.com/docs/5.1/queries#where-clauses

next, i would do something like (might not work, psoudo code)

1.


    $females = Model::whereBetween('DOB',array('2014-08-15','2014-08-18'))->where('fee_paid',100)->get();

    //do something with $females

    2.

    $ali = Model::Where(function ($query) {
                $query->where('votes', '>', 100)
                      ->orWhere('title', '', 'Admin');
    })->where('fee_paid',100);

3.


    $freebies = Model::where('status','active)->where('fee_paid',0)->get()

If i'm completely of what you want, then formulate the question differently :)


Jimmy Canadezo February 2016

MySQL has an AND clause. You can say something like

$search = Ali;

SELECT * FROM registeredUsers WHERE username LIKE '%".$search."%' BETWEEN date1 AND date2 AND paid_fee = 4 

Go ahead and do a quick Google Search for some MySQL statement examples; you'll find some pretty cool stuff out.

Post Status

Asked in February 2016
Viewed 2,959 times
Voted 11
Answered 3 times

Search




Leave an answer


Quote of the day: live life