Category & Sub Categories

$result = mysql_query("SELECT * FROM sc_phpscart_category WHERE parent='0' ORDER BY category ASC");
$count=0;
if(mysql_num_rows($result))
{
echo "<strong>Sub-Category of: <select name='parent' style='background-color: transparent'>\n";
echo "<option value='".$returnID."' selected>".$info->getCatName($returnID)."</option>\n";
echo "<option value='0'>-- Top Level --</option>\n";
while($row=mysql_fetch_array($result))
{
$count=$count+1;
echo "<option value='".$row["id"]."'>".$row["category"]."</option>\n";

$result2 = mysql_query("SELECT * FROM sc_phpscart_category WHERE parent='".$row["id"]."' ORDER BY category ASC");
while($row2=mysql_fetch_array($result2))
{
//$count=$count+1;
echo "<option value='".$row2["id"]."'>|&nbsp;&nbsp;&nbsp;- ".$row2["category"]."</option>\n";
$result3 = mysql_query("SELECT * FROM sc_phpscart_category WHERE parent='".$row2["id"]."' ORDER BY category ASC");
while($row3=mysql_fetch_array($result3))
{
//$count=$count+1;
echo "<option value='".$row3["id"]."'>&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;- ".$row3["category"]."</option>\n";
}
}

}
echo '</select>';
}
else
{
echo '<div align="center"><font size="'.$font_size.'" face="'.$font_face.'">'.$error["nogigs"].'</font></div>';
}

Alright... to help with the last code I was doing I have this code which takes makes a drop list of categories and sub categories. However it is only limited to 3 sub categories and uses many mysql_queries. For example:

Motorcycles
| Harley-Davidson
| Parts
| Leathers
| T-Shirts
| Clothes
| Honda
| Parts
| Lights
| Yamaha

I would like to be able to make a list like that (dropdown) without using too many mysql_queries and that isn't limited to only 3 sub categories so I may have many sub-categories. How would I go about doing this more efficently than the way I am doing.

i recon you would need a recursive function something like this,.

<?php
function display_children($parent, $level) {
$result = mysql_query("SELECT * FROM sc_phpscart_category WHERE parent='".$parent."' ORDER BY category ASC");
while ($row = mysql_fetch_array($result)) {
echo "<option value='".$row["id"]."'>";

for($i = 0 ; $i < $level; $i++)
{
echo "|&nbsp;&nbsp;&nbsp;";
}

echo $row["category"]."</option>\n";
display_children($row['title'], $level+1);
}
}

$result = mysql_query("SELECT * FROM sc_phpscart_category WHERE parent='0' ORDER BY category ASC");
if(mysql_num_rows($result))
{
echo "<strong>Sub-Category of: <select name='parent' style='background-color: transparent'>\n";
echo "<option value='".$returnID."' selected>".$info->getCatName($returnID)."</option>\n";
echo "<option value='0'>-- Top Level --</option>\n";

while($row=mysql_fetch_array($result))
{
display_children($row["id"],0);
}
echo '</select>';
}
?>



please note i can't say for sure that code works. but thats the basic idea

Well, the code doesn't work... seems to have an endless loop as it doesn't stop running, then finally times out. It gives me a long list like this:


-- Top List --
Bombardier
| ATV's
| | ATV's
| | | ATV's
| | | | ATV's
| | | | | ATV's
| | | | | | ATV's
| | | | | | | ATV's
| | | | | | | | ATV's
| | | | | | | | | ATV's

Mind ya, there are alot more rows of ATV's than that....

Yea, nevermind... I got it working.

Looping a query to your database inside another database query is very intensive. Best to query your database in a single shot and then use php to determine your layout. check the link in my signature if you want to see an example. it is easily expandable by changing the database query and adding another check on the subcategories.

Well,
this is actually working rather well and doesn't take too long to load even with alot of categories. However, I am using it to allow users to edit a category.

For example: A users goes to a certain category or sub category then can modify the title of the category and which category it is located under. However, the current set up allows you to make the category a sub category of one of it's sub categories. Which would then provide an endless loops of category selection when visiting the website.

I hope that made sense...
In a more visual approach:

Before
------
Cars
Chevy
Ford
Motorcycles
Harley-Davidson
Clothes
Leathers
Parts
Honda
Clothes

After
-----
Cars
Chevy
Ford
Motorcycles
----------
Clothes
Harley-Davidson
Leathers
Parts
Honda
Clothes

And since Clothes originally points to "Harley-Davidson" and Harley-Davidson isn't a parent of the root (nothing) then you can't change it. How can I get the list to not produce the children of the category you are currently in/editing?

*Da Bump*:confused:

Use MPTT (http://www.codingforums.com/showthread.php?t=79773) or some other method of storing hierarchical data.

check the link in my signature if you want to see an example.
What happened to those links you had listed in the past?