PHP - 2 MySQL Queries in One PHP Function

Question

I have two functions that I am using 2 different queries. I need to make one function that will retrieve the number of views for an item. Below are the two functions:

public function products_views(){
    $this->db->select('*');
    $this->db->from('products');
    $this->db->order_by('view_count', 'DESC');

    $query = $this->db->get();
    return $query->result();
}       



public function getViewCount($product_id) {
    $this->db->select('COUNT(*) AS cnt');
    $this->db->from(views);
    $this->db->where('product_id', $product_id);
    $query = $this->db->get();

    return $query->row()->cnt;
}

I want a query that will return all the total view count for each product from the views table and display all the products from the products table showing the total view count.

How are the tables related? I see there is a product_id in views but you have view_count in products. You should be able to consolidate with a join and group by. — Shad Mickelberry
– Shad Mickelberry, Commented Aug 21, 2015 at 16:57
combine the result of both queries in an array. And return that array. — Talk2Nit
– Talk2Nit, Commented Aug 21, 2015 at 16:59

Ratuvog · Accepted Answer · 2015-08-21 17:23:38Z

1

You need to use JOIN for tables products and views connecting products.id with views.product_id. As a result, the request should look like this:

SELECT products.id, COUNT(views.id) as cnt 
FROM views JOIN product ON products.id = views.product_id 
GROUP BY views.product_id

You need to interpret this request using your active records.

answered Aug 21, 2015 at 17:23

Ratuvog

414 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stretcher25 Over a year ago

Great, that works. What about if I want to show all products from the products table even the ones that don't have no view records in the views table?

Ratuvog Over a year ago

replace FROM views JOIN product to FROM product JOIN views and group by products.id

Collectives™ on Stack Overflow

PHP - 2 MySQL Queries in One PHP Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related