PHP update query value always string 'NULL' instead of NULL

Question

Okay, I have no idea what's going on here. I have an update query that two of the three fields are working exactly as I need, but no matter how much I pre-format the value for it, it still always comes up as a quoted 'NULL' in my dumped query rather than NULL as demonstrated below.

This is how I am pre-formatting my field values;

$val1 = (is_null($item1Val)) ? 'NULL' : "'" . stripslashes(json_encode($item1Val)) . "'";     
$val2 = (is_null($item2Val)) ? 'NULL' : "'" . stripslashes(json_encode($item2Val)) . "'";
$val3 = (is_null($item3Val)) ? 'NULL' : "'" . trim($item3Val) . "'";

This is the update query string I'm using in my code. I've tried using string concatenation also and that seems to yield the exact same issue;

$dml = "UPDATE table SET
field_1 = $val1,
field_2 = $val2,
field_3 = $val3,
WHERE id = $id;";

THIS, however, is how it appears when I dump the query;

UPDATE table SET
field_1 = NULL,
field_2 = NULL,
field_3 = 'NULL',
WHERE id = $id;

I have dumped the values that are coming in as the raw data and each of them come in as true NULL values, which is precisely what I am wanting. So, if they are all coming in as true NULL values, then how is the last variable being turned into a quote value rather than the first two working exactly as desired? I have no clue what I could be missing here.

This is driving me nuts!

Did you try prepared statements,as you should do this with every mysql communication. — nbk
– nbk, Commented May 3, 2020 at 19:24
@nbk - No. Didn't actually consider that since the vast majority of the application uses this particular method. I'll see about converting this implementation over to a prepared statement to see how it handles the bindings. Can you dump a prepared statement prior to execution? — Skittles
– Skittles, Commented May 3, 2020 at 19:40
the prepared statemnet is always a variabke, which you can close before excuting $stmt = $mysqli->prepare( and $stmt->close(); — nbk
– nbk, Commented May 3, 2020 at 19:44
@nbk - Not exactly sure I understand what you mean by "you can close before executing". Could you elaborate on that a little more, perhaps? Also, maybe you misunderstood my question. I was wondering if there was a way to see the resulting query it will run before it is executed? Thanks! — Skittles
– Skittles, Commented May 3, 2020 at 20:05
see the php manual php.net/manual/de/mysqli.quickstart.prepared-statements.php every prepared stament get the variabble $stmt and at the end come a close. nobody says you have to execute anything between. ot you can assign it to a new query — nbk
– nbk, Commented May 3, 2020 at 20:25

nbk · Accepted Answer · 2020-05-03 23:01:35Z

1

use prepared statements, like you always should

dml = "UPDATE table SET field_1 = ?,field_2 = ?,field_3 = ?,WHERE id = ?;";
$stmt = $mysqli->prepare(dml );
$stmt->bind_param("ssss",$val1, $val2,$val3, $id);
$stmt->execute();
//fetching affected rows
$stmt->close();

$stmt is the variable, that can be assigned new if you don't want to execute it or closed.

answered May 3, 2020 at 23:01

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

1 Comment

Skittles Over a year ago

Thanks for the intuitive answer, nbk!

Patrice Flao · Accepted Answer · 2020-05-03 20:35:59Z

0

You are using php so you should prepare the query and bind the fields :
For example :

$sta = $this->db->prepare('INSERT INTO tableName SET field_1=:field1, field_2=:field2 WHERE id=:id);

$sta->execute([
  ':field1' => $field_1,
  ':field2' => $field_2,
  ':id' => $id
]);

Of course the syntax depends or what framework you are using

answered May 3, 2020 at 20:35

Patrice Flao

5011 gold badge6 silver badges18 bronze badges

1 Comment

Skittles Over a year ago

Thanks for that reply. I'm using mysqli rather than PDO.

Collectives™ on Stack Overflow

PHP update query value always string 'NULL' instead of NULL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related