How to use prepared statement ? for IS NULL

[tork]

Here's what works:

 

$q = "SELECT user_id FROM users WHERE pass=? AND active IS NULL";

$r = mysqli_prepare($dbc, $q)

mysqli_stmt_bind_param($r, 's', $pw);  

 

mysqli_stmt_execute($r);

 

 

However, what I'd like to do is make the SELECT like the following to check active IS NULL:

 

$a = 'IS NULL';

 

$q = "SELECT user_id FROM users WHERE pass=? AND active=?";

$r = mysqli_prepare($dbc, $q)

mysqli_stmt_bind_param($r, 'ss', $pw, $a);  

 

mysqli_stmt_execute($r);

 

 

Since this doesn't work, anybody got any ideas what would work?

 

[requinix]

You can't: prepared statements are about passing data and IS NULL is actually about changing the structure of the query itself. "IS" is an operator like > and = and you can't change operators after you've prepared the statement.

 

PHP doesn't have a good solution for passing an arbitrary number of parameters to bind_param(), but you can still do it.

$q = "SELECT user_id FROM users WHERE";
$bind = array("");

// password
$q .= " pass = ?";
$bind[0] .= "s";
$bind[] =& $pw; // reference

// active
if ($a === null) {
	$q .= " AND active IS NULL";
} else {
	$q .= " AND active = ?";
	$bind[0] .= "s";
	$bind[] =& $a; // reference
}

$r = mysqli_prepare($dbc, $q);
call_user_func_array(array($r, "bind_param"), $bind); // easier to call it as a method

mysqli_stmt_execute($r);

[tork]

Ah! That explains why it didn't work. However, I got this error when I replaced the code:

 

call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object

[kicken]

mysqli_prepare is failing, probably due to your query being invalid. Echo out $q and check it to make sure you are generating a valid query.

[tork]

That was it. Thanks to both of you. Don't you just love that Aha feeling when you discover something new?