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 — MrCode
– MrCode, Commented Apr 7, 2014 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
– dewd, Commented Apr 7, 2014 at 16:36

Your Common Sense · Accepted Answer · 2014-04-07 16:00:59Z

1

Use <=> operator

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

answered Apr 7, 2014 at 16:00

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using PHP mysqli: where clause in bind_param might be NULL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related