mysql update column to null or its value

Question

I am trying to update the column (LastName) to NULL, if the column has empty value, else update its value using the below mysql query.

$updateUserInfo = "UPDATE `UsersNew` SET MobileNo = '".$dataArray['MobileNo']."',FirstName = '".$dataArray['FirstName']."', LastName = IF(LastName = '' OR LastName IS NULL, 'NULL', LastName), EmailId = '".$dataArray['EmailId']."' where Uid = '".$uid."';";

But the LastName value is not getting updated

I want if LastName is null i want the column to get updated as NULL else '".$dataArray['LastName ']."'

This can cause problems for people such as [Jennifer Null](http://www.bbc.com/future/story/20160325-the-names-that-break-computer-systems) — Nigel Ren, Jan 03 '19 at 08:17
Under no circumstances should we use values directly appended into queries. You can have a thousand ways to prevent injection, but there's always the 1001th. Use prepared statements, injection is still possible but with an appropriate charset, especially if you only need English and Western languages, then Injection can be avoided with prepared statements. You can worry about other issues such as XSS. — Ronnie, Jan 03 '19 at 08:46

Nick · Accepted Answer · 2019-01-03T08:07:29.883

2

Basically you need to change the section which updates LastName to this:

LastName = " . (empty($dataArray['LastName']) ? 'NULL' : "'{$dataArray['LastName']}'") . "

Note that NULL should not be enclosed in quotes. So your whole query becomes:

$updateUserInfo = "UPDATE `UsersNew`
                   SET MobileNo = '".$dataArray['MobileNo']."',
                   FirstName = '".$dataArray['FirstName']."', 
                   LastName = ".(empty($dataArray['LastName']) ? 'NULL' : "'{$dataArray['LastName']}'") . ",
                   EmailId = '".$dataArray['EmailId']."'
                   WHERE Uid = '".$uid."';";

edited Jan 03 '19 at 08:07

answered Jan 03 '19 at 07:54

Nick

138,499
22
57
95

I tried above, but if LastName is empty, LastName column gets updated as empty (blank), not updating like expected "NULL", if i have $dataArray['LastName]`, then it updates value in column properly – Sushivam Jan 03 '19 at 07:58
@Sushivam do you mean `$dataArray['LastName`]` is empty? – Nick Jan 03 '19 at 07:59
Yes if i dont pass LastName ie) if i dont have $dataArray['LastName]`, then my column LastName should update as NULL, instead its updating as empty. – Sushivam Jan 03 '19 at 08:02
@Sushivam sorry I misunderstood your question. See my edit, also a small demo at https://3v4l.org/ToFKU – Nick Jan 03 '19 at 08:08

T. Altena · Answer 2 · 2019-01-03T08:10:34.167

1

Ternary operators are nice for this:

$updateUserInfo = "UPDATE `UsersNew` 
                   SET MobileNo = '".$dataArray['MobileNo']."',
                   FirstName = '".$dataArray['FirstName']."', 
                   LastName = ".$dataArray['MobileNo']? "'".$dataArray['MobileNo']."'":"NULL, 
                   EmailId = '".$dataArray['EmailId']."' where Uid = '".$uid."';";

Beware though, you need to clean up the input using prepared statements or PDO, otherwise your code might be subject to SQL Injection.

edited Jan 03 '19 at 08:10

answered Jan 03 '19 at 07:56

T. Altena

752
4
15

Your ternary query throws syntax error, Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 1", – Sushivam Jan 03 '19 at 08:03

score 0 · Answer 3 · answered Jan 03 '19 at 08:18

'UsersNew'

should not have the ' ' and be UsersNew

Example from w3Schools:

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

Another Source: Update query PHP MySQL

This in itself might not fix everything, but with the other answers in combination, it might. Lastly, use prepared statements.

mysql update column to null or its value

3 Answers3