0

I am trying to insert into a mySql table, the tables name is the result of a sql select query, the first query returns the correct result but the second query is where the error seems to lie, any help would be very very much appreciated

$query = mysql_query("SELECT council from users where username = '$username'");

 $x = mysql_result($query,0, "council");
 $councilArea = (string)$x;


// mysql inserting a new row
    $result = mysql_query("INSERT INTO '$councilArea' ('barcode', 'productname', 'bin', 'info', 'addedby') VALUES('$barcode', '$productname', '$bin', '$info', '$username')");
user2154825
  • 3
  • 1
  • 1
    The error results from single-quoting `'$councilArea'` and all of the column names like `'barcode'` See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks. Table & column names should not be quoted (unless they are reserved words, requiring backticks) – Michael Berkowski Nov 23 '13 at 18:19
  • 1
    But this also points to a larger problem with your database design. Rather than storing the _name_ of a table holding related information, you ought to be storing a column in a single table which is a foreign key. In other words, your design implies that you have several similar tables when you should have one table with a differentiating column against which you can perform join queries. – Michael Berkowski Nov 23 '13 at 18:20
  • this is part of a JSON response, it proceeds with an if statment checking if the result is populated, if not, return unsuccessful, it returns unsuccessful each time and nothing populated in the database. – user2154825 Nov 23 '13 at 18:20
  • Finally, review [how can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Your use of the deprecated `mysql_*()` extension is potentially unsafe. – Michael Berkowski Nov 23 '13 at 18:21
  • @Michael Sorry, I wrote in the answer, what you put in the comment, but I did not copy on your comments, – Lorenz Meyer Nov 23 '13 at 18:28
  • Thanks Michael, problem was quotes. I thought I tried each combination. As for the design, it is just a prototype representing other databases and just put them into tables. Thanks. – user2154825 Nov 23 '13 at 18:32

1 Answers1

0

You get the error because a table name must not be quoted by single quotes, but by backticks.

There is also a fundamental problem with your database design. You save data in different tables, that really should be stored in one table. Add a field counselArea in the table to distinguish the data, instead of storing it in different tables.

Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121