• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CI and JOINed SQL queries question

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:
$query = $this->db->get('product', 10);

Why dont you run the query using the following code?
$query = $this->db->query(' your query ');

It works Smile I just had to add "AS" definitions for the category and subcategory names because all the resulting fields were called "name". Thanks!

or, read the user guide

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.