PDO prepare - preventing null value SQL update

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?

Why don't you just check in PHP if it's null and if not - executed the statement, otherwise do nothing? Keep it simple, and it's simple if it's checked in PHP since it won't reach MySQL at all - therefore, it's even faster. — Mjh, Jan 21 '16 at 12:19

score 3 · Answer 1 · answered Jan 21 '16 at 14:38

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.

eggyal · Answer 2 · 2016-01-21T12:32:42.257

0

MySQL's ISNULL() function takes only a single argument and returns either 1 or 0 indicating whether that argument was null or not, respectively. It is equivalent to the IS NULL comparison operator.

MySQL does have a 2-argument IFNULL() function, which may have been what you intended to use (though the arguments you've applied are nonsense)?

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

If :type is not NULL, its value will be used; else the incumbent value of the type column will be used instead (yielding no change).

edited Jan 21 '16 at 12:32

answered Jan 21 '16 at 12:24

eggyal

122,705
18
212
237

It might still be important to have the *equipment* field updated. – trincot Jan 21 '16 at 12:27
I see..I got them mixed up, thanks you for the explaination, it helped alot! It is error free, but excepts null values now. The type value stored in database is a 'enum', would this affect the result somehow? – Orbitall Jan 21 '16 at 13:24
@Orbitall: Sorry, I don't understand what you mean by "*excepts null values now*"—please elaborate? – eggyal Jan 21 '16 at 13:26
@Orbitall - why are you checking for `NULL` inside the query? You obviously don't want `NULL` in any form or way, your data type apparently won't even accept it. Why do you bother doing this check in SQL? It's trivial in php `if(empty($your_variable)) return false;` and job done, SQL's job is not to implement validation of your input. – Mjh Jan 21 '16 at 13:41
@eggyal: The `IFNULL()` doesnt seem to be working as intended. The null value ':type' is used and not replaced with the current database value 'type'...Check update. – Orbitall Jan 21 '16 at 13:42
@Mjh: Well my orginal question asked "...unless there is a better way of achieving this?". I am trying out the 1st answer to my question assuming this is the best method. Are you suggesting that I should have multiple SQL statements dependant on null input? Would this be alot more efficient? – Orbitall Jan 21 '16 at 13:50

score 0 · Answer 3 · answered Jan 21 '16 at 14:13

You can use the COALESCE() function. It takes as many arguments as you give it and returns the first not-null argument it encounters if any. Otherwise it returns NULL.

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

PDO prepare - preventing null value SQL update

3 Answers3