Jump to content

Category Structure


dfowler

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 }
					}
				}
			}
		}
	}
} ?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.