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
– Carol H, Commented Mar 26, 2020 at 15:26

draz · Accepted Answer · 2020-03-26 15:59:34Z

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, 2020 at 15:59

draz

8467 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

treyBake Over a year ago

this code is open to SQL injection and should be amended according to this

Collectives™ on Stack Overflow

Conditional update SQL statement in PHP

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related