Running a db query then running another query

#1
[eluser]the future darlo manager[/eluser]
Hi, I am trying to create a basic site map. Basically it should show each of the main menu items and then underneath each of them the sub navigation for that particular section. I have created something which works in standard php but haven't a clue how to put it together in the Codeigniter MVC framework.

Anyone willing to shed any light on this?

Code:
require_once ('html/php_includes/mysql_connect.php'); //Links to the database connection settings

$group_id = 6;

//Sets the page title
$content[content_title] = "Site Map";
    
include('html/header.inc'); //Loads the header file

//Starts to load the content
echo "<div id=\"full_width_container\" class=\"page\">";
echo "<h1>Site Map</h1>";
echo "<p>You can use the site map below to find information on the website.</p>";

$main_menu_query = "SELECT * FROM cacdp_menu_groups WHERE display_on_site = 'Y' ORDER BY list_order";
$main_menu_result = @mysql_query ($main_menu_query);
$number_of_menus = mysql_num_rows ($main_menu_result);

//If the number of is greater then zero display each top level menu element
if ($number_of_menus > 0) {
    while ($main_menu = mysql_fetch_array($main_menu_result, MYSQL_ASSOC)) {
    
        //Finds out if the page is linking to a url or a page id
        if ($main_menu[use_url] == 'Y') {
            echo "<p><a >$main_menu[long_group_name]</a></p>";
        } else {
            echo "<p><a >$main_menu[long_group_name]</a></p>";
        }    
        
        $sub_menu_query = "SELECT * FROM cacdp_sub_navigation WHERE group_id = $main_menu[group_id] AND display_on_site = 'Y' ORDER BY list_order";
        $sub_menu_result = @mysql_query ($sub_menu_query);
        // Finds out how many permission records have been found
        $number_of_sub_menus = mysql_num_rows ($sub_menu_result);
        
        if ($number_of_menus > 0) {
        echo "<ul>";
            while ($sub_menu = mysql_fetch_array($sub_menu_result, MYSQL_ASSOC)) {
                if ($sub_menu[use_url] == 'Y') {
                    echo "<li><a >$sub_menu[sub_nav_name]</a></li>";
                } else {
                    echo "<li><a >$sub_menu[sub_nav_name]</a></li>";
                }
            }
        echo "</ul>";
        }
            
    }
} else {
    echo "<p>No sections found</p>";
}

echo "<p></p>
</div>";
//End of content
    
include('html/php_includes/loadmenu.php'); //Loads the menu
include('html/footer.inc'); //Loads the footer file

#2
[eluser]pistolPete[/eluser]
Use the database class.
Use the MVC approach.

Watch the video tutorials: http://codeigniter.com/tutorials/

#3
[eluser]the future darlo manager[/eluser]
I know the basics but I'm struggling with running a query then another straight after its done whilst running through a loop.

#4
[eluser]xwero[/eluser]
Code:
$query = $this->db->query("SELECT * FROM cacdp_menu_groups WHERE display_on_site = 'Y' ORDER BY list_order");
$rows = $query->result();

foreach($rows as $key => $row)
{
   $query = $this->db->query("SELECT * FROM cacdp_sub_navigation WHERE group_id = ? AND display_on_site = 'Y' ORDER BY list_order",array($row->group_id));
   $rows[$key]['sub_rows'] = $query_>result();
}

#5
[eluser]the future darlo manager[/eluser]
Right I've been trying to think about this but don't think I'm any closer to solving it.

I have two models (one for my menu groups and one for the sub menu navigation).

Code:
class Menu_groups_model extends Model{
    
    function Menu_groups_model() {
        parent::Model();
    }
    
    function getMainNav() {
        $query = $this->db->query("SELECT *
        FROM nrcpd_menu_groups WHERE display_on_site = 'Y'
        ORDER BY list_order");
        return $query;
    }
    
}

Code:
class Sub_navigation_model extends Model{
    
    function Sub_navigation_model() {
        parent::Model();
    }
    
    function getSubNav($group_id) {    
        $query = $this->db->query("SELECT *
        FROM nrcpd_sub_navigation
        WHERE group_id = $group_id
        ORDER BY List_order
        ASC");
        return $query;
    }
    
}

I also have my sitemap function in controller....

Code:
function sitemap()
    {
        //Loads the query to construct the navigation and sub navigation
        $data['top_level_links'] = $this->Menu_groups_model->getMainNav();
        
        foreach ($data['top_level_links']->result() as $row)
        {
            $data['sub_level_links_for_$row->group_id'] = $this->Sub_navigation_model->getSubNav($row->group_id);
        }
        
    }

Basically I want to end up with something like this below...

Main navigation link no 1
-Sub navigation for that group
-Sub navigation for that group
Main navigation link no 2
-Sub navigation for that group
-Sub navigation for that group

Obviously I need to loop through each of the main group records to get my top links. This will give me the correct order they are in (list_order sort) and then loop through the sub navigation ones. However this is where I am getting unstuck. I think I need to put all the sub navigation records into one big array so I can work with them in the view but I'm not too sure how to do it.

This is probably the trickiest thing I've done yet in CI. Nothing has caused me this much grief.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.