php - Laravel - 无效参数号 : parameter was not defined [Named Parameters Error]

Question

我已经在查询中包含了所有命名参数，还处理了 PDO 文档中提到的命名参数规则(即不支持命名参数的可重用性)。它仍然给我错误，我找不到错误。

$leaves = DB::select("SELECT * FROM leaves WHERE emp_id=:emp_id AND status != 'Rejected' AND ((STR_TO_DATE(from_date, '%Y-%m-%d') BETWEEN ':from_date1' AND ':to_date1') OR (STR_TO_DATE(to_date, '%Y-%m-%d') BETWEEN ':from_date2' AND ':to_date2')) OR ((STR_TO_DATE(from_date, '%Y-%m-%d') <= ':from_date3') AND (STR_TO_DATE(to_date, '%Y-%m-%d') >= ':to_date3'))",
      [
        'emp_id'=> (int)$request->emp_id, 'from_date1'=>$request->from_date, 'to_date1'=>$request->to_date,
        'from_date2'=>$request->from_date, 'to_date2'=>$request->to_date,
        'from_date3'=>$request->from_date, 'to_date3'=>$request->to_date
      ]);

Answer 1

您似乎错误地使用了命名参数。检查PDO::prepare文档，您会注意到 ' 没有包围参数名称。请尝试以下查询:

$leaves = DB::select(
   "SELECT * 
    FROM leaves 
    WHERE emp_id=:emp_id AND status != 'Rejected' 
        AND (
           (STR_TO_DATE(from_date, '%Y-%m-%d') 
                BETWEEN :from_date1 AND :to_date1) 
           OR (STR_TO_DATE(to_date, '%Y-%m-%d') 
                BETWEEN :from_date2 AND :to_date2)
        ) 
        OR (
           (STR_TO_DATE(from_date, '%Y-%m-%d') <= :from_date3) 
           AND (STR_TO_DATE(to_date, '%Y-%m-%d') >= :to_date3)
        )",
      [
        'emp_id'=> (int)$request->emp_id, 'from_date1'=>$request->from_date, 
        'to_date1'=>$request->to_date, 'from_date2'=>$request->from_date, 
        'to_date2'=>$request->to_date, 'from_date3'=>$request->from_date, 
        'to_date3'=>$request->to_date
      ]);

基本上，在参数名称周围包含 ' 可以让驱动程序相信您在查询中使用的是实际的 varchar 值。然而，在您的情况下，您正在尝试调用替换。