Laravel - 参数编号无效：未定义参数

Laravel - Invalid parameter number: parameter was not defined

提问by I'll-Be-Back

On Laravel, why do I get an error

在 Laravel 上，为什么我会收到错误消息

Invalid parameter number: parameter was not defined

无效的参数号：未定义参数

I have included all parameters.

我已经包含了所有参数。

When I tested directly on PHPMyAdmin, it work fine.

当我直接在 PHPMyAdmin 上测试时，它工作正常。

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

回答by Andy Noelker

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

您的查询失败，因为您在查询中重复使用参数。Laravel 使用 PDO 进行 SQL 查询。根据文档：

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

回答by Steve Bauman

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

您将所有 SQL 插入到 laravel 的查询构建器select()方法中。你只需要利用它的其他方法：

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

如果您不想使用查询构建器，请尝试执行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
    ]
);