PHP SQL UPDATE only if post data has input given using COALESCE()

Question

Hey guys I've read a lot of posts about this (that are very old) and I have tried everything in them but I cant seem to figure out the problem..

So I have a form for updating table records. But say if no name was entered then it doesn't keep the original name an update the rest it just replaces it with an empty string.

I have tried using COALESCE() but it doesn't recognize anything from my php code as a NULL value.

If I don't put quotation marks '' then I get SQL errors but if I do it's not recognized as null..

for example if I ssh in and run MySQL and type..

select coalesce('','test');

then MySQL just displays the empty string not the string with something in it.

Just cant seem to get past doing queries with post variables without the quotation mark problem..

Here is my code...

/* Original query that replaces with empty input feilds...
$update = "UPDATE KIT202_product SET Name='". $_POST['name']
. "', Price='". $_POST['price']
. "', Description='". $_POST['desc']
. "' WHERE ID=". $_POST['id'];
*/

//What I ended up trying but still doesn't work :(...
$name=NULL;
if (!empty($_POST['name'])) $name="'". $_POST['name']. "'";
$price=NULL;
if (!empty($_POST['price'])) $price="'". $_POST['price']. "'";
$desc=NULL;
if (!empty($_POST['desc'])) $desc="'". $_POST['desc']. "'";

this doesnt work because null just passes nothing instead of NULL so it like running coalesce with only 1 value.

Answer 1

What if you do 2 things: 1) Create a small validation function for your fields; 2) Split the update to only update what has passed validation. Hence, loosely:

$updatePrefix = "UPDATE KIT202_product SET ";
$updateSuffix = "WHERE ID=". $_POST['id']; // You should first these for injection

$conditions = '';
if (isValid($_POST['name'])) $conditions .= " Name='".$_POST['name']."'";
if (isValid($_POST['price'])) {
   if ($conditions != '') $conditions .= ', ';
   $conditions .= " Price= '".$_POST['price']."'";
}
if (isValid($_POST['desc'])) {
    if ($conditions != '') $conditions .= ', ';
    $conditions .= " Description= '".$_POST['desc']."'";
}

if ($conditions != '') $query = $updatePrefix . $conditions . $updateSuffix;

function isValid($input) {
    return (!(empty($input) && $input == ''));
}

This should work to do what you need.

UPDATE: Answering to your comment, yes you can use COALESCE. Try something like this:

$name=NULL;
if (!empty($_POST['name'])) $name="'". $_POST['name']. "'";
$price=NULL;
if (!empty($_POST['price'])) $price="'". $_POST['price']. "'";
$desc=NULL;
if (!empty($_POST['desc'])) $desc="'". $_POST['desc']. "'";
$update = "UPDATE KIT202_product SET Name=COALESCE($name, KIT202.Name)"
. "', Price=COALESCE($price,KIT202.Price)"
. "', Description=COALESCE($desc, KIT202.Description)"
. "' WHERE ID=". $_POST['id'];