php query error

[co.ador]

Hi I am new to php and I am facing some issues here

when I am preview the code in the browser the following error come up.

Database query failed: You have an error in your SQL syntax; check the manual that correspond to your MySWL server version for the right syntax to use near " at line 1

I am trying to echo a table call pages, It echos the table subjects but then when it comes to pages it shows up the error the coding is the following:

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection);
if(!$page_set) {
die("Database query failed:". mysql_error()); 

There is an obvious error in the query that causes to die instead of retrieving the data from pages.?

Please help me out.

[Cute Tink]

Are you sure that $subjects["id"] has a value? Try echo or print on $subjects["id"] to see what you get.

[crmalibu]

The error tells you that the sql query you sent to the database is incorrect. You should take a look at the sql query to see what you're sending, so that you can see the problem, which will help you understand what needs to be done to fix it.

echo "SELECT * FROM pages WHERE subject_id={$subjects["id"]}"; 

What do you get?

[co.ador]

the values does exist!

When echo $subjects["id"]; inside the $page_set variable the values of the $subjects [id] don't show up.

example of the $page_set variable goes as fallows

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); if(!$page_set) { die("Database query failed:". mysql_error()); 
}
echo "<ul class=\"pages\">";
while($page = mysql_fetch_array($page_set)) {
echo "<li>{$pages["menu_name"]}</li>";
// if I echo $subjects["id"]; here it won't show up the subjects id's on the browser. 

But when I echo $subjects["id"]; inside the $subject_set the values does shows up.


example of the $subject_set variable which goes as fallows echoing $subjects["id"];

$subject_set = mysql_query("SELECT * FROM subjects", $connection);
if (!$subject_set) {
die("Database query failed: ". mysql_error());
}
while($subjects = mysql_fetch_array($subject_set)) {
echo "<li>{$subjects["menu_name"]}</li>";
// if I echo $subjects["id"]; here as you told me then the id's of the subjects will show up in the firefox's Browser
} 

crmalibu the whole code goes like this:

$subject_set = mysql_query("SELECT * FROM subjects", $connection);
if (!$subject_set) {
die("Database query failed: ". mysql_error());
}
while($subjects = mysql_fetch_array($subject_set)) {
echo "<li>{$subjects["menu_name"]}</li>";
}
$page_set = mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); if(!$page_set) { die("Database query failed:". mysql_error()); 
}
echo "<ul class=\"pages\">";
while($page = mysql_fetch_array($page_set)) {
echo "<li>{$pages["menu_name"]}</li>";
} 

[Cute Tink]

In this particular case, every time you loop through the $subject_set, $subjects gets a new set of values, 1 set for each row of results. It sounds like once the loop is done, your $subjects probably no longer has a value.

If you are looking for a specific "subject" to call more information on, then you should have a separate variable set to that value. If you need "pages" for all of your subjects, then you'll need to have an array and modify your query accordingly.

[crmalibu]

Cute Tink is right. a while loop will execute as long as the condition is considered true.

Lets say your SELECT * FROM subjects query resulted in 2 rows.

while($subjects = mysql_fetch_array($subject_set)) { 
echo "<li>{$subjects["menu_name"]}</li>"; 
} 

Internally, this is what php is doing

// first row
$subjects = mysql_fetch_array($subject_set);
if ($subjects) {
    echo "<li>{$subjects["menu_name"]}</li>";
}

// second row
$subjects = mysql_fetch_array($subject_set);
if ($subjects) {
    echo "<li>{$subjects["menu_name"]}</li>";
}

// this time, there is no third row, so mysql_fetch_array() returns false
$subjects = mysql_fetch_array($subject_set);
// this if statement is false. this is where the loop stops, 
// but $subjects now holds the value of false from here on out
if ($subjects) {
    echo "<li>{$subjects["menu_name"]}</li>";
} 

[co.ador]

I kind of undertand what you both are saying

since the value of subjects will be false so it means I have to set up a new query before $page_set right?

I would probably need an array and modify a query accordingly.

Because the results I want to get is getting pages below all of my subjects.

