How to create filtering using SQL statement PHP Laravel?

Question

I have one table named as Package. Currently, I want to filter the table actually. Let say the user insert values into variable, for example, from = 2/3/2020 and to = 10/3/2020. Then it will be calculated inside my coding and get the duration of days, resulting noOfdays = 8 days. So, from the duration, it will determined, which packages it belongs within the duration of 8 days.

calculation of days :

        $today = Carbon::now();

        $dt1 = Carbon::createFromFormat('d/m/Y',$departure);
        $dt2 = Carbon::createFromFormat('d/m/Y',$arrival);

        $noOfDays = $dt1->diffInDays($dt2);

The calculation have no error, which when dd($noOfDays), it will result = 8 days.

SQL statement :

            $packages = Package::where([
                ['id', '=', $plan],
                ['from', '<=', $noOfDays, 'AND', 'to', '>=', $noOfDays],
            ])
            ->get();

Package Table :

The error part is, when I filtering, it will get Package 1 and package 2. It supposed to get only the package 2. I think it have something wrong somewhere around the SQL statement. Anyone whoever in this situation before? because logically I think, the SQL part is true already. But it why it filter and get the package 1?

Qonvex620 · Accepted Answer · 2020-02-21 10:00:52Z

1

You have to separate your two where clause to get what you wanted like this.

 $packages = Package::where([
     ['id', '=', $plan], 
     ['from', '<=', $noOfDays], 
     ['to', '>=', $noOfDays]
            ])
 ->get();

To be more readable, its good if your code goes like this

 $packages = Package::where('id', $plan)
            ->where('from', '<=', $noOfDays)
            ->where('to', '>=', $noOfDays)
            ->get();

answered Feb 21, 2020 at 10:00

Qonvex620

3,9801 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pr1nc3 · Accepted Answer · 2020-02-21 10:01:59Z

0

Change your second where statement. Where clause is always using an ADN unlelss you use orWhere or nest it in a function.

['id', '=', $plan], 
['from', '<=', $noOfDays], 
['to', '>=', $noOfDays]

The 4th parameter, that you are passing as an AND is not used like you are expecting it to work.

Which results to package1 to be included in your output.

edited Feb 21, 2020 at 10:01

answered Feb 21, 2020 at 9:55

pr1nc3

8,3763 gold badges27 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to create filtering using SQL statement PHP Laravel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related