I am an amateur programmer creating a PHP based online portal which will update values in a MySQL database in relation to a MMO-type game, in which we are using the portal to track a total number of land tiles protected by each user.
I am working on the script which will update the table count for a given type of protected land, upon submission of an HTML form through a $_POST array.
The MySQL table (
//Set land type string
$landtype = $_POST['landtype'] . '_count';
//Process ADD request
$action = 'ADDED'; //This is for a transaction report further down in the code
$sql = 'UPDATE players SET
`:landtype` = `:landtype` + :tiles WHERE id = :id';
$query = $link->prepare($sql);
catch (PDOException $e)
$error = 'Error updating land count: ' . $e->getMessage();
...more code follows...
Error updating land count: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''city_count'' in 'field list'
$sql = 'UPDATE players SET :landtype = :landtype + :tiles WHERE id = :id';
Error updating land count: SQLSTATE: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''city_count' = 'city_count' + '300' WHERE id = '1'' at line 2
Don't try to bind column name like it's a value:
$sql = 'UPDATE players SET `'.$landtype.'` = `'.$landtype.'` + :tiles WHERE id = :id';