+- 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: CI and JOINed SQL queries question (/showthread.php?tid=32702)
CI and JOINed SQL queries question - El Forum - 08-01-2010
[eluser]Unknown[/eluser]
I have a database with three tables: "product", "category", "subcategory".
Each product has a category and a subcategory field. There is a number in that field, which is the same as the cat_id and subcat_id, taken from the respective other table.
The category and subcategory tables have a field for their ids and for the cat or subcat name (a string). The subcat table also has a column for the category to which a subcategory belongs.
By following tutorials, I've managed display the results of a query from a single table: the names of all nine products from the "product" table, listed as <li> elements. That was not so tough.
My question is -- how do I go about executing a more complicated query, which I will need if I want to display the actual names of the products' categories and subcategories, instead of just the cat_id and subcat_id (these integers are the only cat/subcat-related data stored in the products table).
I will need to execute a query that looks like:
Quote:SELECT `product`.`name`, `subcat`.`name`, `cat`.`name` FROM `product`
LEFT JOIN `cat` ON `cat`.`cat_id` = `product`.`cat_id`
LEFT JOIN `subcat` ON `subcat`.`subcat_id` = `product`.`subcat_id`
WHERE `prod_id` = 1
How do I execute a query like this one? From what I read in the tutorial, I only know how to make a query that selects from just one table:
Code:
$query = $this->db->get('product', 10);CI and JOINed SQL queries question - El Forum - 08-01-2010
[eluser]Unknown[/eluser]
Why dont you run the query using the following code?
Code:
$query = $this->db->query(' your query ');CI and JOINed SQL queries question - El Forum - 08-01-2010
[eluser]Unknown[/eluser]
It works
I just had to add "AS" definitions for the category and subcategory names because all the resulting fields were called "name". Thanks!
CI and JOINed SQL queries question - El Forum - 08-02-2010
[eluser]danmontgomery[/eluser]
or, read the user guide