6 Replies - 571 Views - Last Post: 05 January 2013 - 06:13 PM Rate Topic: -----

#1 tim9009  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 01-October 10

SQL query failing.

Posted 04 January 2013 - 09:47 AM

Hello

I have a problem with a SQL query for my web-based game. Could you guys please have a look at it and help me a bit with how to structure this query?

Here is my query code:
//This would be written further up in the code inside a bunch of if-statements
$sign = "X";

// This is the main query part.
$query = $db->prepare("UPDATE game SET `:square`=:sign WHERE player1 = :player1 OR player2 = :player2");
$query->bindParam(":player1", $_SESSION['username']);
$query->bindParam(":player2", $_SESSION['username']);
$query->bindParam(":sign", $sign);
$query->bindParam(":square", $_POST['square']);
                
$query->execute();
$result = $query->fetch( PDO::FETCH_ASSOC );



The $_POST['square'] does here hold the value 13, which is the name of a collum in the database.

Upon executing the code a error is returned.
"Error! 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 ''13'='X' WHERE player1 = 'Tim' OR player2 = 'Tim'' at line 1"

What am I doing wrong, and how can I change it?

Thank you!
-Tim

This post has been edited by tim9009: 04 January 2013 - 09:48 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL query failing.

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,174
  • Joined: 08-June 10

Re: SQL query failing.

Posted 04 January 2013 - 10:26 AM

View Posttim9009, on 04 January 2013 - 05:47 PM, said:

What am I doing wrong, and how can I change it?

table and column names cannot be replaced by placeholders. they must be included verbatim so that the SQL engine can prepare the SQL execution plan (for which you need the table and column names, but not the data).
Was This Post Helpful? 3
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: SQL query failing.

Posted 04 January 2013 - 11:12 AM

View Posttim9009, on 04 January 2013 - 04:47 PM, said:

The $_POST['square'] does here hold the value 13, which is the name of a collum in the database.

This suggests a serious flaw in your database design. Both that you are having to use a variable as the column name, and that this column name is a number, would indicate that you are violating the first normal form. (The first "rule" of relational database design.)

If you show us your complete database design, we could point out ways to fix that. Judging by that single query you posted, I would think that you'd need at least four tables to accomplish what you are trying to do there. One table for the games, one for a list of users, one to link those two, and a fourth to store the various sign values for each game.
Was This Post Helpful? 0
  • +
  • -

#4 tim9009  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 01-October 10

Re: SQL query failing.

Posted 04 January 2013 - 08:20 PM

Thank you for your answers!

View PostAtli, on 04 January 2013 - 06:12 PM, said:

View Posttim9009, on 04 January 2013 - 04:47 PM, said:

The $_POST['square'] does here hold the value 13, which is the name of a collum in the database.

This suggests a serious flaw in your database design. Both that you are having to use a variable as the column name, and that this column name is a number, would indicate that you are violating the first normal form. (The first "rule" of relational database design.)

If you show us your complete database design, we could point out ways to fix that. Judging by that single query you posted, I would think that you'd need at least four tables to accomplish what you are trying to do there. One table for the games, one for a list of users, one to link those two, and a fourth to store the various sign values for each game.


The reason that I tried to use the column name as a variable was to minimize the amount og code I had to write. I could simply just make a if-statement with 9 different outcomes. The reason as for why I am using numbers as column names is because they represent x and y values for a 3x3 grid. Right now I have 2 tables, one for the game and one for the user data. I don't think I would need any more tables than that. Any way, thank you all for the help. I will try to fix the query now and come back to you if I run in to any more problems along the way.
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,174
  • Joined: 08-June 10

Re: SQL query failing.

Posted 05 January 2013 - 05:15 AM

that sounds like you want to code a game of TicTacToe. check out this and this tutorial, they might get you some ideas.

Quote

The reason as for why I am using numbers as column names is because they represent x and y values for a 3x3 grid

actually, that can be solved differently (and IMO in accordance to the 1NF)
-- something along that
CREATE TABLE game_history
(
  game INT,
  player INT,
  row ENUM('1', '2', '3'),
  col ENUM('a', 'b', 'c'),
  move_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (player) REFERENCES player_list (player_id),
  FOREIGN KEY (game) REFERENCES game_list (game_id)
)

that table stores which player set himself on a certain field (identified by row and col) in a certain game.

This post has been edited by Dormilich: 05 January 2013 - 05:31 AM

Was This Post Helpful? 0
  • +
  • -

#6 tim9009  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 73
  • Joined: 01-October 10

Re: SQL query failing.

Posted 05 January 2013 - 02:06 PM

View PostDormilich, on 05 January 2013 - 12:15 PM, said:

that sounds like you want to code a game of TicTacToe.


Yes that is what I am making, and I am 95% finished so I won't be changing much for now, but thank you a lot for the links any way. Allways good to learn other ways to write the code.

-Tim
Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,174
  • Joined: 08-June 10

Re: SQL query failing.

Posted 05 January 2013 - 06:13 PM

View Posttim9009, on 05 January 2013 - 10:06 PM, said:

and I am 95% finished so I won't be changing much for now

the problem I see is the database, you can screw that up and it has an immense effect on your whole application. but since the information you get off the DB is limited, a change in DB design does not need to cause a big change in the PHP code (thanks to VIEWs). always remember that a (RDBMS-type) DB like MySQL is mainly about relation between the data, not just the data itself (in short: MySQL ≠ Excel).
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1