Database query builder

Hello,

I have a car parts catalogue which was built in codeigniter 2 by someone else and to be honest very messy and hard to follow and understand what is actually happening so as a learning curve I want to rebuild it myself using codeigniter using HMVC and a little help from the community so it is set out correctly and logical to read, although parts of the database are relational, I have a database table for the catalogue, where due to the way it was built and how it gets updated daily I can't separate it out, I'm also new to codeigniter so struggling to figure something out, I also sometimes struggle understanding the terminology used in the tutorials, as I must admit I'm a bit simple sometimes and getting on a bit in age so bear with me I might ask dumb questions, if it can be put in layman's terms or broken down to what it is actually doing in laymen's terms I can understand it.

I have the following table fields inside the catalogue table.
Manufacture
Model
Start Year
End Year
Category
Item.

I have setup a HMVC structure using wiredesignz and created the following module manufactures
with model, view, controllers inside that. I've managed from following the news tutorial to get a page loaded with all the Manufactures, 2 things that I don't fully understand?

1) should I create moduales called models, year, items, categories or should I have this all in one module called catalogue?

2) how to then create a link to a second page to query the database to select all models based on the manufacture selection, to then display a list of Models based on the Manufacture, then likewise for the year range and items, item.

To in effect end up with the following URL structures

http://sitename/manufactures/ - List all Manufactures

http://sitename/ford/ - List All Ford Models based on selecting Ford at the Manufactures Level

http://sitename/ford/focus/ - List all Ford Focus year ranges based on selecting Ford at Model Level

http://sitename/ford/focus/2005-2007/ - List all Items for a Ford Focus 2005 - 2007

http://sitename/ford/focus/2005-2007/bonnets - List all Bonnets for a Ford Focus 2005 - 2007
Based on the News Tut this is what I currently have
Manufactures - Controller

PHP Code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Manufactures extends MX_Controller {
 
 public function __construct()
 {
 parent::__construct();
 $this->load->model('manufactures_model');
 }
 public function index()
 {
 $data['manufactures'] = $this->manufactures_model->get_manufactures();
 $data['meta_title'] = 'meta title';
 $data['meta_desc'] = 'meta desc';
 $data['meta_keywords'] = 'keywords';
 $data['title'] = 'Select manufacture';
 
 $this->load->view('templates/header', $data);
 $this->load->view('manufactures', $data);
 $this->load->view('templates/footer', $data);
 
 }
 
} 


Manufacture - Model

PHP Code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Manufactures_model extends CI_Model {
 
 
 public function __construct()
 {
 $this->load->database();
 }
 public function get_manufactures($manufacture = FALSE)
 {
 if ($manufacture === FALSE)
 {
 $query = $this->db
 ->select('manufacture')
 ->group_by('manufacture')
 ->order_by('manufacture', 'desc')
 return $query->result_array();
 }
 
 $query = $this->db->get_where('catalog', array('manufacture' => $manufacutres));
 return $query->row_array();
 
 
 }
} 


Manufacture View

Code:

<div class="container">
<h2><?php echo $title; ?></h2>
<div class="row">
<?php foreach ($manufactures as $manufactures_items): ?>
<div class="col-xs-12 col-md-4">
       <?php echo ucwords(strtolower($manufactures_items['manufacture'])); ?>
 </div>

<?php endforeach; ?>
</div>
</div>

Cheers

M

Use the same module and in your Manufactures Controller add methods for the table fields.

Manufactures/manufacture
Manufactures/model
Manufactures/startYear
Manufactures/endYear
Manufactures/category
Manufactures/item

You will also need to pass an id to some of the methods.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Hi

Thanks for the quick reply, I can't use ID's due to the way the database is and how it gets updated, the same item appears several times in the database under different IDs and item name, as the same part can fit several year ranges as well as several different manufacture/models so 1 bonnet can in fact be used on 20 or so different cars, but the way the original catalogue was built several years with over 18,000+ products the only relation to each Item inside the table is the field SKU (I forgot to mention this in the first post)

If we use SKU P100A for example
This can relate to the following...

Peugeot > 206 > 2006 - 2010 > Front Bonnet

Peugeot > 206 > 2010 - onwards > Front Bonnet

Peugeot > 106 > 2002 - 2010 > Front Bonnet

so this would appear in the database as the following:

--------------------------------------------------------------------
Manufacture | Model | Start Year | End Year | Item | SKU
--------------------------------------------------------------------
Peugeot | 206 | 2006 | 2010 | Front Bonnet | P110A
Peugeot | 206 | 2010 | | Front Bonnet | P110A
Peugeot | 106 | 2002 | 2010 | Front Bonnet | P110A
--------------------------------------------------------------------

Only thing I can figure is from the manufactures list to produce a models list is use where manufacture = ford and ->group_by('model')
to generate the list of Ford Models by using URL ref

<a href="/ford">Ford</a>

problem is I can't figure how to write the model query to build the models list based on a manufacture, once I get over that stumbling block I sould be able to work out to do the rest...

then the next stage for the year range would be
<a href="/ford/focus">Ford Focus</a>

If you can help me out to get over this stumbling block it would be much appreciated

Cheers

M

or to put it another way how do I write the query select all models where manufacture = manufacture such as Ford? to create the list as I can't figure it out based on the tutorial?

public function listbymanufacturer($manufacturer){}

$this->db-where('manufacture',$manufacturer);
$q=$this->db->get('Manufactures_table')->result();

if ($q->num_rows() > 0) {
return $q->result();
} else {
return FALSE;
}

Thanks qury I will try this later, for some strange reason i didn't get an email telling me someone had replied....

To get emails you need to subscribe to this forum on top.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )