CODEWITHSUNDEEP
phpmysqldatabase
Ahmed Yasin
Ahmed Yasin

Reputation: 33

Update MYSQL row with non null value only

i have a table contain huge number of rows and i need to update row with specific ID, for example assume i have a row with below details:

Id= 1
Name= lessa
Address = USA

now i used below PHP code to update the row:

<?php
$con = mysqli_connect("localhost","MyUserName","MyPassword","DB");
$id = '1';
$name = "";
$address = "UK";
// update only non value items
$r=mysqli_query($sql);
mysqli_close($con);
?>

now my issue since the value of address is changed from USA to UK i need to update this value only, also since the name value is nothing the name should be remain so after update the row should be like below:

ID=1
Name = lessa
Address = UK

Also if in another time the name value changed and address remain the same i need to update the name only.

also assume i have 100 column not only three as this example.

any help for write the update statement will be appreciated.

Update:

I use below code but no update happen:

<?php
$con = mysqli_connect(DB info);
$id = 'jo_12';
$name = "";
$address = "UK";
$sql = "UPDATE info
        SET name = IF(? = '', name, ?),
            address = IF(? = '', address, ?)
        WHERE id = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param("ssssi", $name, $name, $address, $address, $id);
$stmt->execute();

mysqli_close($con);
?>

Upvotes: 0

Views: 1338

Answers (2)

Barmar
Barmar

Reputation: 782166

Put tests in the UPDATE query:

$sql = "UPDATE yourTable
        SET name = IF(? = '', name, ?),
            address = IF(? = '', address, ?)
        WHERE id = ?";
$stmt = $con->prepare($sql) or die ($con->error);
$stmt->bind_param("sssss", $name, $name, $address, $address, $id);
$stmt->execute();

The IF() tests assign the old value of the column back to it if the variable is empty.

Upvotes: 4

Tom&#225;š Pajurek
Tom&#225;š Pajurek

Reputation: 86

Try to use this SQL query:

UPDATE table_name SET Address='UK' WHERE ID=1

You can of course substitute ID=1 for any other number.

Upvotes: 0

Related Questions