CODEWITHSUNDEEP
phpmysql
Stretcher25
Stretcher25

Reputation: 23

PHP - 2 MySQL Queries in One PHP Function

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.

Upvotes: 0

Views: 52

Answers (1)

Ratuvog
Ratuvog

Reputation: 41

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.

Upvotes: 1

Related Questions