ajd2598 ajd2598 - 1 year ago 61
MySQL Question

Getting syntax error when trying to update numerical value (MySQL)

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 (

) in question has four similar fields (along with other fields):

  • wild_count

  • city_count

  • nether_count

  • end_count

On the HTML form, the user can select a land type when submitting, and the script attempts to perform a string concatenate to complete the field, then supplies this for the placeholder in the prepared SQL query, as such:

//Set land type string
$landtype = $_POST['landtype'] . '_count';
//Process ADD request
if (!isset($_POST['negative']))
$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);
$query->bindValue(':landtype', $landtype);
$query->bindValue(':tiles', $_POST['tiles']);
$query->bindValue(':id', $_POST['player']);
catch (PDOException $e)
$error = 'Error updating land count: ' . $e->getMessage();
include './includes/error.inc.php';
...more code follows...

When trying to POST my form using the following code, I get the following error:

Error updating land count: SQLSTATE[42S22]: Column not found: 1054 Unknown column ''city_count'' in 'field list'

(I had selected
in my form example).

I've tried the same code, except without the backticks around the placeholder
$sql = 'UPDATE players SET :landtype = :landtype + :tiles WHERE id = :id';
) and I get a different error:

Error updating land count: SQLSTATE[42000]: 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

I'm not certain how to proceed. Does the attempt at setting the field value by creating a concatenated string break it here?

Answer Source

Don't try to bind column name like it's a value:

$sql = 'UPDATE players SET `'.$landtype.'` = `'.$landtype.'` + :tiles WHERE id = :id';

Can PHP PDO Statements accept the table or column name as parameter?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download