dfowler Posted April 8, 2008 Share Posted April 8, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted April 8, 2008 Share Posted April 8, 2008 How are you retrieving these to put "something" in between? Quote Link to comment Share on other sites More sharing options...
dfowler Posted April 8, 2008 Author Share Posted April 8, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 8, 2008 Share Posted April 8, 2008 How many subcategory level do you have? Quote Link to comment Share on other sites More sharing options...
dfowler Posted April 9, 2008 Author Share Posted April 9, 2008 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). Quote Link to comment Share on other sites More sharing options...
fenway Posted April 9, 2008 Share Posted April 9, 2008 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. Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 9, 2008 Share Posted April 9, 2008 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. Quote Link to comment Share on other sites More sharing options...
kingnutter Posted April 9, 2008 Share Posted April 9, 2008 ...the adjacency list model is there, but read down to the Nested Set model which may solve your problem. Quote Link to comment Share on other sites More sharing options...
dfowler Posted April 9, 2008 Author Share Posted April 9, 2008 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 9, 2008 Share Posted April 9, 2008 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. Quote Link to comment Share on other sites More sharing options...
dfowler Posted April 9, 2008 Author Share Posted April 9, 2008 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! Quote Link to comment Share on other sites More sharing options...
fenway Posted April 9, 2008 Share Posted April 9, 2008 Well yes, you should be getting both name and id... you simply need to order your result set appropriately. Quote Link to comment Share on other sites More sharing options...
dfowler Posted April 10, 2008 Author Share Posted April 10, 2008 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 } } } } } } } ?> Quote Link to comment Share on other sites More sharing options...
fenway Posted April 10, 2008 Share Posted April 10, 2008 Well, it's less than optimal, but it if works, go with it temporarily Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.