+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Simple DB Question (/showthread.php?tid=17062)
Simple DB Question - El Forum - 03-24-2009
[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
Simple DB Question - El Forum - 03-24-2009
[eluser]tomcode[/eluser]
You can join the tables, and then treat them as one table.
Simple DB Question - El Forum - 03-24-2009
[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?
Simple DB Question - El Forum - 03-24-2009
[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.
Simple DB Question - El Forum - 03-24-2009
[eluser]Jesse Schutt[/eluser]
Thanks tomcode...
Are you saying I don't need the select part?
Simple DB Question - El Forum - 03-24-2009
[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.
Simple DB Question - El Forum - 03-24-2009
[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 courseOh .. 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).
Simple DB Question - El Forum - 03-24-2009
[eluser]Jesse Schutt[/eluser]
Thanks Jedd! This helps clear a lot up for me. Appreciate it!