Get id (dynamic) from another model

[donboe]

For a menu of a shopping cart I have a menu with 4 categories. Each category has several subcategories. For that I use the following two methods:

    public function get_categories()
    {
        $sql = "SELECT category_id
                     , category
                  FROM categories
              ORDER BY category_sequence";
              
        $stmt = $this->pdo->query($sql);
        
        return $stmt->fetchAll();
    }
    
    public function get_sub_categories($categoryId)
    {
        
        $sql = "SELECT sub_category_id
                     , sub_category
                  FROM sub_categories
                 WHERE category_id = ?
              ORDER BY sub_category_id";
              
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($categoryId));
        
        return $stmt->fetchAll();
    }

In the controler I have the following for the categories:

$categories       = $this->page->get_categories();

I obviously don’t want to create 4 varaiables (1 subcategory for a category) and have 4 nested foreach loops in the menu. What is the right way to get the category_id dynamic and have just one nested foreach loop in the menu? In other words:

$sub_categories   = $this->page->get_sub_categories(?);

  <ul class="navigatie">
    <?php foreach($shop_menu as $value): ?>
      <li><a href="javascript:void(0)" title="<?php echo $value['category'] ?>"><?php echo $value['category'] ?></a>
        <ul>
          <?php foreach ($shop_sub_menu as $value): ?>
          <li><?php echo $value['sub_category']; ?></li>
          <?php endforeach; ?>
        </ul>
      </li>
    <?php endforeach; ?>  
  </ul>

Thank you in advance

[StarLion]

For starters, I wouldnt have used two tables for this - a category is a category, regardless of whether it’s a sub-category of another category. You could represent this with either an Adjacency Model or a Nested Set Model. Have a read of an [article I like to reference][1] in regards to these models.

That said, let’s tackle your need as written. As you do not care about the subcategories after the menu has been constructed, retrieve the list inside your first foreach and reuse the variable.

foreach ($shop_menu as $value) {
  $subcats = get_sub_categories($value['id']);
  //Do The Thing With The Category
  foreach ($subcats AS $subcat) {
  //Do The Thing With The SubCategory     
  }
}

Alternatively, if you want to insist on clinging to a MVC model and doing no processing at this point in your code, you’ll have to construct an array of arrays to reference. (foreach ($shop_menu_subcats[$value[‘id’]] AS $subcat))
[1]: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

[donboe]

Hi StarLion. Thanks for the responce

@StarLion

$subcats = get_sub_categories($value[‘id’]);

How do I pass this to the View within the controller?

Thank you in advance.

[StarLion]

Yeah, as i said, if you’re clinging to MVC and separating everything forever because reasons, you’ll have to stack the subcategories into a multidimensional array and call it instead.

[donboe]

Hi StarLion. Sorry for my ignorance, but here I am loosing you. When I do as you said:

@StarLion

foreach ($shop_menu as $value) {
$subcats = get_sub_categories($value[‘id’]);
//Do The Thing With The Category
foreach ($subcats AS $subcat) {
//Do The Thing With The SubCategory
}
}

I get a Call to undefined function get_sub_categories() error So what would be my best option?

Edit:

I tried the following in the controller:

$categories       = $this->page->get_categories();

foreach ($categories as $key=>$value)
{
    $sub_categories = $this->page->get_sub_categories($value['category_id']);
}

and passed them both in the View! I don’t get any more errors but each category has the same submenu (the one belonging to the last category_id)

[StarLion]

$categories       = $this->page->get_categories();
foreach ($categories as $key=>$value)
{
    $sub_categories[$value['category_id']] = $this->page->get_sub_categories($value['category_id']);
}