CODEWITHSUNDEEP
phpmysqlsqldatabaselaravel
asad
asad

Reputation: 149

How to create filtering using SQL statement PHP Laravel?

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

Upvotes: 2

Views: 69

Answers (2)

pr1nc3
pr1nc3

Reputation: 8338

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.

Upvotes: 0

Qonvex620
Qonvex620

Reputation: 3972

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();

Upvotes: 1

Related Questions