Using PHP mysqli: where clause in bind_param might be NULL

Question

I have a bound query where a column in the where clause might be null or might have a value.

Example code:

select
    name
from
    table
where
    typeId = 1

Sometimes the query might include a null value i.e.

select
    name
from
    table
where
    typeId is null

Normally with a bound query, I would do the following:

select
    name
from
    table
where
    typeId = ?

However, where typeId is null, I won't get a resultset. Only if I hardcode typeId is null will I get a result. Do you know of a solution or workaround to this?

FYI, the code for the query is:

    $sql = "select name from table where typeId = ?"; 
    $params = array(null);
    $types = array("i");

    $param_refs = array();
    foreach ($params as $key => $value) {
        $param_refs[$key] = &$params[$key];
    }
    $stmt = $mysqli->prepare($sql);


    call_user_func_array(array($stmt, "bind_param"), array_merge($types, $param_refs));
    $stmt->execute();

    $result = $stmt->get_result();

    $fields = $result->fetch_fields();

    while($row = $result->fetch_assoc()){
          //code to handle each row of result
    }

possible duplicate of [using nulls in a mysqli prepared statement](http://stackoverflow.com/questions/371644/using-nulls-in-a-mysqli-prepared-statement) — MrCode, Apr 07 '14 at 16:02
@MrCode I saw that answer, it's not a dupe. The solution is different for an insert compared to a select's where clause. As is indicated by the answer given below. — dewd, Apr 07 '14 at 16:36

score 1 · Accepted Answer · answered Apr 07 '14 at 16:00

1

Use <=> operator

$sql = "select name from table where typeId <=> ?";

answered Apr 07 '14 at 16:00

Your Common Sense

156,878
40
214
345

Using PHP mysqli: where clause in bind_param might be NULL

1 Answers1