Need help witha query

[eluser]CodeIgniterNoob[/eluser]
I have two tables.

Table 1: parent_menu_item fields(parent_menu_id, parent_name)
Table 2: child_menu_item fields(child_menu_id, child_name, parent_menu_id)

I need to pull the parent and all the children that might to belong to the parent by parent_menu_id. So it has to look like this:

ParentMenuItem1
Childmenu
Childmenu
Childmenu
ParentMenuItem2
Childmenu
Childmenu
Childmenu
And so on.....

Heres my query so far:

Code:

function getAllParentAndChildMenuItems()
      {    
        $data = array();
        $this->db->join('parent_menu_item AS b', 'a.parent_menu_id = b.parent_menu_id', 'inner');
        $this->db->where('b.status','active');
        $this->db->order_by('b.sortorder','desc');
        $this->db->order_by('a.sortorder','desc');
        $this->db->select('b.*');
        $this->db->select('a.*');
        $Q = $this->db->get('child_menu_item AS a');

        if ($Q->num_rows() > 0)
        {
            foreach ($Q->result_array() as $row)
            {
                $data[] = $row;
            }
        }
        
        $Q->free_result();  
        return $data;
    }

And heres my view so far:

Code:

if (count($menu)) {
foreach ($menu as $key => $list) {

  echo $list['parent_name']."<br />";
  if ($list['parent_menu_id'] = $list['parent_menu_id']) {
   echo $list['name']."<br />";
  }
}
}

Can someone help me with this?

[eluser]TheFuzzy0ne[/eluser]
What's the problem you're experiencing?

[eluser]TheFuzzy0ne[/eluser]
OK, thinking about it, try using a left join instead of an inner join. I think that might be your problem.

[eluser]CodeIgniterNoob[/eluser]
Im not getting the results that I want.

parent1
parent1_child1
parent1
parent1_child2

parent2
parent2_child1
parent2
parent2_child2


I need to get:

parent1
parent1_child1
parent1_child2
and so on...

parent2
parent2_child1
parent2_child2
and so on...

[eluser]TheFuzzy0ne[/eluser]
I doubt you will from a database query. You'll need to have your model format the data the way you want it, and pass that back in an array.

[eluser]CodeIgniterNoob[/eluser]
Can you give me some kind of an example?

[eluser]TheFuzzy0ne[/eluser]
Sure!

Code:

function getAllParentAndChildMenuItems()
{    
    $this->db->select('a.parent_name AS pn');
    $this->db->select('b.child_name AS cn');

    $this->db->from('parent_menu_item AS a');
    $this->db->join('child_menu_item AS b', 'a.parent_menu_id = b.parent_menu_id', 'left');
    $this->db->where('a.status', 'active');

    $this->db->order_by('b.sortorder','desc');
    $this->db->order_by('a.sortorder','desc');

    $Q = $this->db->get();

    $data = array();

    if ($Q->num_rows() > 0)
    {
        foreach ($Q->result_array() as $row)
        {
            $data[$row['pn']][] = $row['cn'];
        }
    }
        
    $Q->free_result();  
    return $data;
}

The above code is untested.

If it does work first time, it probably won't contain all of the data you need, but it should contain the data you've asked for. So if you have any more questions, please feel free to ask after you've attempted to tweak the code yourself.