Multi tool use

How to make this query like that in Codeigniter and mysql? [duplicate]

This question already has an answer here:

Suppose there are two tables i.e product and product_status

here is the structure of product table

-------------------------------------------------
| ID | PRODUCT_NAME | PRODUCT_DESC |
-------------------------------------------------
| 1 | abc | abc desc |
-------------------------------------------------
| 2 | xyz | xyz desc |
-------------------------------------------------


here is the structure of product_status table

-------------------------------------------------
| ID | PRODUCT_ID | PRODUCT_STATUS |
-------------------------------------------------
| 1 | 1 | status 1 |
-------------------------------------------------
| 2 | 1 | status 2 |
-------------------------------------------------


If I make a query through php (codeigniter) then it will show the data like this.

$this->db->where('ID',1);
$query = $this->db->get('product');
$product = $query->row();

echo $product->PRODUCT_NAME;


But my question is how to get status from product_status table for that specific product id without doing another query? I mean I want a single query which will fetch a single record (because id is unique) and show the product data from product table as well as product status from product_status table as obejct array like this-

$products = $product->PRODUCT_STATUS;


So I can loop through to $products and print the status.

foreach ($products as $p) {
echo $p;
}


Is it possible without making another query?

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer
1

$sql = sprintf("SELECT p.*, ps.PRODUCT_STATUS FROM product p LEFT JOIN product_status ps ON(p.ID = ps.PRODUCT_ID) WHERE p.ID=%d", $product_id);
$query = $this->db->query($sql);
$product = $query->row();