Help with SQL

[eluser]Scott Ficek[/eluser]
I have done SQL for a number of years, but I am little rusty and I am struggling to understand the syntax in this app I have purchased.

I have this select statement:

$this->db->select('supplier_id, sum(unit_price * quantity) AS total_inventory_price, sum(cost_price * quantity) AS total_inventory_cost, sum(quantity) AS total_quantity');
$this->db->from('items');
$this->db->where('deleted', 0);
$this->db->group_by('supplier_id');
$this->db->order_by('name');
return $this->db->get()->result_array();

I simply want to join the items table to the supplier table using the supplier_id=person_id, and the code keeps bombing. Here is what I constructed:

$this->db->select('suppliers.company_name, items.supplier_id, sum(items.unit_price * items.quantity) AS total_inventory_value, sum(items.cost_price * items.quantity) AS total_inventory_cost, sum(items.quantity) AS total_quantity');
$this->db->from('items');
$this->db->join('items ON items.supplier_id = suppliers.person_id');
$this->db->where('deleted', 0);
$this->db->group_by('supplier_id');
$this->db->order_by('name');
return $this->db->get()->result_array();

I tested the SQL in mySQL and it was fine. I am obviously missing some syntax. Help!

[eluser]skunkbad[/eluser]
You may have other problems, but your syntax for join is wrong.

Code:

// This
$this->db->join(‘items ON items.supplier_id = suppliers.person_id’);
// Should be this
$this->db->join('items', 'items.supplier_id = suppliers.person_id');

Also, chaining get() and result_array() is not advised. I believe this could cause errors if there was a time when no rows were present in the result set.

[eluser]Scott Ficek[/eluser]
Thanks for the reply. Unfortunately, that did not work.

This is the original code that works:
$this->db->select('supplier_id, sum(unit_price * quantity) AS total_inventory_price, sum(cost_price * quantity) AS total_inventory_cost, sum(quantity) AS total_quantity');
$this->db->from('items');
$this->db->where('deleted', 0);
$this->db->group_by('supplier_id');
$this->db->order_by('name');

I added your code and it didn't work:
$this->db->select('supplier_id, sum(unit_price * quantity) AS total_inventory_price, sum(cost_price * quantity) AS total_inventory_cost, sum(quantity) AS total_quantity');
$this->db->from('items');
$this->db->join('items', 'items.supplier_id = suppliers.person_id');
$this->db->where('deleted', 0);
$this->db->group_by('supplier_id');
$this->db->order_by('name');

I also added that code snippet to the original longer version that has suppliers.company_name etc.

All the models in this app are using:
return $this->db->get()->row_array();
What else would I use?

[eluser]skunkbad[/eluser]
Normally you would test for rows:

Code:

// Fake query, as there is no method named "whatever"
$query = $this->db->whatever();

if( $query->num_rows() > 0 )
{
    return $query->row_array();
}

return FALSE;

If you want to see the actual query after it runs:

Code:

echo $this->db->last_query();

You can always run the query with raw SQL too:

Code:

$sql = 'SELECT * FROM girls WHERE face != "ugly"';

$this->db->query( $sql );

The user guide has all this stuff in it.

[eluser]Scott Ficek[/eluser]
Thanks.

I read most of the user guide here: http://ellislab.com/codeigniter/user-guide/

I didn't really find anything substantial about SQL.

[eluser]Matalina[/eluser]
You are probably getting a joining tables error because you are joining the same table to itself with the wrong table names. (and please use the code tags

Code:

$this->db->select('supplier_id, sum(unit_price * quantity) AS total_inventory_price, sum(cost_price * quantity) AS total_inventory_cost, sum(quantity) AS total_quantity');
$this->db->from('items');
$this->db->join('suppliers', 'items.supplier_id = suppliers.person_id');
//$this->db->join('items', 'items.supplier_id = suppliers.person_id');
$this->db->where('deleted', 0);
$this->db->group_by('supplier_id');
$this->db->order_by('name');
return $this->db->get()->result_array();

[eluser]CroNiX[/eluser]
You will probably also need to use FALSE as the 2nd parameter to your select or it will probably mess up while it tries to protect the fieldnames since it's using aggregate functions. There is also a db:elect_sum() function for that particular aggregate function.

[eluser]jmadsen[/eluser]
[quote author="Scott Ficek" date="1333736338"]Thanks.

I read most of the user guide here: http://ellislab.com/codeigniter/user-guide/

I didn't really find anything substantial about SQL.[/quote]

your answer will be in here, Scott:

http://ellislab.com/codeigniter/user-gui...ecord.html

To check up on sql specifically, you'd want to go to the MySQL site

[eluser]Scott Ficek[/eluser]
Thanks everyone.

The join was wrong as you pointed out. It turns out where statement was also blowing it up because there was a deleted field in both tables.

That section in the user guide is exactly what I was looking for. I will keep plugging along.