How do I output from multiple queries in my view? (Take 2)

[eluser]ChrisF79[/eluser]
I'm extremely confused by how I can output data from various tables in my view. I don't think joining the data in the query is the answer so here's the concept:

Tables
Categories - Contains category id, category name

Models - Contains ID, laptop name, laptop model number

Model_specs - Contains the specs for each laptop. Models table ID is also in this table to join on if needed. The confusing part is each spec is on it's own row (processor is a row, memory is a row, etc)

Pricing - Has the model id from the model table (to link those two tables) and then the pricing from various stores.

My Problem
I want to create a view that shows the Model Name from the Model Table in an H2 tag, with a <ul> showing the model specs. Then under that, I'd show the pricing from each of the vendors.

If I run a query that selects all of the models from a particular category and their specs, I have the same model number 6 times (since that's how many rows of specs there are).

My Question
How can I run these queries so that I can create the output I need in my view? Do I run three separate queries (one that gets the models, then one that goes out and gets the specs for that model, and then one that goes out and gets the pricing for that model)?

Either way, how do I use the MVC to accomplish this?

Edit - Query Results
Here is a screenshot of query resutls when I use the join method. I don't think this is the right way of doing it.

[eluser]srpurdy[/eluser]
Hi Again Chris lol

You can do this in 3 queries. If you want, and there is nothing really wrong with that.

But you could do it in two as well by just using a count in your loop, so the first query has the model specs and the lap top name. you can just do this

Code:

<?php $i = 0;?>
<?php foreach($models_and_specs->result() $ms):?>
<?php if($i == 0):?>
<h2>&lt;?php echo $ms-laptop_name;?&gt;</h2>
<ul>
<li>&lt;?php echo $ms->model_spec_name;?&gt; : &lt;?php echo $ms->model_spec_value;?&gt;</li>
</ul>
&lt;?php else:?&gt;
<ul>
<li>&lt;?php echo $ms->model_spec_name;?&gt; : &lt;?php echo $ms->model_spec_value;?&gt;</li>
</ul>
&lt;?php endif;?&gt;
&lt;?php $i++;?&gt;
&lt;?php endforeach;?&gt;

Than just do another foreach loop for the prices which would be your second query.

[eluser]ChrisF79[/eluser]
So in my controller, I call the three functions that are in my model to do three queries. And then in the view, I'm just nesting foreach statements?

[eluser]PhilTem[/eluser]
In the end it's up to you how you're concatenating the data. But here are three ways of doing:

1) Library
Create a library with one function that will create a nested array of your models with their specs as children. Just call this function from your controller and pass the resulting array to the view. Then it's simple loooping over the array items and displaying them properly

Code:

foreach ( $models as $name => $specs )
{
  foreach ( $specs as $spec )
  {
  }
}

1b) Model
Create a model that basically does the same as the library. Since a library isn't real MVC you might want to create it as a model

2) Do the concatenating of data just like the library would do in the controller. It must not break the MVC definition though it actually isn't recommended doing so

3) Get the results of each model's function call and assign it to the view and do some fancy looping over these results

Number 3 is in most cases the way I choose because I can easily add another "sub-entry" like the specs without big of a hassle. However, if I need to display or use the data on more than one page I put things into a library. Or if it's designed to be an API then you're better off having one common place to collect the data.