Laravel - Invalid parameter number: parameter was not defined

Question

On Laravel, why do I get an error

Invalid parameter number: parameter was not defined

I have included all parameters.

When I tested directly on PHPMyAdmin, it work fine.

Code:

$results = \DB::select('SELECT client_id,
                               date_format(start_date,"%d/%m/%Y") as start_date,
                               date_format(end_date,"%d/%m/%Y") as end_date,
                               first_name, last_name, phone, postcode
                            FROM hire INNER JOIN client ON client.id = hire.client_id
                            where ((:sdate between start_date and end_date OR :edate between start_date and end_date) OR (:sdate <= start_date and end_date <= :edate)) AND car_id = :car_id', [
        'sdate'  => $start_date,
        'edate'  => $end_date,
        'car_id' => $car_id
    ]
);

Variable example:

$start_date = $inputs['start_date'];  //2015-10-27
$end_date = $inputs['end_date'];     //2015-10-27
$car_id = $inputs['car_id'];         //5

Czan you show how you set values to your 3 variables ? – Mateo Barahona Nov 04 '15 at 16:02 — Mateo Barahona, Nov 04 '15 at 16:02
@MateoBarahona Updated my Question. – I'll-Be-Back Nov 04 '15 at 16:04 — I'll-Be-Back, Nov 04 '15 at 16:04

score 44 · Answer 1 · answered Nov 04 '15 at 16:17

Your query is failing because you are reusing parameters in your query. Laravel uses PDO for its SQL queries. According to the docs:

You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

So even though they have the same value, you will have to rename those parameters.

$results = \DB::select('SELECT client_id,
                           date_format(start_date,"%d/%m/%Y") as start_date,
                           date_format(end_date,"%d/%m/%Y") as end_date,
                           first_name, last_name, phone, postcode
                        FROM hire INNER JOIN client ON client.id = hire.client_id
                        where ((:sdate between start_date and end_date OR :edate between start_date and end_date) OR (:sdate2 <= start_date and end_date <= :edate2)) AND car_id = :car_id', [
    'sdate'  => $start_date,
    'sdate2'  => $start_date,
    'edate'  => $end_date,
    'edate2'  => $end_date,
    'car_id' => $car_id
]
);

https://stackoverflow.com/a/48346164/470749 agrees with this. And naming separate params worked for me. Thanks. — Ryan, Sep 03 '18 at 23:20

Steve Bauman · Answer 2 · 2015-11-04T16:20:02.107

You're inserting all of your SQL inside of laravel's query builder select() method. You just need to utilize its other methods:

$select = [
    'client_id',
    'start_date',
    'end_date',
    'first_name',
    'last_name',
    'phone',
    'postcode'
];

$results = \DB::table('hire')
        ->join('client', 'client.id', '=', 'hire.client_id')
        ->select($select)
        ->where('car_id', $car_id)
        ->whereBetween('start_date', [$start_date, $end_date])
        ->orWhereBetween('end_date', [$start_date, $end_date])
        ->get();

These aren't all of your parameters, but it should get you started.

If you're not looking to use the query builder, try performing a raw($expression):

$results = \DB::raw('SELECT client_id,
                           date_format(start_date,"%d/%m/%Y") as start_date,
                           date_format(end_date,"%d/%m/%Y") as end_date,
                           first_name, last_name, phone, postcode
                        FROM hire INNER JOIN client ON client.id = hire.client_id
                        where ((:sdate between start_date and end_date OR :edate between start_date and end_date) OR (:sdate <= start_date and end_date <= :edate)) AND car_id = :car_id', [
        'sdate'  => $start_date,
        'edate'  => $end_date,
        'car_id' => $car_id
    ]
);

You can try using `selectRaw($select)` (or `raw($expression)`) to run your statement, but using `select()` only adds columns to the current query builder. It won't process any of the SQL you're looking to run. Not to mention you're also not asking for any results using the `get()` method. https://github.com/laravel/framework/blob/5.1/src/Illuminate/Database/Query/Builder.php#L230 — Steve Bauman, Nov 04 '15 at 16:14
While using the query builder or the Eloquent ORM is indeed preferred, adding raw SQL to the `select` method of the `DB` facade is completely valid. http://laravel.com/docs/5.1/database#running-queries — Andy Noelker, Nov 04 '15 at 16:19

score 1 · Answer 3 · answered Sep 03 '20 at 08:03

1

app/config/database.php

add this in mysql array

'options'=> extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true,
            ]) : [],

answered Sep 03 '20 at 08:03

Abdullah al Noman

29
4

Care to elaborate what this does exactly? – Lasse Meyer Sep 03 '20 at 14:37
This does not address or solve the issue, at least not in all instances, as I am still running into this issue after upgrading to a new server with PHP8 – JustinM151 Oct 03 '21 at 20:30

score 1 · Answer 4 · answered Jul 11 '22 at 11:23

As Abdullah al Noman stated.

You can add the following statment to your database config file.

app/config/database.php

PDO::ATTR_EMULATE_PREPARES => true,

So it would look like this.

'options'=> extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true,
            ]) : [],

In my case, I had multiple custom connections, so you need to make sure to find all PDO references and add them there.

After you do that, then in command.

php artisan optimize:clear

Laravel - Invalid parameter number: parameter was not defined

4 Answers4

Linked