SQL: Edit only if input is not null

Question

I currently working on simple Christmas gifts database :) and I have a problem with my Edit function. When user select existing gift for edit (by ID) and enter new values (for example for price) I want only that the price is changed and everything else is kept as it was. I try to use function IFNULL but my code is not working as I expected. Everytime i get new value for price, the other fields are erased.

My code (Iam using MySQL):

            else if($_REQUEST['btn_submit']=="Edit")
            {
                    $gifts_id = $_POST["gifts_id"];

                    $year = $_POST["year"];
                    $whom = $_POST["whom"]; 
                    $category = $_POST["category"]; 
                    $what = $_POST["what"]; 
                    $shop = $_POST["shop"]; 
                    $url = $_POST["url"]; 
                    $price = $_POST["price"]; 
                    $note = $_POST["note"]; 
                    $status = $_POST["status"];                  

                    Db::query("
                        UPDATE `gifts` 
                        SET
                            `year` = ifnull('$year',`year`),
                            `whom` = ifnull('$whom',`whom`),
                            `category` = ifnull('$category',`category`),
                            `what` = ifnull('$what',`what`),
                            `shop` = ifnull('$shop',`shop`),
                            `url` = ifnull('$url',`url`),
                            `price` = ifnull('$price',`price`),
                            `note` = ifnull('$note',`note`),
                            `status` = ifnull('$status',`status`) 
                        WHERE
                            `gifts_id` = '$gifts_id';
                              ");

                    echo("<p>Gift with ID:'$gifts_id' successfully updated</p>");
            }

Thanks for answers!

PS: I code just for fun so please be mercyful :)

I think you might be a little late to market with this site :-) — RiggsFolly, Dec 20 '17 at 17:39
You'll get SQL injections every minute... in any case, I don't think the variables are null, they're probably just undefined or empty strings... try `print_r()`ing all the variables after the assignations and see what you get. If they're not explicitly null, then that's why everything's getting overwritten (erased). — Zeke, Dec 20 '17 at 17:49
Please see https://stackoverflow.com/q/60174/1637737 about SQL injections. Secure code is happy code. — Mic1780, Dec 20 '17 at 18:13
Thank you, Iam aware of that :). Just now Iam in phase where I fight with every line of my code but I want to secure it in the future. I was using Excel table for this purpose but this year I decide to move on a try to code my gift database into php and sql. Its just hobby, like crosswords for some people. — KoubiCZ, Dec 20 '17 at 18:27

score 0 · Answer 1 · answered Dec 20 '17 at 18:11

If your want to properly edit your values, first you should fill all your inputs with your old values so the user can edit them or leave them as it was. Then you can check that all the values are not null before calling the sql as shown below:

else if($_REQUEST['btn_submit']=="Edit")
            {
                    $gifts_id = $_POST["gifts_id"];

                    $year = $_POST["year"];
                    $whom = $_POST["whom"]; 
                    $category = $_POST["category"]; 
                    $what = $_POST["what"]; 
                    $shop = $_POST["shop"]; 
                    $url = $_POST["url"]; 
                    $price = $_POST["price"]; 
                    $note = $_POST["note"]; 
                    $status = $_POST["status"];                  
if(!empty($gifts_id)&&!empty($year)&&!empty($whom)&&!empty($category)&&!empty( $what)&&!empty($shop)&&!empty($url )&&!empty($price)&&!empty($note)&&!empty($status))
{

Db::query("
                        UPDATE `gifts` 
                        SET
                            `year` = ifnull('$year',`year`),
                            `whom` = ifnull('$whom',`whom`),
                            `category` = ifnull('$category',`category`),
                            `what` = ifnull('$what',`what`),
                            `shop` = ifnull('$shop',`shop`),
                            `url` = ifnull('$url',`url`),
                            `price` = ifnull('$price',`price`),
                            `note` = ifnull('$note',`note`),
                            `status` = ifnull('$status',`status`) 
                        WHERE
                            `gifts_id` = '$gifts_id';
                              ");

                    echo("<p>Gift with ID:'$gifts_id' successfully updated</p>");
            }
else
{
echo("<p>Gift with ID:'$gifts_id' was not updated, please check your data</p>");
}

Thank you! I really like your idea. But now I have problem with loading values into input fields. I try: But my input fields are on the screen from beginning so $year is not defined yet. Any sugestions how accomplish that? :) Thank you! — KoubiCZ, Dec 20 '17 at 18:59
While loading the page, you have to select the values from the db and assign them into variables such as $year then your code should work fine — SMH, Dec 20 '17 at 19:11

Barmar · Answer 2 · 2017-12-20T18:18:26.703

IFNULL tests only for the special NULL value, and quoted strings are never null. You should compare the strings with ''.

                Db::query("
                    UPDATE `gifts` 
                    SET
                        `year` = if('$year' = '',`year`, '$year'),
                        `whom` = if('$whom' = '',`whom`, '$whom'),
                        ...
                    WHERE
                        `gifts_id` = '$gifts_id';
                          ");

Another option is to build the query dynamically.

$assign_array = array();
foreach (array('year', 'whom', 'category', ...) AS $field) {
    if ($_POST[$field] !== '') {
        $assign_array[] = "`$field` = '{$_POST[$field]}'";
    }
}
$assign_string = implode(',', $assign_array);
Db::query("
    UPDATE `gifts` 
    SET $assign_string   
    WHERE  `gifts_id` = '$gifts_id';");

Note, however, that this is vulnerable to SQL injection. If your DB API allows you to create prepared queries and provide an array of values, you should do that. You can build up the parametrized query and array of values in a similar manner to this.

SQL: Edit only if input is not null

2 Answers2