PDO prepare - preventing null value SQL update [duplicate]

Question

I have optional input provided as the 'type' value. I'm trying to prevent the statement from updating the type if it is null data and continue to update the equipment value.

Im tempted to create seperate prepare statements dependant on PHP check of null, but then found the SQL ISNULL(). I havnt used it before and am unsure how to use it within a prepare, unless there is a better way of achieving this?

PHP:

    $update = $db -> prepare("UPDATE room 
    SET type = ISNULL(@:type,:type), equipment = :equipment 
    WHERE room_id = :room_id");

    $update -> bindParam(":room_id", $room_id);
    $update -> bindParam(":type", $data['type']);
    $update -> bindParam(":equipment", $equipmentList);

    $update -> execute();

I get a 'SQLSTATE[42000]: Syntax error or access violation: 1582' when attempting the above.

UPDATE:

    UPDATE room 
    SET type = IFNULL(:type, type), equipment = :equipment 
    WHERE room_id = :room_id

Ok this correct syntax fixes errors, thanks to eggyal! The problem now is that null :type values are updated and the database valuetype(which is a enum) is not used instead of the null. I double checked in php before SQL that the :type values is null.

Why is IFNULL still returning a null?

Answer 1

• Use bindValue instead of bindParam, you are not altering the value of parameter, therefore you don't need to pass it by reference.

• if the data type in MySQL can contain null then you can tell it that you are sending a NULL value. Code:

$update -> bindValue(":type", $data['type'], is_null($data['type'] ? PDO::PARAM_NULL : PDO::PARAM_STR);

If your target column cannot contain null then you do not need to execute the query at all.