PHP/CodeIgniter Relational Database Array Building

Question

SOLVED! Final answer is located at the bottom of this question

I'm trying to make a menu builder using CodeIgniter, and for some reason, I can't seem to wrap my head around the concept, even though it seems simple enough (I'm new to PHP and CI). This should have less to do with CodeIgniter itself really, as I'm really only using it for the queries and MVC pattern.

I have two tables:

• menus:

  • id
  • name

• menu_pages:

  • id
  • page_id (relational to pages.id)
  • menu_id (relational to menus.id)
  • item_name (how it appears in the menu)
  • item_order (for sorting)
  • item_parent (for nesting items in sub menus)

EDIT: This is the structure I'm looking to achieve:

array(  
    [0] => array(
        [menu_id]    => 1,
        [menu_name]  => 'Menu 1',
        [menu_pages] => array(
            [0] => array(
                [id]         => 1,
                [page_id]    => 1,
                [menu_id]    => 1,
                [item_name]  => 'Home',
                [item_order] => 0,
                [item_level] => 0,
                [parent_id]  => ''
            ),
            [1] => array(
                [id]         => 2,
                [page_id]    => 2,
                [menu_id]    => 1,
                [item_name]  => 'About',
                [item_order] => 0,
                [item_level] => 0,
                [parent_id]  => ''
            )
        )
    ),
    [1] => array(
        [menu_id]    => 2,
        [menu_name]  => 'Menu 2',
        [menu_pages] => array(
            [0] => array(
                [id]         => 3,
                [page_id]    => 3,
                [menu_id]    => 2,
                [item_name]  => 'Services',
                [item_order] => 0,
                [item_level] => 0,
                [parent_id]  => ''
            ),
            [1] => array(
                [id]         => 4,
                [page_id]    => 4,
                [menu_id]    => 2,
                [item_name]  => 'Contact',
                [item_order] => 0,
                [item_level] => 0,
                [parent_id]  => ''
            )
        )
    )
)

Here's what I've got so far (UPDATED):

function GetMenus() {
    $menus    = $this->db->get('menus');
    $menucols = $this->db->list_fields('menus');

    $pages    = $this->db->get('menu_pages');
    $pagecols = $this->db->list_fields('menu_pages');

    $arr      = array();
    $i        = 0;

    foreach($menus->result() as $menu) {
        foreach($pages->result() as $page) {
            foreach($pagecols as $col) {
                $arr[$i][$col] = $page->$col;
            }

            foreach($menucols as $cols) {
                $this->menus[$i][$cols] = $menu->$cols;

                if($arr[$i]['menu_id'] === $menu->id) {
                    $this->menus[$i]['menu_pages'] = $arr[$i];
                }
            }
        }

        $i++;
    }

    return $this->menus;
}

The above actually outputs this:

Array(
    [0] => Array(
        [id] => 1,
        [name] => default,
        [menu_pages] => Array(
            Array( /* missing #1, showing #2/2 */
                [id] => 2
                [page_id] => 1
                [menu_id] => 1
                [item_name] => About
                [item_order] => 0
                [item_level] => 0
                [parent_id] => 
            )
        )
    ),
    [1] => Array(
        [id] => 2,
        [name] => menu2,
        [menu_pages] => Array(
            Array( /* missing #3, showing #4/4 */
                [id] => 4
                [page_id] => 3
                [menu_id] => 2
                [item_name] => Contact
                [item_order] => 0
                [item_level] => 0
                [parent_id] => 
            )
        )
    )
)

As you can see, this is pretty close to what I need, but there are missing items because it seems as though they are getting overwritten in the array (it is only showing the last menu item for each menu - seems maybe they have the same keys).

Thanks for any and all help and suggestions!

EDIT: Here is the final solution, loosely based on Mischa's answer:

Model:

function GetMenus() {
    /* Yes, I know these can be chained, I unchained them to avoid
       horizontal scrolling on SO */
    $this->db->select('menus.name, menu_pages.*, pages.slug');
    $this->db->join('menu_pages', 'menu_pages.menu_id = menus.id');
    $this->db->join('pages', 'pages.id = menu_pages.page_id');
    $this->db->order_by('item_order', 'ASC');
    $menus  = $this->db->get('menus');
    $result = array();

    foreach($menus->result() as $menu) {
        $result[$menu->name][$menu->id] = array(
            'page_id'    => $menu->page_id,
            'menu_id'    => $menu->menu_id,
            'item_name'  => $menu->item_name,
            'item_slug'  => $menu->slug,
            'item_order' => $menu->item_order,
            'item_level' => $menu->item_level,
            'parent_id'  => $menu->parent_id
        );
    }

    return $result;
}

Controller:

$this->menu = $this->page->GetMenus();

View:

<ul class="nav">
    <?php foreach($this->menu['default'] as $item) { ?>
        <li>
            <a href="<?php echo $item['item_slug']; ?>">
                <?php echo $item['item_name']; ?>
            </a>
        </li>
    <?php } ?>
</ul>

Answer 1

Try this one:

function GetMenus() {
    $menus = $this->db->get('menus');
    $pages = $this->db->get('menu_pages');
    $cols  = $this->db->list_fields('menu_pages');

    foreach($menus->result() as $menu) {
        $this->menus[$menu->id] = $menu;
        foreach($pages->result() as $page) {
            if($page->menu_id === $menu->id) { 
                foreach($cols as $col) {
                    $this->menus[$menu->id][$col][] = $page->$col;
                }
            }
        }
    }

    print_r($this->menus); // for debugging to see result

    return $this->menus;
}

Answer 2

You have two queries, but I think I would opt for a single query with a join. This makes the code shorter and less complex. I haven't tested the code below, but something like this should work:

function GetMenus()
{
  $this->db->select('menus.name, menu_pages.*');
  $this->db->join('menu_pages', 'menu_pages.menu_id = menus.id');
  $this->db->order_by('menus.id');
  $q = $this->db->get('menus');

  $result = array();
  $current_menu_id = NULL;
  $i = -1;

  foreach($q->result() as $row)
  {
    if($current_menu_id !== $row->menu_id)
    {
      $i++;
      $result[] = array('menu_id' => $row->menu_id,
                        'menu_name' => $row->name,
                        'menu_pages' => array()
                  );
    }

    $result[$i]['menu_pages'][] = array('id' => $row->id,
                                        'page_id' => $row->page_id,
                                        'menu_id' => $row->menu_id,
                                        'item_name' => $row->item_name,
                                        'item_order' => $row->item_order,
                                        'item_level' => $row->item_level,
                                        'parent_id' => $row->parent_id
                                  );

    $current_menu_id = $row->menu_id;
  }

  return $result;
}

Answer 3

 $sql = "
    select 
    id , 
    name ,
    group_concat(page_id) as page_ids,
    group_concat(item_name) as item_name,
    group_concat(item_order ) as item_order ,
from menues 
left join menu_pages on menu_pages.menu_id = menues .id";

$query = $this->db->query($sql);
return $query->result();

And done expolde the cells on php end

Answer 4

You could declare $this->menus as :

$this->menus = array()

and use array_push() function like so:

array_push($this->menus,$page->$col);

I would also recommend to do join operation in model rather than looping in your function because database would do it fast and your code would look elegant.