Price database and confusing SQL query

[eluser]Relexx[/eluser]
I would recommend that you use active record where ever possible. read ActiveRecord

The below is not tested

Code:

$this->db->select ('prices.id, product.id, product.name, merchant.name');
$this->db->from ('prices, product, merchant, last_prices');
$where_array = array('prices.product_id' => 'last_prices.product_id', 'prices.merchant_id' => 'last_prices.merchant_id', 'prices.created_on' => 'last_prices.created', 'product.id' => 'prices.product_id', 'merchant.id' => 'prices.merchant_id');
$this->db->where ($where_array);

You may be able to do this as well I am not sure. I would be interested to know if it does

Code:

$this->db->select ('prices.id');
$this->db->select ('product.id');
$this->db->select ('product.name');
$this->db->select ('merchant.name');
$this->db->from ('prices');
$this->db->from ('product');
$this->db->from ('merchant, last_prices');
$this->db->where ('prices.product_id',  'last_prices.product_id');
$this->db->where ('prices.merchant_id', 'last_prices.merchant_id');
$this->db->where ('prices.created_on', 'last_prices.created');
$this->db->where ('product.id', 'prices.product_id');
$this->db->where ('merchant.id', 'prices.merchant_id');
$this->db->where ($where_array);

Likewise the join clauses in the where could be replaced by using $this->db->join() but I am less familiar with this style of sql syntax.