Conditional update SQL statement in PHP

Question

I am trying to update my third column value (option_value) with my database and code below:

if(isset($_POST['settings_updatebtn']))
{
    $finerate= $_POST['fine_rate'];
    $issue_email = $_POST['issue_template'];
    $return_email = $_POST['return_template'];

    $query = "UPDATE
                settings
            SET
                option_value = CASE option_name WHEN 'finerate' THEN '$finerate' WHEN 'email_temp_issue' THEN '$issue_email' WHEN 'email_temp_return' THEN '$return_email'
            WHERE
                option_name IN (
                'finerate',
                'email_temp_issue',
                'email_temp_return'
                )";
    $query_run = mysqli_query($connection,$query);

    if($query_run)
    {
        $_SESSION['success']= "Your settings is updated";
        header('Location: systemsettings.php');    }
    else
    {
        $_SESSION['status']= "Your settings is NOT updated";
        header('Location: systemsettings.php');    }
}

However it shows query not running, is my SQL statement wrong in somewhere?

@AbhinK showing status: Your settings is NOT updated. The query did not run — Carol H, Mar 26 '20 at 15:26
@CarolH refer to [here](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) :) — treyBake, Mar 26 '20 at 16:14

score 1 · Accepted Answer · answered Mar 26 '20 at 15:59

1

use END at the end of your case statement:

UPDATE
    settings
SET
    option_value = 
        CASE option_name 
            WHEN 'finerate' THEN '$finerate' 
            WHEN 'email_temp_issue' THEN '$issue_email' 
            WHEN 'email_temp_return' THEN '$return_email'
        END
WHERE
    option_name IN (
    'finerate',
    'email_temp_issue',
    'email_temp_return'
    )

answered Mar 26 '20 at 15:59

draz

793
6
10

1

this code is open to SQL injection and should be amended according to [this](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – treyBake Mar 26 '20 at 16:14

Conditional update SQL statement in PHP

1 Answers1