[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);
