0

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 (players) 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
    try
    {
        $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']);
        $query->execute();
    }
    catch (PDOException $e)
    {
        $error = 'Error updating land count: ' . $e->getMessage();
        include './includes/error.inc.php';
        exit();
    }
...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 city in my form example).

I've tried the same code, except without the backticks around the placeholder :landtype (i.e. $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?

Kevin Kopf
  • 13,327
  • 14
  • 49
  • 66
ajd2598
  • 25
  • 8
  • you placed backticks on wrong place $sql = 'UPDATE players SET `:landtype` = :landtype + :tiles WHERE id = :id'; remove backticks in near lantype = – JYoThI Nov 03 '16 at 04:30
  • Thanks for your suggestion, I apologize for not being more clear in the post, but I've tried both with and without backticks around the placeholder, and I get different errors (last two paragraphs). Any other thoughts? – ajd2598 Nov 03 '16 at 04:34
  • try this one $sql = 'UPDATE players SET `landtype` = :landtype + :tiles WHERE id = :id'; – JYoThI Nov 03 '16 at 04:35
  • Gave that a try, unfortunately, I get an error that "Error updating land count: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'landtype' in 'field list'" -- since landtype isn't actually a field in my table, only the 'wild_count', 'city_count', 'nether_count', and 'end_count' fields. the only other way I can think of is to write 4 separate if statements and four separate query statements based on what type of land is selected. Do you think that's necessary? – ajd2598 Nov 03 '16 at 04:39
  • if there is no column like landtype means why your trying to update that column ? – JYoThI Nov 03 '16 at 04:41
  • if you put backticks on column name means its act as string not as bindvalue so it should be 'UPDATE players set `'.$landtype.'` = `'.$landtype.'` + :tiles WHERE id =:id'; – JYoThI Nov 03 '16 at 04:45
  • WEBjuju provided an answer that solved the issue, but thank you for your efforts in helping! – ajd2598 Nov 03 '16 at 04:46

1 Answers1

0

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?

Community
  • 1
  • 1
WEBjuju
  • 5,797
  • 4
  • 27
  • 36
  • This fixed my problem. Thank you. May I ask, why the use of a backtick, immediately followed by a single quote mark and viceversa at the end around both those strings? – ajd2598 Nov 03 '16 at 04:45
  • backtick is the way to tell mysql it's a column name (in case someone named a column with a reserved keyword). your string ($sql) is single quoted, so i ended the string after the backtick, then i used the dot (.) concatenator to continue the string using the variable, and then etc. – WEBjuju Nov 03 '16 at 04:46
  • Makes perfect sense, thank you again. I tried to rate up your answer, but apparently I don't have enough reputation yet. You have my thanks though. – ajd2598 Nov 03 '16 at 04:48
  • thank you, best of luck. btw, you want to be sure that the $landtype is white listed (use a switch statement or in_array() to make sure you're not feeding in bad stuff from the end user ;) – WEBjuju Nov 03 '16 at 04:49