UPDATE Mysql database with COALESCE

Question

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!

instead of `COALESCE` use `Image = IF( $image1 IS NOT NULL, $image1, Image)`. In that case, when `$image1 == NULL` the field is set to it's own value. — Michel, Sep 24 '21 at 09:46
The variable which may be NULL must contain quoted value when it is provided. Not `$image = "abc.jpg";` but `$image = "'abc.jpg'";`. — Akina, Sep 24 '21 at 09:48
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 24 '21 at 09:50
@Michela I've tried like you suggested but still doesn't work. — albyx, Sep 24 '21 at 10:08
@albyx I'm writing you an answer. @Alkina is correct, as you need to wrap the strings in quotes. And the `NULL` will never print in the string. — Michel, Sep 24 '21 at 10:48

score 0 · Accepted Answer · answered Sep 24 '21 at 11:11

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

or

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

Thank you for the specific answer! I've done what you writed but now it inserts the value "NULL" into the $image and $nameimg fields (with COALESCE) and trying something like this `Image = IF( '$image1' IS NOT 'NULL', '$image1', Image)` doesn't work. — albyx, Sep 24 '21 at 12:22
Echo your query and take a good look at it. Are all the quotes where they need to be? Are the NULL values NULL? — Michel, Sep 24 '21 at 12:39

UPDATE Mysql database with COALESCE

1 Answers1