CODEWITHSUNDEEP
phpmysql
Sushivam
Sushivam

Reputation: 3117

mysql update column to null or its value

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 ']."'

Upvotes: 2

Views: 975

Answers (3)

user9008566
user9008566

Reputation: 

'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.

Upvotes: 0

T. Altena
T. Altena

Reputation: 811

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.

Upvotes: 1

Nick
Nick

Reputation: 147256

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."';";

Upvotes: 2

Related Questions