Latest

Monday, July 10, 2017

Laravel 5 Custom Query Invalid parameter number

Asked by: Devin Dixon


In Laravel 5, I am trying to do a customer query. My code is like so:

$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::statement(DB::raw($sql),$params);

Except I keep getting this error, even when I remove DB::raw, and I've tried DB::select as well.

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)

Does anyone have an idea why this happening and how to fix?


Answers

Answered by: OuailB at 2017-07-11 04:43AM



Try to use DB:select() with the $params array as a second argument, like this :

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


Answered by: sumit at 2017-07-11 04:49AM



You can do like below

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



Source

No comments:

Post a Comment

Adbox