database query failed

[shoykay]

Please, I will like anyone who can help me on this query

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

it resulted in the following error when viewed on the browser

"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 '' at line 1"

After putting a single quote around '{$subject}' i.e

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

the error disappears but no value was displayed

Please what can i do to this query . Thank you

[lyrico]

Try to put the $subject into a variable. For example, $id=$subject;
then make a query like this:
$page_set=mysql_query("SELECT * FROM pages WHERE subject_id='".$id."'");
just pay attention (') and (").

[NettSite]

Your SQL query is fine, but obviously the value of $subject["id"] does not match any value for subject_id in your table. Look at the code before the selct and ensure that you are loading the correct values into the $subject array.

[shoykay]

Thank you for response.. i put $id=$subject; and use the variable $id but it still do not return any value. I also check both tables in the database and they contain values that match.. please what else can i do. thank you

[dietdew12z]

Try this:

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

[lyrico]

Can you show us your code?

[shoykay]

Thank you, this is my code

<?php 
	$subject_set=mysql_query("select * from subjects", $connection);
	confirm_query($subject_set);
	while ($subject = mysql_fetch_array($subject_set)) {
	   echo "<li><a href=\"content.php?subj=" . urlencode($subject["id"]) ."\">{$subject["menu_name"]}</a></li>";}
	
        $id=$subject['id'];
	$page_set=mysql_query("SELECT *  FROM pages WHERE subject_id = '".$id."'", $connection);
	confirm_query($page_set);			
	
        echo "<ul class=\"pages\">";
	while ($page = mysql_fetch_array($page_set)) {
	echo "<li><a href=\"content.php?page=" . urlencode($page["id"]) ."\">{$page["menu_name"]}</a></li>";}
	echo "</ul>";
				 
				
?>

[shoykay]

Thank you, this is my code

<?php 
	$subject_set=mysql_query("select * from subjects", $connection);
	confirm_query($subject_set);
	while ($subject = mysql_fetch_array($subject_set)) {
	   echo "<li><a href=\"content.php?subj=" . urlencode($subject["id"]) ."\">{$subject["menu_name"]}</a></li>";}
	
        $id=$subject['id'];
	$page_set=mysql_query("SELECT *  FROM pages WHERE subject_id = '".$id."'", $connection);
	confirm_query($page_set);			
	
        echo "<ul class=\"pages\">";
	while ($page = mysql_fetch_array($page_set)) {
	echo "<li><a href=\"content.php?page=" . urlencode($page["id"]) ."\">{$page["menu_name"]}</a></li>";}
	echo "</ul>";
				 
				
?>

[Mckcvision]

Sorry have you include("db.php") to connect to your database?

[lyrico]

I'm not so sure but try this:

<?php

    error_reporting(0);
    session_start();

    $connection = Mysql_connect('localhost','root','password');
    mysql_select_db('name_of_database');

    $subject_set=mysql_query("select * from subjects");
    while($subject = mysql_fetch_array($subject_set))
    {
       $id=$subject['id'];
    }
    $page_set=mysql_query("SELECT *  FROM pages WHERE subject_id = '".$id."'");         

        echo "<ul class='pages'>";
    while ($page = mysql_fetch_array($page_set))
        {
       echo "<li><a href='yourpage.php?id=$page[id]'>".$page['menuname']."</a>"."</li>";
         }
    echo "</ul>";
?>

[shoykay]

thank you all, I connected to the database before running the query,
the first query
$subject_set=mysql_query("select * from subjects", $connection);
return values
but the other with the WHERE clause return no value.
$page_set=mysql_query("SELECT * FROM pages WHERE subject_id = '".$id."'", $connection);
I tried using the WHERE clause somewhere else and it did not return value there too while other query worked well.

[lyrico]

<?php 
    $subject_set=mysql_query("select * from subjects", $connection);
    confirm_query($subject_set);
    while ($subject = mysql_fetch_array($subject_set)) {
       echo "<li><a href=\"content.php?subj=" . urlencode($subject["id"]) ."\">{$subject["menu_name"]}</a></li>";

        $id=$subject['id'];} // read below note
    $page_set=mysql_query("SELECT *  FROM pages WHERE subject_id = '".$id."'", $connection);
    confirm_query($page_set);           

        echo "<ul class=\"pages\">";
    while ($page = mysql_fetch_array($page_set)) {
    echo "<li><a href=\"content.php?page=" . urlencode($page["id"]) ."\">{$page["menu_name"]}</a></li>";}
    echo "</ul>";


?>

note: I modified you code and include the $id=$subject['id']; inside the while loop in your first query. Try it.

[Mckcvision]

I think the problem is because of the single quotation and double quotation.

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

:)
Your variable $id should be careful of the quotation issue too.