MySQL SELECT Error

Question

I am beginner with PHP. I faced that problem when I start my first SQL queries:

Database query On Pages failed : 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 '['id']}' at line 1

There is my SELECT command :

$page_set = mysql_query(
    "SELECT * FROM pages WHERE subject_id  = {[$subject'id']}", $connection
);

I checked all my database tables and everything is correct. This query works :

$page_set = mysql_query("SELECT * FROM pages" ,$connection)

Answer 1

Try something like this:

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = '" . $subject['id'] . "'", $connection);

This assumes that id is an item in the $subject array which I am guessing is what you are going for here. I dont think you need those curly braces "{" but you may need single quotes around the value in the where clause.

Also, if this doesnt work, try echoing out the sql command that is being run and you should be able to see the issue (or post the SQL command in your question as well)

Answer 2

try this,

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = '" . $subject['id'] . "'", $connection);

be warned that this code is vulnerable with SQL Injection, please read the article below to learn how to prevent from it,

• How can I prevent SQL injection in PHP?

Answer 3

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject['id']}", $connection);

Answer 4

All of the above can work, but the cleanest method I prefer myself is either:

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = '".$subject['id']."'", $connection);

OR my recommendation for you, as a beginner:

$subject_id = $subject['id'];
$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = '$subject_id'", $connection);

I suggest that you use the second method I wrote. It's the best and cleanest practice.

Note: Please beware that the mysql_ extention is officially depreciated. You should either use mysqli or PDO. (http://nl3.php.net/manual/en/function.mysql-query.php)

Answer 5

That is not your SELECT command. That is a PHP mysql_query command. First create the SQL, then query the database with it:

$sql = "SELECT * FROM pages WHERE subject_id  = {[$subject'id']}";
$page_set = mysql_query($sql, $connection);

If you then run into the error, you can just output the $sql and check how it looks like. Compare with the error message and fix your building of the SQL query.

If you're looking for some inspiration how the output of the variable could been done, see an existing answer.

Things will get more easy for you if you use PDO instead of the deprecated (long-time outdated, not supported any longer) mysql_* functions. Especially as parametrized queries will help you building the SQL command, something that the mysql_* functions do not even support!

For a tutorial see: PDO Tutorial for MySQL Developers

Answer 6

Going forward, construct your query string in a separate variable like this:

$sql = "SELECT * FROM pages WHERE subject_id  = {[$subject'id']}";

Then run the query like this:

$res = mysql_query($sql);

If you do that you can see the fully resolved query string with something like this:

if (!$res) die("FAIL: $sql BECAUSE: " . mysql_error());

You must test the results value from mysql_query() -- it is not a black box; it can and will fail for reasons outside of your control. When the failure occurs, you want to recognize it and handle the error conditions.

Siedbar note: the mysql extension is now deprecated by PHP, so you might want to choose PDO or MySQLi for your current / future work.

And if you're new to PHP, this is a really great book to help you get started: http://www.sitepoint.com/books/phpmysql5/