Quering categories

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Quering categories

Post 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!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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:

Code: Select all

category_id     description
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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post 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.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

That works! Thanks :)
Post Reply