Update MySQL with Dynamic Fields

Question

I'm making a game using PHP with MySQL and I need an UPDATE query where the fields names could change from one user to another.

My code is:

$upd = $sql->prepare("UPDATE empire_users SET :p = :p + :p2 WHERE id = :id");
$upd->execute(array(
    ':p' => "p".$type,
    ':p2' => 10,
    ':id' => $_SESSION["id"]
));

In my database, users have 3 columns : pwood, pstone, pwheat, and $type could only be "wheat", "stone", or "wood".

I want to update the selected field (which depends of $type) to increase by p2 (here 10).

Answer 1

See http://php.net/manual/fr/pdo.prepare.php, especially following comment:

"To those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names:

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for "SELECT name FROM my_table WHERE id = :value" will be the same whatever you substitute for ":value", but the seemingly similar "SELECT name FROM :table WHERE id = :value" cannot be planned, because the DBMS has no idea what table you're actually going to select from."

Answer 2

Thanks for your answers, I corrected :

$upd = $sql->prepare("UPDATE empire_users SET p".$type." = p".$type." + :p2 WHERE id = :id");
$upd->execute(array(
    ':p2' => 10,
    ':id' => $_SESSION["id"]
));