Category Structure

[dfowler]

Hey guys, I have a minor aesthetics problem that is driving me nuts. I have a table in a database that is structured as follows:

categories

id

name

description

parent_id

date

 

I have a dropdown that is pulling all these value dynamically, and here is my problem. Some of the categories are sub-categories, and I would like for them to display after their parent category. However, right now it just pulls everything based on it's id in  the database. How do I display the list as I would like it?

 

Example I want ([id:1, parent_id:0]Cars->[id:5, parent_id:1]Honda) to show up on the list as:

Cars

-Honda

 

instead of:

Cars

something

something

something

-Honda

[fenway]

How are you retrieving these to put "something" in between?

[dfowler]

Ok, I'll try to add a little more detail.  Say these are the first 5 records in the database:

 

IDNameParent_ID

1Cars0

2Trucks0

3Honda1

4SUVs0

5Dodge2

I am populating the dropdown like this currently:

<?php
include 'system.php';
$query1 = "SELECT * FROM categories";
$result1 = mysql_query($query1);
$cats = array();
while (($row = mysql_fetch_assoc($result1)) !== false) {
  $cats[] = $row;
}
?>
Category: <select name="cat">
<option value="0"></option>
<?php foreach($cats as $c) { ?>
	<option value="<?php echo $c['id']; ?>"><?php echo $c['name']; ?></option>
<?php } ?>
</select>

So the dropdown looks like this:

Cars

Trucks

Honda

SUVs

Dodge

 

I want it to look like this:

Cars

-Honda

Trucks

-Dodge

SUVs

 

Basically the subcategory falls under it's parent, I hope this clears things up a little more.

[fenway]

How many subcategory level do you have?

[dfowler]

There will unlimited subcategory ability.  So using the car example you could have:

 

Cars

-Honda

  -Accord

  -2 Door

    -V6

    -Red

      -etc.

 

It all depends on the user (I created an admin that allowed the creation of the categories).

[fenway]

Then you need to store this info as a hierarchy... the way you've done it, with a "parentID", you can't write a query that will return a "unlimited" subcategory list -- you need to know the depth in advance, which is impossible.  The adjacency list model might work.

[kingnutter]

Fenway is right that you need a hierarchical directory structure. From my research this isn't something that MySql is very good at.

 

I'm not sure it's impossible however. Have a look here:

 

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

I'm a bit concerned that this would slow down pretty quickly. And I also worry that for a busy site, if Category IDs are changing during users open sessions that chaos could ensue.

[kingnutter]

...the adjacency list model is there, but read down to the Nested Set model which may solve your problem.

[dfowler]

I see what you mean, I could always just list for a high number of rows.  I believe even though I allowed for unlimited subcategories, they probably won't drop below 4 levels.

[fenway]

I see what you mean, I could always just list for a high number of rows.  I believe even though I allowed for unlimited subcategories, they probably won't drop below 4 levels.

Then you could simply self-join 4 times... that's what I tend to do.

[dfowler]

Thanks for yall's help so far.  Here is what I tried using but it isn't working properly:

 

$sql="SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent_id = t1.id
LEFT JOIN categories AS t3 ON t3.parent_id = t2.id
LEFT JOIN categories AS t4 ON t4.parent_id = t3.id
WHERE t1.parent_id = 0";
$result1 = mysql_query($sql);
$cats = array();
while (($row = mysql_fetch_assoc($result1)) !== false) {
  $cats[] = $row;
}
?>
	<form name="form1">
		<select name="categories" ONCHANGE="location=this.options[this.selectedIndex].value;">
			<option selected>---- Choose a Category to Browse ----</option>
			<?php foreach($cats as $c) { ?>
			<option value="http://www.site.com/index.php?id=<?php echo $c['id']; ?>"><?php echo $c['name']; ?></option>
			<?php } ?>
		</select>
	</form>

 

I know I'm not pulling the id in the sql statement, but I'm not sure how to do this correctly.  Once again, thanks for all of you guys help so far!

[fenway]

Well yes, you should be getting both name and id... you simply need to order your result set appropriately.

[dfowler]

I feel really dumb, but I ended up going around the problem.  Here is what I did:

<?php $sql="SELECT * FROM categories WHERE parent_id=0";
$result1 = mysql_query($sql);
$cats = array();
while (($row = mysql_fetch_assoc($result1)) !== false) {
  $cats[] = $row;
} ?>
<select name="category">
<option selected>---- Choose a Parent Category ----</option>
<?php foreach($cats as $c) { ?>
	<option value="<?php echo $c['id']; ?>"><?php echo $c['name']; ?></option>
	<?php $sql2 = "SELECT * FROM categories WHERE parent_id='".$c['id']."'";
	$result2 = mysql_query($sql2);
	$sub_cats = array();
		while (($row2 = mysql_fetch_assoc($result2)) !== false) {
			$sub_cats[] = $row2;
		}
	if($sub_cats) {
		foreach($sub_cats as $sub) { ?>
			<option value="<?php echo $sub['id']; ?>">  -<?php echo $sub['name']; ?></option>
			<?php $sql3 = "SELECT * FROM categories WHERE parent_id='".$sub['id']."'";
			$result3 = mysql_query($sql3);
			$sub_cat2 = array();
			while (($row3 = mysql_fetch_assoc($result2)) !== false) {
				$sub_cat2[] = $row3;
			}
			if($sub_cat2) {
				foreach($sub_cat2 as $sub2) { ?>
					<option value="<?php echo $sub2['id']; ?>">    -<?php echo $sub2['name']; ?></option>
					<?php $sql4 = "SELECT * FROM categories WHERE parent_id='".$sub2['id']."'";
					$result4 = mysql_query($sql4);
					$sub_cat3 = array();
					while (($row4 = mysql_fetch_assoc($result4)) !== false) {
						$sub_cat3[] = $row4;
					}
					if($sub_cat3) {
						foreach($sub_cat3 as $sub3) { ?>
							<option value="<?php echo $sub3['id']; ?>">      -<?php echo $sub3['name']; ?></option>
						<?php }
					}
				}
			}
		}
	}
} ?>

[fenway]

Well, it's less than optimal, but it if works, go with it temporarily