How can I go about putting an array and modify a query accordingly? that would be before page_set?

for further information the database I created have two tables one call subjects and one call pages.

The pages table has three inserts and four fields

id               subject_id                  menu_name            position       visible

1                   1                        History                  1              1
2                   2                        Our Mission              2              1 
3                   3                        Smart Pro                1              1

The subjects table has three inserts and four fields

id             menu_name               position     visible

1               History                  1              1
2               Our Mission              2              1 
3               Smart Pro                1              1

I am trying to get the menu_name of the pages table below the menu_name of the subjects table through the subject_id field and the id field of the subjects table.

[Cute Tink]

Something like this is what I use:

$array = array();

$subject_set = mysql_query("SELECT * FROM subjects", $connection);

if (!$subject_set) {

die("Database query failed: ". mysql_error());

}

while($subjects = mysql_fetch_array($subject_set)) {

$array[] = $subjects["id"];

echo "<li>{$subjects["menu_name"]}</li>";

}

$query = "SELECT * FROM pages WHERE subject_id in (" . implode( ',', $array ) . ")";

$page_set = mysql_query($query,$connection); if(!$page_set) { die("Database query failed:". mysql_error()); 

}

echo "<ul class=\"pages\">";

while($page = mysql_fetch_array($page_set)) {

echo "<li>{$pages["menu_name"]}</li>";

} 

Please note where I added $array notations and I altered your "pages" query

[co.ador]

Hey good guys

I was analizing what you told me about the $subject variable getting a false value after the loop good analises guys:

$subject_set = mysql_query("SELECT * FROM subjects", $connection);
 if (!$subject_set) { 
die("Database query failed: ". mysql_error()); } 
while($subjects = mysql_fetch_array($subject_set)) { 
echo "<li>{$subjects["menu_name"]}</li>"; 
} $page_set = mysql_query("SELECT * FROM pages WHERE subject_id={$subjects["id"]}",$connection); 
if(!$page_set) { 
die("Database query failed:". mysql_error()); } 
echo "<ul class=\"pages\">"; while($page = mysql_fetch_array($page_set)) { echo "<li>{$pages["menu_name"]}</li>"; } 

if you take a look at the Curly Braces before page_set which was causing the value of subjects to return to false again once i put that Curly Brace out it worked. It retrieved the information out of the Database and put the pages belows the subjects thanks to the ul and li tags.

I will try the way you gave me, I will create a query variable and put it together to try the array and query modification you did.


Thank you guys I have started to dive in into php Language I am very happy.

[co.ador]

your implode version works good it display the data, but the only thing is that it is not displaying each page in its respective subject it first display the three subjects and then a loop of the three pages.

instead of subject-page, subject-page, subject-page how can we fix it to fit in this format or the array version.

In the verstion we fixed before the pages were able to show up after each subject because I took out the Curly Brace i was talking about. When i take them out in your version the browser shows up a Parse error on line 40 but line 40 is the last one I don't know why the browser refers to the last line.

[Cute Tink]

You can store the results in an associative array and echo them where you want them. You will have to hold off echo-ing the results of the queries until you have all of the information that you need.

[co.ador]

And how do you place an associative array and hold the echo with the array and modification query you did?

[Cute Tink]

Using the same snippet of code:

$array = $assoc = array();

$subject_set = mysql_query("SELECT * FROM subjects", $connection);

if (!$subject_set) {

die("Database query failed: ". mysql_error());

}

while($subjects = mysql_fetch_array($subject_set)) {

$array[] = $subjects["id"];

$assoc[$subjects['id']] = $subjects['menu_name'];

}

$query = "SELECT * FROM pages WHERE subject_id in (" . implode( ',', $array ) . ")";

$page_set = mysql_query($query,$connection); if(!$page_set) { die("Database query failed:". mysql_error()); 

}

while($page = mysql_fetch_array($page_set)) {

echo "<li>{$assoc[$page["subject_id"]]}</li>
       <ul class=\"pages\">
       <li>{$pages["menu_name"]}</li>
       </ul>";

} 

I didn't test this, so you may want to modify the output to see how things look.