Simple DB Question

[eluser]Jesse Schutt[/eluser]
Hello All,

I have what should be a simple question for you experts!

In one of my projects I have a product table and a category table. The product table uses the foreign key from the category table to associate it with the particular category. In the category table there are just three fields - Category ID, Category Title, and Category Description.

I am able to display all the products on the page by querying the products table, but I am not sure how to pull in the category information. I want to be able to pull the Category Title based on the category_id in the product table. Also, I would like to access the Category Description.

Does this make sense?

Thanks in advance!

Jesse

[eluser]tomcode[/eluser]
You can join the tables, and then treat them as one table.

[eluser]Jesse Schutt[/eluser]
Thanks for the response!

Code:

function product_details($id){
            $this->db->select('*');
            $this->db->where('product_in_stock_id', $id);
            $this->db->join('ref_product_category', 'product_in_stock.category_id = ref_product_category.category_id');
            $query = $this->db->get('product_in_stock');
            
            if($query->num_rows() == 1)
            {
                return $query->row();
            }
            else
            {
                return FALSE;
            }
        }

This is what I have so far... Am I going in the right direction?

[eluser]tomcode[/eluser]
Yeah, seems good. You do not (edit) need(/edit) the select part. And try a left or right join, a plain join can give surprising results, can't remember exactly, I think if the joined has no corresponding id You get an empty result with a plain join.

[eluser]Jesse Schutt[/eluser]
Thanks tomcode...

Are you saying I don't need the select part?

[eluser]tomcode[/eluser]
[quote author="Jesse Schutt" date="1237953259"]Thanks tomcode...

Are you saying I don't need the select part?[/quote]

Exactly, You do not need the select part.

[eluser]jedd[/eluser]
Hi Jesse. Absent a schema for accuracy, I'd do something like this (because I'm not comfy with CI's AR stuff, mostly)

Code:

$query = $this->db->query (" SELECT   prod_id, prod_name, prod_this, prod_that,
                                         category.title as cat_title,
                                         category.description as cat_desc
                                 FROM product
                                 LEFT JOIN
                                         category ON category.id=product.category  
                                 WHERE prod_id=". $foo );       // the WHERE is entirely optional of course

Oh .. you might not need the SELECT part, but you should in general avoid select * as you usually don't want everything, and more importantly in joins and multi-table selects you want to avoid, or at least minimize, the pain and confusion that arises from handling fields with the same name that come from different tables. 'id' being the obvious and most common candidate. Hence the judicious use of 'AS' (gotta love the AS).

[eluser]Jesse Schutt[/eluser]
Thanks Jedd! This helps clear a lot up for me. Appreciate it!