Page 1 of 1
Quering categories
Posted: Sun Jan 28, 2007 8:58 pm
by GeXus
Ok, So i've been racking my brain trying to deal with doing category tree in mysql and php. Maybe it is because im using smarty templates so getting the data to display is different, but this is what I have.
one table setup like this
id name rel
the 'rel' is the id of the parent category, so the top level categories have no rel, and the "sub categories" will have a rel.
How would I go about quering this so that it displays as follows:
Category
sub cat
sub cat
Category 2
sub cat
sub cat
sub cat
etc...
Thanks!
Posted: Sun Jan 28, 2007 9:13 pm
by infolock
You are going to need to redesign your table. What you are wanting to do would require a hellish amount of queries, if statements, and a boat load of headaches.
Best is to keep it simple:
Create a table with your categories:
category Table:
Create a table with your sub-categories:
sub_category table:
Code: Select all
sub_category_id category_id description
Now, you have a master-detail table lookup that can be queried like this:
Code: Select all
Select sub_category.sub_category_id, sub_category.description as sub_cat_desc, category.description as category_desc
FROM sub_category
INNER JOIN category ON category.category_id = sub_category.category_id
WHERE sub_category.category_id = '1'
ORDER BY sub_category.sub_category_id
Edit: Another reason for the re-design is for your naming convension of your fields. You can do it however you want, but when you are going to be using fields that are Primary/Auto Inc fields, it's best to call them xxxxx_id (where xxxxx is the actual name of the table you are creating). Then, when you need to use a field in another table to link them together, you should also name the field in that table whatever it is you set in the first. That way, debugging will be easier, and your inner joins will make more sense from a 10,000 ft glance.
Posted: Sun Jan 28, 2007 9:30 pm
by GeXus
Funny you should say that, because that was my original design and I still have it. Here is the problem with that.
In order for me to loop through and not repeat the category name (since there will typically be more sub categories), I have to get the category name, and check if it is the same, do not print, else print it...
The problem here lies with when i'm trying to display the data in columns.. 3x it never displayes properly because of the way it's looping through that.
One solution I know will work, is if I can somehow throw the sub categories into a multidimentional array that matches up to the categories.. so for example should look like
Code: Select all
array(
array('sub one a', 'sub one b', 'sub one c'),
array( 'sub two a', 'sub two b'),
array( 'sub three c')
);
Then I can match it up and because I am using smarty, use a nested section tag..
That's the only way I can seem to get it to work, if I can somehow get the data into an array like that.. which I haven't had luck doing.
Posted: Sun Jan 28, 2007 10:06 pm
by infolock
Well, maybe my query wasn't exactly the most thought out query in the world then
Why not just run 2 queries?
1)
Code: Select all
Select * from category ORDER BY category_id
2)
Code: Select all
//with $rst being the result from the query above...
$data_array = array();
while($row = mysql_fetch_assoc($rst)) {
$sql = "SELECT DISTINCT(sub_category_id) as sub_category, sub_category.description FROM sub_category WHERE category_id = '{$row['category_id']}'";
$rst2 = mysql_query($sql);
while($sub_row = mysql_fetch_assoc($rst2)) {
$data_array[$row['description']][] = array('id' => $row2['sub_category'], 'description' => $row2['description']);
}
}
echo '<pre>';
print_r($data_array);
echo '</pre>';
You should now have the array you are wanting. Even now, you could do something like this to loop through it...
Code: Select all
foreach($data_array as $key => $value) {
echo "<h3>$key</h3>
<table>";
for($i = 0; $i < 3; $i++) echo "<td>" . (isset($value[$i]['id']) ? $value['description'] : 'NO SUB CATEGORY FOUND!') . "</td>";
echo "</tr></table>";
}
Of course, this is untested and really...sloppily...put together. Check the query to match your stuff. Make sure my loop isn't horribly wrong. But it should (in theory only) work right out of the box.
Hope this helps.
Posted: Sun Jan 28, 2007 11:33 pm
by GeXus
That works! Thanks
