Welcome Guest, Not a member yet? Register   Sign In
Group by Query help from multiple tables


I need help in a query from multiple tables.

Here are the table names:-

images (for product's images)

  1. I need the query to return result as 4 products per user but maximum 3 users.
  2. and calculate the total number of products per user.
As show in the picture:-
[Image: RcS8D3s.png]

Here is the query I have been trying:-

public function random_three_sellers(){
     'p.id as p_id, p.slug as p_slug, p.title as p_title, p.user_id as p_uid, p.status as p_status,
     u.id as u_id, u.username as u_name, u.slug as u_slug, u.email_status as u_estatus, u.banned as u_ban,
     i.product_id as i_pid, i.image_small as i_ism
   $this->db->where('p.status', 1);
   $this->db->from('products p');
   $this->db->join('images i','i.product_id = p.id', 'left');
   $user_array = array('u.banned' => 0 , 'u.email_status' => 1);
   $this->db->join('users u','u.id = p.user_id', 'left');
   $query = $this->db->get();
   // var_dump($query);
   // die();

   if ($query->num_rows() > 0) {
     return $query->result_array();
     return false;
3. Is it possible to add total number of followers per user. This data is in another table named "followers" , the column is "following_id"

4. Finally, how to show them in the view's loop?

Please ask me if any more info is required for the solution.



if you give me the table definitions (and test data for each of the 4 tables-products, images,users and followers) then I can use http://sqlfiddle.com/ to help you figure it out.

(This post was last modified: 05-04-2019, 08:39 AM by jTam.)

Hello php_rocs,

Thank you for reply.

The tables are connected by these columns:

products (id) -> images (product_id)

products (user_id) -> users (id)

users (id) -> followers (following_id)

uploaded test data "sql" file .. I can not see the uploaded file. So sharing link from googledrive.


Theme © iAndrew 2016 - Forum software by © MyBB