Category/Sub-Category

[valandor062]

Hello,

I am trying to create a categories with sub-categories for a quick navigation bar. I am new to php and mysql and I am having a lot of trouble getting the query to run properly for me.

I have a connection to my database, and it run the initial query to retrieve the categories but when I ask it to receive the sub-categories and place them under the proper category I get an error.

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

To debug the issue I added

 $rs = mysql_query($page_set) or die("Problem with the query: $q at line " . __LINE__ . '<br>' . mysql_error()); 

This came up the an error: Problem with the query: at line 39
Query was empty

Here is the bit of coding I"m having trouble with:

<?php
		$subject_set=mysql_query("select * from subjects", $connect);
			if(!$subject_set) {
				die ("Database Query Failed:" . mysql_error());
							}

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

	$page_set= mysql_query ("SELECT * FROM pages where subject_id = " .$row['id'] , $connect);

		$rs = mysql_query($page_set) or die("Problem with the query: $q at line " . __LINE__ . '<br>' . mysql_error());
		if (!$page_set) {
			die("Database query failed: " . mysql_error());

		}

		?>

[anakadote]

You're referencing a variable, $row['id'], that does not exist. You also need to query the subcategories within the loop that returns the categories:

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

$page_set= mysql_query ("SELECT * FROM `pages` where `subject_id` = " .$page['id'] , $connect);
} 

Notice the variable is '$page' in the current context, not '$row'.

[valandor062]

This is my current code.... I have fixed the above issues and still get the same results.

<?php

echo "<ul>";

$category_set = mysql_query( "Select * FROM subjects", $connect);
	if(!$category_set) 
		{
			die( "Data Base Query Failed: " . mysql_error());
		}

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

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}", $connect);
	if(!$page_set) 
		{
			die("Database Query Failed: " . mysql_error());
		}

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

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

		echo "</ul>";
?>

[anakadote]

You still need to put the second query inside of the first while loop:

<?php 

echo "<ul>"; 

$category_set = mysql_query( "Select * FROM subjects", $connect); 
if(!$category_set) { 
    die( "Data Base Query Failed: " . mysql_error()); 
} 

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

    $page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}", $connect); 
    if(!$page_set) { 
        die("Database Query Failed: " . mysql_error()); 
    } 

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

echo "</ul>"; 
?>

[valandor062]

Thank you! I see what I was doing wrong and understand now why it didn't work! I appreciate the help and hope this will be a good learning experience for me 🙂