CODEWITHSUNDEEP
phpmysql
frosty
frosty

Reputation: 2851

Update column value based on PHP variable which is equal to a column name

$rating =  mysqli_real_escape_string($conn,$_POST['rating']);
$id = mysqli_real_escape_string($conn,$_POST['id']);
mysqli_query($conn,"UPDATE table SET $rating=$rating+1 WHERE id='$id'");

Is there any way to update a column based on the PHP variable $rating? $rating is a column name.

Also, this may be prone to security risks etc, so I'd like to know if this is even a good way to go about it.

Upvotes: 0

Views: 2513

Answers (3)

Tim Dawson
Tim Dawson

Reputation: 89

Validating the column variable didn't work for me with PDO. I tried various arrangements of brackets around the '$colname+1' scenario, but they all gave errors. Finally I opted for SELECTING the current value, incrementing it and putting it back.

//  Find existing value:
    $query = "SELECT " . $col . " from " . $table . "
        WHERE bk_ref = :bk_ref";

    echo 'Select query is: ' . $query . ' with bk_ref: ' . $bk_ref . '<br />';

    $stmt = $pdo->prepare($query);
    $stmt->execute(['bk_ref' => $bk_ref]);

    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    echo 'Row is: '; print_r ($row); echo '<br />';
    $val = $row[$col];
    echo 'Found value: ' . $val . '<br />';
// Could use FETCH_NUM, $val would be at $row[0]

// Increment by 1 (or make any other changes required):    
    $val++;

//  Put it back into the table:
    $query1 = "UPDATE " . $table . " SET " . $col . " = " . $val . "
        WHERE bk_ref = :bk_ref";

    echo 'Update query is: ' . $query1 . ' with bk_ref: ' . $bk_ref . '<br />';

    $stmt = $pdo->prepare($query1);
    $stmt->execute(['bk_ref' => $bk_ref]);

I am aware that this doesn't precisely answer the question and might be regarded as 'old school', but it does at least provide a reasonably concise solution, once the 'echoes' and comments are taken out.

Upvotes: -2

Ulver
Ulver

Reputation: 905

Yes you can use variable name as field name in the sql. However you must validate it first before putting it into sql string. Since its not a field value, you cannot "quote" it.

$rating =  $_POST['rating'];
// Define list of valid "rating" db field names here
$valid_fields = Array('rating_a', 'rating_b', 'rating_c');

if (in_array($rating, $valid_fields)) {
    $id = mysqli_real_escape_string($conn,$_POST['id']);
    mysqli_query($conn,"UPDATE table SET $rating=$rating+1 WHERE id='$id'");
}

Upvotes: 2

Gideon Appoh
Gideon Appoh

Reputation: 663

First and foremost your sql is not correct because I don't think a variable can be a column name in mysql. So please check in the mysql database for the exact column you want to update. So your sql will be something like

$rating =  mysqli_real_escape_string($conn,$_POST['rating']) + 1;

$id = mysqli_real_escape_string($conn,$_POST['id']);
mysqli_query($conn,"UPDATE `table` SET `rating`=$rating WHERE id='$id'");

If rating is not your column name then change it to the exact column name. Hope I helped.

Upvotes: -3

Related Questions