Mike Heavers

09/11/2015

I came across the need for what has to be a pretty common thing in PHP - you have a list of items in one database table, with a category id. In another table you have a list of those categories and their ids. And you just wanna loop through all the items, and list them out organized by category. So your database looks something like this:

Items Table:

item1 {
  id: 1
  name: 'item1'
  cat: 1
}

item2 {
  id: 2
  name: 'item2'
  cat: 1
}

item3 {
  id: 3
  name: 'item3'
  cat: 2
}

Categories Table:
cat1 {
  id: 1
  name: 'cat1'
}

cat2 {
  id: 2
  name: 'cat2'
}

I had a hard time verbalizing (much less coding) this, and it took me a surprisingly long time to work it out, so I decided to post the example that finally worked for me in case anyone else is stuck. This may be helpful for anyone using PHP, but my specific example makes use of code igniter. First, start with the model:

public function get_item_by_category(){

    $this->db->select('items.*', FALSE);
    $this->db->select('categories.id as cat_id, categories.name as cat_name', FALSE);
    $this->db->from('items');
    $this->db->join('categories', 'categories.id = items.cat'); //get all items where the item cat matches category id
    $this->db->order_by('categories.id');
    $query = $this->db->get();

    if($query->num_rows() == 0){
        return false;
    } else {
        return $query->result_array();
    }

}

Here we're selecting all of the items in our items table, and selecting the category id and name from the categories table. I've given them an alias, because both my items and categories table make use of the key "name" and "id". If we don't do this, when we join the data, one of them will be overwritten by the other. So then we're joining the two tables to make all of the data available to our controller, and we're ordering it by category ID.

Next, in our controller:

$item_list = $this->item_model->get_item_by_category();

if($item_list){
    $categories = array();
    $items_by_category = array();
    $curCatIndex = -1;
    $curCatID = NULL;
    foreach($item_list as $i=>$item){

        $category_id = $item['cat_id'];
        $category_name = $item['cat_name'];
        if ($curCatID != $category_id){
            $curCatIndex+=1;
            $categories[$curCatIndex] = $category_name;
            $curCatID = $category_id;
        }
        $items_by_category[$curCatIndex][] = $item;

    }

    $data['categories'] = $categories;
    $data['items_by_category'] = $items_by_category;
}

Here we're setting an array to hold our categories and an array to hold our items. The $items_by_category will be a multidimensional array, such that all items in the first category end up in the first array, all items in the second category are in the second array, etc. Like this:

$items_by_category[0][0] = category1, item1
$items_by_category[0][1] = category1, item2
$items_by_category[1][0] = category2, item1

We're setting a counter ($curCatIndex) that will keep track of each time we hit a new category id, and a variable for the category ID to check if our category id has changed ($curCatID). If we hit a new category ID, we know we've collected all the items for that category, so we increment the curCatIndex counter:

$curCatIndex+=1;

Because our $curCatID starts out as NULL when we begin our loop, we increment the $curCatIndex so that our array will start out at index 0.

Whenever we hit a new category id, we push the name of that category to our $categories array:

$categories[$curCatIndex] = $category_name;

and set the $curCatID equal to our new id:

$curCatID = $category_id;

Then, regardless of whether we are in a new category or not, we push the item to the proper place in the multidimensional array:

$items_by_category[$curCatIndex][] = $item;

and lastly we just make these arrays available to our view:

$data['categories'] = $categories;
$data['items_by_category'] = $items_by_category;

Lastly, our view:

<?php if(isSet($items_by_category)) : ?>
    <?php foreach($categories as $i=>$category_item): ?>
        <p><?php echo $category_item ?></p>
        <?php foreach($items_by_category[$i] as $item) : ?>
            <?php
            echo $item['id'];
            ?>
        <?php endforeach; ?>
    <?php endforeach; ?>
<?php else: ?>
<p>No tems</p>
<?php endif; ?>

We have a nested loop. The outer loop loops through our categories:

    <?php foreach($categories as $i=>$category_item): ?>

Notice that we've added a variable $i to keep track of the loop index (i.e. array index)

Then we print out the name of the category:

    <p><?php echo $category_item ?></p>

and then the inner loop loops through the items in that category:

<?php foreach($items_by_category[$i] as $item) : ?>

Notice we're using the $i variable here to make sure we select the right dimension from our item array.

Then inside this loop you can do whatever you want. Here I'm just printing out the unique id of my item:

echo $item['id'];

Probably a more elegant way to do this, but hope it helps someone.