Author Topic: Filter Categories by Mfg  (Read 2186 times)

jkovar

  • Beginner
  • *
  • Posts: 34
Filter Categories by Mfg
« on: July 19, 2008, 08:05:40 am »
I would appreciate some help with my PHP code as I am trying to learn.

I have a select drop down that shows the category of products on my shop.browse by manufacturer page.  What I am trying to accomplish, is filter the drop down to show only categories assinged to the Manufacturer who's page I am browsing.

Code samples below show 1.  Current select state that shows all categories and 2.My attempt to change to filter by manufacturer using WHERE clause.

Feedback would be appreciated.

1.  Working Code

$db = new ps_DB;
      $q = "SELECT c.category_id,c.category_name FROM #__{vm}_category as c INNER JOIN #__vm_product_category_xref as x ON c.category_id =x.category_id ";
      $q .= "WHERE category_publish='Y'";
      $q .= "GROUP BY c.category_id ORDER BY category_name ASC";
      $db->query($q);
      while ($db->next_record()) {   ?>
          <option value="<?php echo $db->f("category_id"); ?>" <?php echo $category_id==$db->f("category_id") ? "selected=\"selected\"" : "";?>>
            <?php echo $db->f("category_name"); ?>

2.  Code not working (no categories show)

$db = new ps_DB;
      $q = "SELECT c.category_id,c.category_name FROM #__{vm}_category as c INNER JOIN #__vm_product_category_xref as x ON c.category_id =x.category_id "
       ."INNER JOIN #_vm_product_mf_xref as m ON x.product_id = m.product_id "
      . "WHERE x.manufacturer_id = '$manufacturer_id' "
       . "GROUP BY c.category_id ORDER BY category_name ASC";
      $db->query($q);
      while ($db->next_record()) {   ?>
          <option value="<?php echo $db->f("category_id"); ?>" <?php echo $category_id==$db->f("category_id") ? "selected=\"selected\"" : "";?>>
            <?php echo $db->f("category_name"); ?>

Thanks