UPDATE Mysql database with COALESCE-CodePudding

I want to update the image fields only if the user's input is not NULL. I'm trying to do it with coalesce but it doesn't work. I've tried to insert the query manually by replacing the $image and $nameimg fields with NULL and the other fields with random value and it worked. Can someone please help me?

Here's my code:

if(isset($_POST["mod_name"])) 
{  
    $id_mod=$_POST["mod_id"];
    $name=mysqli_real_escape_string($conn,$_POST["mod_name"]);
    $description=mysqli_real_escape_string($conn,$_POST["mod_description"]);
    $price=$_POST["mod_price"];
    $category=mysqli_real_escape_string($conn,$_POST["mod_category"]);
 
    if($_FILES['mod_image1']['size'] == 0)
    { 
        $image1=addslashes(file_get_contents($_FILES["mod_image1"]["tmp_name"]));
        $nameimg1=basename($_FILES["mod_image1"]["name"]);
    }
    elseif($_FILES['mod_image1']['size'] != 0)
    {
        $image1=NULL;
        $nameimg1=NULL;
    }
    if($_FILES['mod_image2']['size'] == 0)
    { 
        $image2=addslashes(file_get_contents($_FILES["mod_image2"]["tmp_name"]));
        $nameimg2=basename($_FILES["mod_image2"]["name"]);
    }
    elseif($_FILES['mod_image2']['size'] != 0)
    {
        $image2=NULL;
        $nameimg2=NULL;
    }
    if($_FILES['mod_image3']['size'] == 0)
    { 
        $image3=addslashes(file_get_contents($_FILES["mod_image3"]["tmp_name"]));
        $nameimg3=basename($_FILES["mod_image3"]["name"]);
    }
    elseif($_FILES['mod_image3']['size'] != 0)
    {
        $image3=NULL;
        $nameimg3=NULL;
    }
       
 mysqli_query($conn,"UPDATE product SET Product = '$name', Description = '$description', Price = '$price', Category = '$category', Image = COALESCE($image1, Image), Image_name = COALESCE($nameimg1, Image_name), Image_2 = COALESCE($image2, Image_2), Image_name_2 = COALESCE($nameimg2, Image_name_2), Image_3 = COALESCE($image3, Image_3), Image_name_3 = COALESCE($nameimg3, Image_name_3) WHERE ID_product = '$id_mod'");

}

Thank you!

CodePudding user response：

It fails because of the way you create the query string: a NULL value will never appear and strings inside the query need quotes.

Take this part and values:

COALESCE( $image1, Image ). The output should be:

COALESCE( 'abc.jpg', Image ) a string with quotes

COALESCE( NULL, Image ) a NULL value as word (no quotes)

What happens in your code?

$image    query-string               result  because
abc.jpg   COALESCE( abc.jpg, Image ) fail    strings need quotes
NULL      COALESCE( , Image )        fail    null value won't print

We could put default quotes around the string:

COALESCE( '$image1', Image )

$image   query-string                 result  because
abc.jpg  COALESCE( 'abc.jpg', Image ) good    strings have quotes
NULL     COALESCE( '', Image )        fail    null value won't print,
                                              we get empty string

So we keep the quotes and make the NULL value a (string) NULL with $image1 = 'NULL';

$image         query-string                 result  because
abc.jpg        COALESCE( 'abc.jpg', Image ) good    strings with quotes
(string) NULL  COALESCE( 'NULL', Image )    fail    (string) NULL isn't the same
                                                      as a null value.

WARNING You shoudn't do it as below, as you're wide open to SQL injections. My recommendation: switch to PDO and prepared statements. It is much safer AND you don't have to bother about strings, numbers or NULL values anymore, as the prepared statement accepts them as they are.

So what you need is: no default quotes in the query, wrap your strings in quotes and make NULL a string:

$nameimg1="'".basename($_FILES["mod_image1"]["name"])."'';
   // result: 'abc.jpg' (string) with quotes 
$nameimg1="NULL"; 
   //result (string) NULL without quotes