prepared statement and binding parameters conflict

Question

Please look at the PHP code:

$val = $_POST['name']; 
$stmt = $mysqli->stmt_init(); 
$stmt->prepare("SELECT id, name, email FROM some_table WHERE name LIKE ?%"); 
$stmt->bind_param('s', $val); 
$stmt->execute();

When I run this query; I get the following error: Warning: mysqli_stmt::bind_param(): invalid object or resource mysqli_stmt

What is wrong here?

You're not checking the return values; I'd also suggest using mysqli_error to see what the actual error is. At a guess, I'd say that `?%` in the query is wrong, and that the `%` should be part of `$val` — andrewsi, Sep 29 '13 at 17:34
The `%` is part of the value that is bound, not part of the prepared query — Mark Baker, Sep 29 '13 at 17:35
Possible duplicate: http://stackoverflow.com/questions/1786436/php-pdo-prepared-statement-mysql-like-query — ioan, Sep 29 '13 at 17:38

raina77ow · Answer 1 · 2013-09-29T17:40:16.353

0

The problem is that ? should either stay as a placeholder in SQL expression or - again, as a placeholder, be used in SQL function(s). So you have two options here: just add % to a supplied value, like this:

$val = $_POST['name'] . '%';
$stmt->prepare("SELECT id, name, email FROM some_table 
  WHERE name LIKE ?"); 
$stmt->bind_param('s', $val);

... or leave the value as is, modifying SQL instead:

$stmt->prepare("SELECT id, name, email FROM some_table 
  WHERE name LIKE CONCAT(?, '%')"); 
$stmt->bind_param('s', $val);

edited Sep 29 '13 at 17:40

answered Sep 29 '13 at 17:35

raina77ow

103,633
15
192
229

When I did what you mentioned, here is the error I received: Fatal error: Cannot pass parameter 2 by reference – sridhar Sep 29 '13 at 17:39
Updated the answer: it's bindParam, not bindValue, so the value should be passed by reference indeed. – raina77ow Sep 29 '13 at 17:41

score 0 · Accepted Answer · answered Sep 29 '13 at 17:36

Change it as below:

$val = $_POST['name'] . '%'; 
$stmt = $mysqli->stmt_init(); 
$stmt->prepare("SELECT id, name, email FROM some_table WHERE name LIKE ?"); 
$stmt->bind_param('s', $val);
$stmt->execute();

Basically, the prepared stmt should still have only the placeholder '?', while your variable should have the wildcard '%' at end

prepared statement and binding parameters conflict

2 Answers2