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, status, heading, description, manufacturer, model e.g. 10, live “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
So what I will do is query my tables using the join function. My database query looks like this:
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 function from a controller and pass the result onto a view file.
$data['results= $this->buysellmodel->browse('mp3 players','live');
$this->load->view('welcome/browse', $data);
The status field is because my items can be either live, pending or discontinued.
Anyway this query outputs exactly what I want, which is brilliant. What I am struggling with is how to process this into the format I explained above. I have dabbled a bit with loops but it is tricky to print out the right things in the right order with all the counts. What I really need to do is load up the counts into an array and then print all the information out at once. But I can't get my head around the process of doing this.
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.
