How to use multiple WHERE in DB::select in Laravel

Question

Here is the original code:

$invoice = DB::select('SELECT MAX(CAST(`invoice_number` as UNSIGNED)) as invoice_number FROM `invoices` where company_id = "' . company()->id . '" ');
return $invoice[0]->invoice_number;

The above works great, but I want to use it like this:

$invoice = DB::select('SELECT MAX(CAST(`invoice_number` as UNSIGNED)) as invoice_number FROM `invoices` where company_id = "' . company()->id . '" where depa_series = "2" ');
return $invoice[0]->invoice_number;

But this gives the following error:

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right syntax 
to use near ' where depa_series = "2"' at line 1 (SQL: SELECT MAX(CAST(`invoice_number` as UNSIGNED)) as invoice_number FROM `invoices` where company_id = "140", where depa_series = "2" )

How to use multiple WHERE clauses in DB::select? Thank you!

piscator · Accepted Answer · 2020-07-27 14:51:15Z

3

The query gives a syntax error, because you use where twice.

Try to change it to this, by using the AND condition:

DB::select('SELECT MAX(CAST(`invoice_number` as UNSIGNED)) as invoice_number FROM `invoices` where company_id = "' . company()->id . '" AND depa_series = "2"');

answered Jul 27, 2020 at 14:51

piscator

8,8495 gold badges26 silver badges35 bronze badges

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

Comments

linktoahref · Accepted Answer · 2020-07-27 14:54:05Z

3

Have a look at the selectRaw method

$invoice = DB::table('invoices')
            ->selectRaw('MAX(CAST(invoice_number as UNSIGNED)) as invoice_number')
            ->where('company_id', company()->id)
            ->where('depa_series', "2")
            ->get();

return $invoice[0]->invoice_number;

answered Jul 27, 2020 at 14:54

linktoahref

8,0273 gold badges32 silver badges54 bronze badges

Comments

Uhweeka · Accepted Answer · 2020-07-27 15:13:47Z

0

here is what you should do $invoice =

DB::table('invoices') ->selectRaw('MAX(CAST(invoice_number as UNSIGNED)) as invoice_number') ->where(['company_id', company()->id, 'depa_series', "2"]) ->get(); return $invoice[0]->invoice_number;

There will no need to using multiple where statements. You can just put all in an array.

answered Jul 27, 2020 at 15:13

Uhweeka

511 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to use multiple WHERE in DB::select in Laravel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related