Processing query data to produce craigslist style categories with row counts.

[eluser]Ollie Rattue[/eluser]
Hey, I need a little advice/nudge in the right direction. I have 3 tables:

manufacturers -> id, manufacturer e.g. 20, Apple
category -> id, type -> 35, MP3 Player
models -> id, model, manufacturer, category e.g. 2, Ipod, 20, 35
items -> id, heading, description, manufacturer, model e.g. 10, "Apple Ipod for sale 1 week old", "I am selling my nearly new apple ipod", 20, 2

Right now as you can see I have decided to separate the manufacturers and categories and refer to them by their integar ids. This gives me more flexibility and makes the db more dynamic e.g. if I change a category name it will stay the same id in the models table but the update will "flutter" through the website.

So from thinking it through this seems to be the most logical way to setup my db. Am I right in my thoughts here?

I want to create a webpage where you see all of the manufacturers with their corresponding models when you choose a given category. On top of this I want to do a count of the number of items within each category. So it would look something like this:

Products -> MP3 Players (breadcrumb display)

Apple (10)
-> Ipod classic (1), Ipod Mini (6), Ipod touch (3)

Samsung (15)
-> etc

Thinking this through it is a series of queries with the results from one being used for the other. Maybe I would read my results into one array which I would foreach through to build my browseable webpage.

The process will look something like this:

get id and manufacturerid from models where category = 35 would give me all the models and manufacturer ids that are MP3 players

next query items where manufacturer = the id i just got and model = theidijustgot

this would give me all the items under a certain make and model.

But the thing is I would then need to make this query for every manufacturer and model which I want to check. Is the right way to do it?

This page could end up having 1000's of db queries which I would of thought would slow thepage to a crawl. Luckily this page can be cached say once every couple of hours.

So any input, advice, example code, links would be hugely appreciated. I just don't want to spend massive ammounts of time doing a lot of overly complicated coding.

[eluser]Hannes Nevalainen[/eluser]
You need to join your tables =)

It can be little tricky at first but it's really worth digging in to it! =)
Find a nice tutorial on this (google for tutorial mysql join) and you will get lucky ^^

Happy Coding =)

[eluser]Ollie Rattue[/eluser]
[quote author="Hannes Nevalainen" date="1218558287"]You need to join your tables =)

It can be little tricky at first but it's really worth digging in to it! =)
Find a nice tutorial on this (google for tutorial mysql join) and you will get lucky ^^

Happy Coding =)[/quote]

So I looked into Joins and it turns out they are kind of useful My current model looks like this:

Code:

function browse($category, $status) {
$this->db->select(array('manufacturers.manufacturer','models.model'));
$this->db->from('manufacturers');
$this->db->join('models', 'models.manufacturerid = manufacturers.id');
$this->db->join('items','items.modelid = models.id');
$this->db->where('category', $category);
$this->db->where('status', $status);
$this->db->orderby( 'manufacturers.manufacturer', 'asc' ); // puts it into alpabetical order
$query = $this->db->get();
}

I then call this from my controller and pass the results onto my view file like so:

Code:

$data['results'] = $this->buysellmodel->browse('mp3 players','live');
$this->load->view('welcome/browse', $data);

Which works nicely at giving me a list of all the manufacturers and models of all the live items in my database for sale. Not overly relevant but I forgot to mention that my items can be live, discontinued or pending.

However what I can't work out is how I loop through this information to produce a nice list of manufacturers with their corresponding models with counts for individual models and manufacturers.

I have tried a few

Code:

foreach($results->result() as $row):

loops but it is very tricky to echo things out at the right point. What I think I have to do is read the information into an array and then print each manufacturer and model section when I have the counts for the various sections. But I can't get my head around how I can do this.

Again I really need some help or a nudge in the right direction.