CodeIgniter Forums
query problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: query problem (/thread-60273.html)



query problem - El Forum - 02-20-2014

[eluser]Bigil Michael[/eluser]
I would like to create a menu like this from a single table
Code:
two wheeler
four wheeler
     jeep
     cars
             Audi
             BMW
             Benz

Code:
I have created  table with name menus, like this

id    parent     menu_name
1        0            two wheeler
2        0            four wheeler
3        1            cars
4        1            jeep
5        3            Audi
6        3            BMW
7        3            Benz

i wrote the query like this
Code:
SELECT p.menu_name as menu,d.menu_name as sub_menu,f.menu_name as sub_sub_menu FROM menus AS p join menus d on d.parent = p.id join menus f on f.parent = d.id

and tried to print the result in a normal way
Code:
<?php
foreach($list_menu as $row) {
   echo $row->menu.'<br/ >';
     echo '&nbsp;&nbsp;'.$row->sub_menu.'<br/ >';
     echo '&nbsp;&nbsp;&nbsp;&nbsp;'.$row->sub_sub_menu.'<br/ >';
} ?&gt;
got the result like this
Code:
four weeler
  cars
    Audi
four weeler
  cars
    BMW
four weeler
  cars
    benz
here two wheeler, jeep etc are missing and not in proper format.

can anyone help me to solve this issue....



query problem - El Forum - 02-20-2014

[eluser]bgreene[/eluser]
left join


query problem - El Forum - 02-20-2014

[eluser]noideawhattotypehere[/eluser]
why dont you just pull everything from the table and make it as an list in php? all these non breaking spaces look ugly. i event posted a function for this not so long time ago.


query problem - El Forum - 02-21-2014

[eluser]jonez[/eluser]
Why use a single table? Multiple tables with foreign keys are more flexible and you can cascade deletes, ensure referential integrity, etc.


query problem - El Forum - 02-21-2014

[eluser]Bigil Michael[/eluser]
thanks


query problem - El Forum - 02-25-2014

[eluser]Bigil Michael[/eluser]
i have solved up to 2 level like this.
controller
Code:
$build_array = array();
  $parent = $this->Menus_model->get_parent_menu();    
  foreach($parent as $row){
    $build_array[] = array(
     'parent_array' => $row,
     'child_array' => $this->Menus_model->get_child_menu($row['id'])  
    );    
   }  
  $data['all_menu'] = $build_array;

model

Code:
function get_parent_menu(){    
     $this->db->where('parent', 0);
     $this->db->where('status',1);
     $this->db->select('id,parent,menu_name');
     $this->db->from('menus');
     $result = $this->db->get();
     return $result->result_array();
    }

   function get_child_menu($id){    
     $this->db->where('parent', $id);
     $this->db->where('status',1);
     $this->db->select('id,parent,menu_name');
     $this->db->from('menus');
     $result = $this->db->get();    
     return $result->result_array();
    }
view
Code:
&lt;?php foreach($all_menu as $mn){?&gt;  
&lt;?php echo $mn['parent_array']['menu_name'];?&gt;
  &lt;?php  foreach ($mn['child_array'] as $row) { ?&gt;        
   &lt;?php echo $row['menu_name']?&gt;</option>
  &lt;?php } ?&gt;
&lt;?php } ?&gt;

from this code i can print main menu and sub menu. but i have to print sub of sub menu also.
can any one help me to modify this code.



query problem - El Forum - 02-26-2014

[eluser]Bigil Michael[/eluser]
can any one solve this issue.


query problem - El Forum - 02-27-2014

[eluser]jonez[/eluser]
Make your controller logic a function and call it recursively.