Multiple queries in one model function

[eluser]ozskry[/eluser]
Hi there,

I have a model with this code:

Code:

function categories()
    {
        $this->load->database('database_name', TRUE);
        $this->db->select('category');
        $this->db->group_by('category');
        $query = $this->db->get('table');
        
        foreach ($query->result() as $categories)
        {
            $categories_array[] = $categories;
        }
        return $categories_array;
    }

Now, depending on these categories results, I need to execute a second query like:

Code:

function sub_categories()
    {
        $this->load->database('database_name', TRUE);
        $this->db->select('sub_category');
        $this->db->where('category',$categories);
        $query = $this->db->get('table');
        
        foreach ($query->result() as $sub_categories)
        {
            $sub_categories_array[] = $sub_categories;
        }
        return $sub_categories_array;
    }

How can I do that and send the $categories_array and the $sub_categories_array to the view?

Thanks,
Cary

[eluser]xeroblast[/eluser]

Code:

$blah['categories'] = $categories_array;
$blah['sub_categories'] = $sub_categories_array;
return $blah;

[eluser]dark_lord[/eluser]
I believe this is possible and much easier through sql's JOIN statement and CI has those activerecords for joining two tables.

[eluser]ozskry[/eluser]
Hi xeroblast and wish_bear,

Thx for your quick replies.

In fact, I have one table with rows like:

article1 - category1 - sub_category1
article2 - category1 - sub_category2
article3 - category1 - sub_category3
article4 - category2 - sub_category4
article5 - category2 - sub_category4

In need to build a menu like:

category1
-sub_category1
-sub_category2
-sub_category3
category2
-sub_category4

My first query retrieves the categories and groups them. Then, I need to use these categories to find the sub_categories. I can do that in pure PHP. Don't understand how to do this in CI.

Thanks,
Cary

[eluser]xeroblast[/eluser]
tell me how'd you do it in nativePHP and we'll convert it to CI...

[eluser]ozskry[/eluser]
Hi xeroblast,

I found a solution here:

How to convert from PHP to MVC in CodeIgniter

Following this example, I created a multi-dimensional array and then used the HTML Helper and the ul() function to build the menu.

Regards,
Cary

[eluser]xeroblast[/eluser]
good for you... i already know that... i just want to help you convert your queries not mine...

anyway, continue reading...

[eluser]ozskry[/eluser]
Hi again,

Thanks for your help.

I'm coming back to you because I just realize that I can't add attributes to the <li> tags in the ul() function.

Here is my previous native PHP code:

Code:

$select_categories = "SELECT category FROM ms_artworks.artworks GROUP BY category";
$query_categories = mysql_query($select_categories) or die(mysql_error());
$row_select_categories = mysql_fetch_assoc($query_categories);
$totalrows_select_categories = mysql_num_rows($query_categories);

<ul class="menu">
&lt;?php do { ?&gt;
    <li class="category">&lt;?php echo $row_select_categories['category'];
    $category = $row_select_categories['category'];
    $select_sub_categories = "SELECT sub_category FROM ms_artworks.artworks WHERE category = '$category'";
    $query_sub_categories = mysql_query($select_sub_categories, $connection_ms_catalog) or die(mysql_error());
    $row_select_sub_categories = mysql_fetch_assoc($query_sub_categories);
    $totalrows_select_sub_categories = mysql_num_rows($query_sub_categories);?&gt;</li>
    &lt;?php do { ?&gt;
        <li class="sub_category"><a href="&lt;?php echo $row_select_sub_categories['sub_category'];?&gt;">&lt;?php echo $row_select_sub_categories['uk'];?&gt;</a> </li>
        &lt;?php } while ($row_select_sub_categories = mysql_fetch_assoc($query_sub_categories)); ?&gt;
        &lt;?php } while ($row_select_categories = mysql_fetch_assoc($query_categories)); ?&gt;
        <li class="category">Logos &amp; Guides</li>
            <li class="sub_category"><a href="logos.php">Logos</a></li>
            <li class="sub_category"><a href="guides.php">Guides</a></li>
            </ul>

Hope you can help.

Regards,
Cary

[eluser]xeroblast[/eluser]
then dont use the ul()...

i just loop the <li> with attributes if i needed one...

[eluser]ozskry[/eluser]
[quote author="xeroblast" date="1268921985"]then dont use the ul()...

i just loop the <li> with attributes if i needed one...[/quote]

Yes, ok, but how? A loop on the array?