CodeIgniter Forums

Full Version: Group by Query help from multiple tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

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(){
     ' as p_id, p.slug as p_slug, p.title as p_title, p.user_id as p_uid, p.status as p_status, 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 =', 'left');
   $user_array = array('u.banned' => 0 , 'u.email_status' => 1);
   $this->db->join('users u',' = 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 to help you figure it out.
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.