Logically select categories and subcategories (php,joomla,javascript,ajax)
Asked 07 September, 2021
Viewed 944 times
  • 54
Votes

Hello I have installed jbusinessdirectory component for joomla, and I have module named mod_jbusinessdirectory (this is a search module for business listing) in tmpl/default.php file I have select code: (see below)

<?php if($params->get('showCategories')){ ?>
    <div class="select">
        <div class="categoryic"></div>
        <select name="categorySearch" class="select-styled" id="categories">
    <option value="0">category</option>
    <?php foreach($categories as $category){?>
    <option value="<?php echo $category->id?>" <?php echo $session->get('categorySearch')==$category->id && $preserve?" selected ":"" ?> ><?php echo $category->name?></option>
    <?php if(!empty($category->subcategories)){?>
    <?php foreach($category->subcategories as $subCat){?>
    <option value="<?php echo $subCat->id?>" <?php  echo $session->get('categorySearch')==$subCat->id && $preserve?" selected ":"" ?> >-- <?php echo $subCat->name?></option>
    <?php }?>
      <?php }?>
      <?php }?>
    </select>
    </div>
 <?php }?>

From this code I get categories and subcategories like this:

  • Main category 1
  • subcategory 1 subcategory 2 subcategory 3

  • Main category 2

  • subcategory 1 subcategory 2 subcategory 3

screenshot here: categories and sub categories screenshot

In helper.php I have functions that get categories and subcategories from database

static function getMainCategories(){
    $db = JFactory::getDBO();
    $query = ' SELECT * FROM #__jbusinessdirectory_categories where parent_id=1 and published=1  order by name';
    $db->setQuery($query);
    return $db->loadObjectList();
}

static function getSubCategories(){
    $db = JFactory::getDBO();
    $query = ' SELECT c.* FROM #__jbusinessdirectory_categories c
               inner join  #__jbusinessdirectory_categories  cc  on c.parent_id = cc.id  where c.parent_id!=1  and cc.parent_id = 1 and c.published=1
               order by c.name';
    $db->setQuery($query,0,1000);
    $result = $db->loadObjectList();

    return $result;
}

And lastly in modjbusinesdirectory.php file I have the PHP like this:

if($params->get('showCategories')){
    $categories =  modJBusinessDirectoryHelper::getMainCategories();
    if($params->get('showSubCategories')){
        $subCategories = modJBusinessDirectoryHelper::getSubCategories();
        foreach($categories as $category){
            foreach($subCategories as $subCat){
                if($category->id == $subCat->parent_id){
                    if(!isset($category->subcategories)){
                        $category->subcategories = array();
                    }
                    $category->subcategories[] = $subCat;
                }
            }
        }
    }
}

categories and subcategories table structure screenshot here

My question is: How do I make Two select queries instead of one. Where in the first query I get the main categories and in the second query I get the subcategories (eg: if I choose from the first query the main category books and in the second query I choose children it has to show only books with the subcategory children books).

2 Answer