SQL UPDATE value as null or string

Question

I searched almost whole internet, but could not find something similar, yet my question looks so simple.

I have a php code like so:

$id = 1;    
if (!isset($_POST['port1'])) {
        $port1 = null;
    }

... which simply checks if submitted value is empty or not, if it is empty, then variable $port1 = null;

then, further in the code, I need to insert this value/update it in the database

$sql_update = ("UPDATE names_10 SET `digName1`=$port1 WHERE `device_id`='$id'");

...which should set the "digname1" to null. but it won't! I tried every combination, every type of quotes, but every time I got UPDATE error..

any ideas?

`null` in a string in php is not the same as `NULL` as a value in a query. I think you should solve this by using parameters rather than munging the string. — Gordon Linoff, Apr 05 '17 at 10:44
http://stackoverflow.com/questions/9314353/set-value-to-null-in-mysql — u_mulder, Apr 05 '17 at 10:45
@GordonLinoff What do you mean by "using parameters"? I need to use this variable — David Stančík, Apr 05 '17 at 10:45
@AlexSlipknot i didn't heard about that... kinda beginner in php — David Stančík, Apr 05 '17 at 10:49
@BunkerBoy not that easy... it is made with AJAX request on other site So it Only echos the text error... which happens If there was error in SQL update... But Okay, i will find the error and Edit my question — David Stančík, Apr 05 '17 at 10:51
Here is an example how to use PDO: http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-pdo/ — Alex Slipknot, Apr 05 '17 at 10:52
@Alex Slipknot let him start without pdo as he said he is beginner in php — lazyCoder, Apr 05 '17 at 10:54
@Bunker Boy let it be, but at least there is must be injection-protection! — Alex Slipknot, Apr 05 '17 at 10:55
@AlexSlipknot there is, this is Only the important part of code.. injection solving things does not matter in this question.. don't worry — David Stančík, Apr 05 '17 at 10:56

score 1 · Accepted Answer · answered Apr 05 '17 at 10:51

1

Try this:

$id = 1;    
if (!isset($_POST['port1'])) {
        $port1 = "NULL";
}

$sql_update = ("UPDATE names_10 SET `digName1`= $port1 WHERE `device_id`='$id'");

I would rather suggest you to use PDO when you plan to bind something like this. There are a lot of benefits using PDO that would amaze you!

answered Apr 05 '17 at 10:51

Satish Saini

2,880
3
24
38

Okay, i will read about it. And try What? Looks identical to my code ... – David Stančík Apr 05 '17 at 10:52
case sensitive 'NULL' (that is string and not NULL) – Alex Slipknot Apr 05 '17 at 10:53
Okay, i will try – David Stančík Apr 05 '17 at 10:54
Do you want null as a string in your database? – Satish Saini Apr 05 '17 at 10:59
@SatishSaini boom, after you updated the answer i tried... Now it works!!!!!! Thanks!!!! – David Stančík Apr 05 '17 at 11:00
@DavidStančík Cool :) Glad that it worked for you! :) – Satish Saini Apr 05 '17 at 11:00
@SatishSaini Thanks a lot! :-) – David Stančík Apr 05 '17 at 11:01

SQL UPDATE value as null or string

1 Answers1