Welcome Guest, Not a member yet? Register   Sign In
Outputting from two queries in my view?
#1

[eluser]ChrisF79[/eluser]
I want to output the cost of some products in a big table. For my example, let's say they're cars so it is easy to explain.

The make and models of the cars are stored in a table called Models. The specs of the cars (engine size, transmission, etc) are stored in Model_Specs. If someone says they want to see Ford, I have a query that goes out and selects all of the cars that are made by Ford and joins Model_Specs to bring all of that in.

A second query runs that goes out and finds the prices of each model from every dealership. So, there may be 3 versions of the Mustang and each version is sold at 10 different dealerships with 10 different prices.

How do I output that in my view? The first query would spit out a header with "Mustang 5.0 Convertible" and a little box with the specs for example The second query fetches the prices from the pricing table. I just don't understand using MVC how I'd build this sort of thing. Any advice owuld be greatly appreciated!
#2

[eluser]srpurdy[/eluser]
You would just do a foreach loop for the second query. that would loop through all the results.

This just depends on how your passing the information to the query so you know which prices to get. Best way is to use a uri segment.

So for example if your uri segment is Mustang-5-0

You could do a query like this (just change segment 3 to whatever it is.
url_model_name should be a url friendly field with dashes -
Code:
function get_car_prices()
{
$car_prices = $this->db
->where('models.url_model_name', $this->uri->segment(3))
->where('models.model_id = model_prices.model_id')
->select('
     model_prices.dealership_name,
     model_prices.price
  ')
->from('models,model_prices')
->order_by('model_prices.price', 'asc')
->get();
return $car_prices;
}

This is a bit easier if your using id numbers. Cause than all you need is a query like this
Code:
function get_car_prices()
{
$car_prices = $this->db
->where('model_prices.model_id',$this->uri->segment(3))
->select('
     model_prices.dealership_name,
     model_prices.price
  ')
->from('model_prices')
->order_by('model_prices.price', 'asc')
->get();
return $car_prices;
}

Controller would have something like
Code:
$data['car_prices'] = $this->cars_model->get_car_prices();

Than in your view you would just foreach loop.
Code:
<?php foreach($car_prices->result() as $cp):?>
html.... <?php echo $cp->price;?>
<?php endforeach;?>
#3

[eluser]ChrisF79[/eluser]
Thanks SO much for the reply (I know that took a lot of time), but what the URI segment would actually be is simply Ford. Then, I'd have a heading for Mustang 5-0, one for Econoline Van, etc. That's where I'm getting confused.

#4

[eluser]srpurdy[/eluser]
Okay if your uri segment is Ford

You will need a field that url_title() was applied to. So For example if you have a car like Bugatti-Veyron that the field in the database is Also Bugatti-Veyron. I normally use 2 name fields for this. one with the - and one with normal spaces for displaying purposes. This is only needed on the first table "models" since you can extract the id number from the first and second ->where()

In another words this line
->where('models.url_model_name', $this->uri->segment(3))

gets the record in my example Bugatti-Veyron
Then this line
->where('models.model_id = model_prices.model_id')
will then get the id of that record and then look for that id in the prices table.

Then you can go ahead and select whatever fields you need to display. Smile

Hope that helps make it make more sense. Big Grin
#5

[eluser]ChrisF79[/eluser]
I'm starting to understand. One follow-up that jumps out at me though is that in my model_specs, I have one line for each spec. So, how would I get it to pull all of those too? The similarity is the model_id.
#6

[eluser]srpurdy[/eluser]
I also meant to mention if you have Ford

and have multiple cars being listed. With prices for each one it would be better to either.

List all the cars of that make. Then use an ajax request to get the prices based on user inputs.

Or to store all the prices in an a giant array in memory. otherwise you could have a lot of queries.

Hopefully I didn't confuse you more. Smile
#7

[eluser]srpurdy[/eluser]
[quote author="ChrisF79" date="1352849147"]I'm starting to understand. One follow-up that jumps out at me though is that in my model_specs, I have one line for each spec. So, how would I get it to pull all of those too? The similarity is the model_id.[/quote]

Ah so a row per spec?

Just do that same thing extract via the model_id, and foreach loop.

It might make sense to do what I said and store this in 1 big array. and use a for loop instead of a foreach loop.

with a for loop and count. and a multi-dimensional array would be ideal for performance reasons, but this depends on how much data your going to have. Smile




Theme © iAndrew 2016 - Forum software by © MyBB