Multiple WHERE conditions in SQL query

Question

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.

Is this a question on how to use query using php or using sql only? What code do you have already? — peixotorms, Feb 06 '16 at 19:53
Come on Richie! 488 rep, you should know the rules by now! Try something, post it, people can correct it. — Matt, Feb 06 '16 at 19:53
lol. I have some ugly functions I have written with foreach loop, which I thought might not really help. I do know the rules. — Richie, Feb 06 '16 at 19:58

score 1 · Answer 1 · edited May 23 '17 at 12:07

1

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

edited May 23 '17 at 12:07

Community

1
1

answered Feb 06 '16 at 19:53

daxro

185
13

score 0 · Answer 2 · answered Feb 06 '16 at 20:32

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 :)

score 0 · Answer 3 · answered Feb 06 '16 at 21:02

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.

Multiple WHERE conditions in SQL query

3 Answers3