[SOLVED] Php navigation menu HELP!

[serbestgezer]

Hi Guys,

 

I am trying to make Php navigation but I can't go any further.

 

Mysql Database

 

category

catid

catname

 

subcategory

subid

catid

subname

 

-------------------------------------

category

catid    catname

1        Cars

2        Bikes

 

subcategory

subid    catid  subname

1        1        Toyota

2        1        Honda

3        1        Hyundai

4        2        Kawasaki

5        2        Yamaha

6        2        Suzuki

 

-------------------------------------

 

<ul><li><a href="#">$catname</a></li>

<ul><li><a href="#">$subname</a><li></ul>

</ul>

 

 

How can I get result as you see below from database

 

-------------------------------------

<ul>

 

<li><a href="#">Cars</a></li>

<ul>

<li><a href="#">Toyota</a><li>

<li><a href="#">Honda</a><li>

<li><a href="#">Hyundai</a><li>

</ul>

 

<li><a href="#">Bikes</a></li>

<ul>

<li><a href="#">Kawasaki</a><li>

<li><a href="#">Yamaha</a><li>

<li><a href="#">Suzuki</a><li>

</ul>

 

</ul>

 

-------------------------------------

 

 

[mikesta707]

ok so I assume that the cars in your subcategory table have a catid thats the same as cars. Ok so first we do a simple query

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'");//This will get the cars catid for use in gettings cars out of the second data base

$row = mysql_fetch_assoc($sql);
$id = $row['catid'];

 

Ok now we have the catid of the Cars entry in the table category. We want to use this catid to get all the cars in the other table named subcategory. So we do the following

$sql2 = mysql_query("SELECT * FROM subcategory WHERE catid='$id'");
$row2 = mysql_fetch_assoc($sql2);
$name = $row2['subname'];

 

Ok cool so now we can get the first entry in the subcategory table that has the same catid as the Car entry. So now we want to loop through the entire table, and get all the categories, and subcategories in those categories. Well consider the following code

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'");

while ($row = mysql_fetch_assoc($sql)){//this will loop through all entries in mysql result
$id = $row['catid'];
$name = $row['catname'];
echo "<li>$name</li>";
//now lets access the subcategory table
$sql2 = mysql_query("SELECT * FROM subcategory WHERE catid='$id'");
//and we do another loop to go through all the results from the above query
while ($row2 = mysql_fetch_assoc($sql2)){
$name2 = $row2['subname'];
echo "<li>$name2</li>";
}//end subcategory loop
}//end category loop

 

There that should do what you want it to. Hope that helps!

 

 

[serbestgezer]

Thanks for quick reply mate, you are a champion!

 

I am getting an error message

 

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/index.php on line 6

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/index.php on line 10

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /Library/WebServer/Documents/index.php on line 15

 

 

 

<?

mysql_connect('127.0.0.1', 'test', 'test') or die('could not connect to db');

mysql_select_db('cars');

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'");//This will get the cars catid for use in gettings cars out of the second data base

 

$row = mysql_fetch_assoc($sql);

$id = $row['catid']; // error line

 

$sql2 = mysql_query("SELECT * FROM subcategory WHERE catid='$id'");

$row2 = mysql_fetch_assoc($sql2); // error line

$name = $row2['subname'];

 

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'");

 

while ($row = mysql_fetch_assoc($sql)){//this will loop through all entries in mysql result // error line

$id = $row['catid'];

$name = $row['catname'];

echo "<li>$name</li>";

//now lets access the subcategory table

$sql2 = mysql_query("SELECT * FROM subcategory WHERE catid='$id'");

//and we do another loop to go through all the results from the above query

while ($row2 = mysql_fetch_assoc($sql2)){

$name2 = $row2['subname'];

echo "<li>$name2</li>";

}//end subcategory loop

}//end category loop

 

?>

 

[mikesta707]

hmm that usually means that the SQL syntax is wrong, resulting in the. Try changing

 

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'");//This will get the cars catid for use in gettings cars out of the second

to

$sql = mysql_query("SELECT * FROM category WHERE catname='Cars'");//This will get the cars catid for use in gettings cars out of the second

 

also, at the end of every query write

 

or die('ERROR'.mysql_error());

 

that will give you what mysql errors are happening

[serbestgezer]

ok I found the problem. I named the tables uppercase in database.

 

now it prints like that

 

<li></li><li>Toyota</li><li>Honda</li><li>Hyundai</li>

 

I dont get category name and bikes subcategories

[mikesta707]

ok I found the problem. I named the tables uppercase in database.

 

now it prints like that

 

<li></li><li>Toyota</li><li>Honda</li><li>Hyundai</li>

 

I dont get category name and bikes subcategories

 

Ok well if it is printing the cars, that means it got the Car category Id number correctly. That means that out of the following code

while ($row = mysql_fetch_assoc($sql)){//this will loop through all entries in mysql result
$id = $row['catid'];//this is right
$name = $row['catname'];//this is wrong
echo "<li>$name</li>";

make sure that $name is being set to the right value. Check to make sure catname is the exact column name.

 

now for the bikes, as long as it is in the table, it should loop through it. Try adding the following

echo "We have entered the main loop";

after the line with the first while() on it. that should echo two times. If it doesnt then there is a problem with the SQL

[serbestgezer]

I changed the query

 

$sql = mysql_query("SELECT catid FROM category WHERE catname='Cars'") or die('ERROR'.mysql_error());

 

with

 

$sql = mysql_query("SELECT catid, catname FROM category WHERE catname='Cars'") or die('ERROR'.mysql_error());

 

now I am getting the category name also I added echo "We have entered the main loop"; after first while. It doesnt print two times. it seems like doesnt looping

[mikesta707]

oh jeez im sorry.  My mistake I see what happend

change

$sql = mysql_query("SELECT catid, catname FROM category WHERE catname='Cars'") or die('ERROR'.mysql_error());

to

$sql = mysql_query("SELECT catid, catname FROM category) or die('ERROR'.mysql_error());

 

The WHERE is what was messing us up. Instead of choosing everything from the database, it chose everything with the catname Cars, so it would never get the entry with the catname as Bikes.

 

alternatively, instead of

$sql = mysql_query("SELECT catid, catname FROM category) or die('ERROR'.mysql_error());

you can write

$sql = mysql_query("SELECT * FROM category) or die('ERROR'.mysql_error());

[serbestgezer]

thanks a lot mate. its working great!