dimanche 28 juin 2015

how to get third table count with group concat

I have a three tables.
1) tbl_product base table //which stores the product information.

id int(11) unsigned
product_type_id int(11) unsigned
product_image   varchar(255)
title   varchar(255)
description text
price   float(8,2)
discount_price  float(8,2)
status  tinyint(1)
created_at  datetime
updated_at  datetime

2) tbl_product_review //which stores the reviews of every product.

id  int(10) unsigned
product_id  int(10) unsigned
title   varchar(255)
description text
star_rating int(1)
status  int(1)
created_at  int(10)

3) tbl_wishlist //which store the user's wishlist details means when user add any product into his/her wishlist that entry saved in this table

id  int(11) unsigned
product_id  int(11) unsigned
user_id int(11) unsigned
status  tinyint(1) unsigned
created_at  datetime

These are my table architecture. I want fetch user's id using group concat according to product id. means if product id is 1 then all the user's id just come up with (1,3,4,5) like this. next is, I want total number of product reviews of each product. for e.g if user id is 1 then product number comes with 1 or 0 if have or don't have.

Actually i want this type of output

id  product_type_id title               description              price  discount_price       product_image      users   total_review
1   1               Product one         Product one description  786       50               product.jpg        1,2         2

I want do this by a single query.I don't want to fetch first half information after that start loop after than get next half information. I think you understand my problem.

Aucun commentaire:

Enregistrer un commentaire