Nested queries (I think that is the term?)

[eluser]plasticated[/eluser]
I want to loop some database results on a page. But within, I want to loop some for each result which are linked by a common id number. Its basically a nested query.

So I have something like this:

Item 1
Sub item 1
Sub item 2
Sub item 3
Item 2
Sub item 1
Sub item 2
Sub item 2

How do I go about that in Codeigniter? Are there any special tricks?

[eluser]ywftdg[/eluser]
I have been asking about this as well for a few days, seems there is no answer for this. I am looking into sub-queries as an alternative for right now.

[eluser]Jilani Jidni[/eluser]
[quote author="plasticated" date="1218580876"]I want to loop some database results on a page. But within, I want to loop some for each result which are linked by a common id number. Its basically a nested query.

So I have something like this:

Item 1
Sub item 1
Sub item 2
Sub item 3
Item 2
Sub item 1
Sub item 2
Sub item 2

How do I go about that in Codeigniter? Are there any special tricks?[/quote]

are you get the data from two table like Parent-Child concept. Then I don't think there is any special tricks in CI. just get the into a result set order by Item. then render it into view page.

[eluser]plasticated[/eluser]
Because the database results are unpredictable - could be 20 results, could be 30, its impossible to create rigid queries. This has to be a dynamic solution.

In normal PHP I could create a query, and then place another query within the result code, grabbing an id from the first query output which would produce the result. However, this isn't an option if you wish to keep to MVC practices.

There has to be a solution to this? It's a pretty common requirement when creating an HTML page - especially for search results.

[eluser]kea13[/eluser]
Hi plasticated,

you might want to check out the article "Implementing An N-Level Nested Tree in PHP And PostgreSQL" over at phpriot.com - that is probably what you're looking for. This article is about doing things with PostgreSQL (obviously), but it should be adaptable to your choice of DB pretty easily.

Hope that helps,
Roman

[eluser]ywftdg[/eluser]
I am discussing this same topic in another thread, which is why I asked above too. Why is this (snippet of code):

Code:

$query = "select psku, pname, pcost, pprice, pskulist from products where pskulist <> ''";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
        $query2 = "select sum(pprice) from products where psku in ($row[4])";

So hard to figure out in CI? I run queries like this all the time, as I would assume many people do that might have arrays in rows, etc. Is there no way to run another query at the same time in a loop, and spit out the data all together?

[eluser]m4rw3r[/eluser]
My MPTtree model has a function which returns that kind of array (arrays within arrays), and a lot of other methods for manipulating/traversing trees in db.

Don't know if it is what you are searching for, but anyways.

To keep MVC practice, use a model function which returns a nested array like this:

Code:

[name]     => 'root'
[id]       => 2
[children] => array([0] => array([name] => 'something', ...),
                    [1] => array([name] => ...
                    ...

Then, in your view, loop through the array recursively (or do it in your controller), and then print it at the right place.

[eluser]Alex Kendrick[/eluser]
[quote author="ywftdg" date="1218747709"]I am discussing this same topic in another thread, which is why I asked above too. Why is this (snippet of code):

Code:

$query = "select psku, pname, pcost, pprice, pskulist from products where pskulist <> ''";
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) {
        $query2 = "select sum(pprice) from products where psku in ($row[4])";

So hard to figure out in CI? I run queries like this all the time, as I would assume many people do that might have arrays in rows, etc. Is there no way to run another query at the same time in a loop, and spit out the data all together?[/quote]

I am a newbie trying to figure this out as well. I want to run a query based off each result of a prior query and render it all in the view. I can put the nested query in the view and run it as I loop through the first query results, but this defeats the purpose of MVC. However, I am at a loss to figure out how to do this all in the controller.

Just wondering if any of the folks asking questions here ever found a solution.

[eluser]antonagestam[/eluser]
I was a bit confused about this too! But then I figured that if I want to do this the MVC way I have to do the parent-child stuff in the model.

So do your queries and loop them trough and save the data to an array wich you return to your controller. Ask me if you want to see a working example.

[eluser]Alex Kendrick[/eluser]
antonagestam, I would love to see an example! Thanks for offering.