Laravel 5自定义查询参数编号无效

Question

在Laravel 5中，我正在尝试进行客户查询。我的代码是这样的：

$params =数组( 'criteria‘=> $criteria，'criteria1’=> $criteria )；

    //Define the SQL
    $sql = 'SELECT * FROM ' . $this -> _taskTableName .' 
    JOIN ' . $this -> _userTableName .' ON 
    ' . $this -> _userTableName .'.id = ' . $this -> _taskTableName .' .client_id 
    WHERE notes LIKE \'%:criteria%\' OR name LIKE \'%:criteria1%\' ';


    //Exeute the search
    $tasks = DB::statement(DB::raw($sql),$params);

除非我一直收到这个错误，即使我删除了DB::raw，并且我也尝试了DB::select。

SQLSTATE[HY093]: Invalid parameter number: :criteria (SQL: SELECT * FROM tasks 
JOIN users ON 
users.id = tasks .client_id 
WHERE notes LIKE '%:criteria%' OR name LIKE '%:criteria1 %' )
in Connection.php (line 647)
at Connection->runQueryCallback(object(Expression), array('criteria' => 'Devin', 'criteria1' => 'Devin'), object(Closure))
in Connection.php (line 607)
at Connection->run(object(Expression), array('criteria' => 'Devin', 'criteria1' => 'Devin'), object(Closure))
in Connection.php (line 450)

有谁知道为什么会发生这种情况，以及如何修复？

Answer 1

尝试使用带有$params数组的DB:select()作为第二个参数，如下所示：

$params = array( 'criteria' => $criteria, 'criteria1' => $criteria );

//Define the SQL
$sql = 'SELECT * FROM ' . $this -> _taskTableName .' 
JOIN ' . $this -> _userTableName .' ON 
' . $this -> _userTableName .'.id = ' . $this -> _taskTableName .' .client_id 
WHERE notes LIKE \'%:criteria%\' OR name LIKE \'%:criteria1%\' ';


//Exeute the search
$tasks = DB::select($sql, $params);

Answer 2

你可以像下面这样做

$sql = 'SELECT * FROM ' . $this -> _taskTableName .' 
        JOIN ' . $this -> _userTableName .' ON 
        ' . $this -> _userTableName .'.id = ' . $this -> _taskTableName .' .client_id 
        WHERE notes LIKE \'%?%\' OR name LIKE \'%?%\' ';
    DB::select($sql,array($criteria,$criteria));