Jump to content



Recommended Posts

I need to alter this query so that it takes the AVERAGE of $sort_by_selected2.


$sort_by_selected2 can have two different values here. Either "ABS(review_product_rating) DESC" or "ABS(review_product_rating) ASC"


$sql30 = "SELECT * FROM $tbl_name JOIN $tbl_name3 USING(product_id) WHERE product_category='$cat' ORDER BY $sort_by_selected2 LIMIT $start, $limit";

Link to comment
Share on other sites

I may need a sub-query. Basically I have a reviews table ($tbl_name3) which has product_id and review_product_rating. So there maybe 5 reviews for product_id "1", and 3 reviews for product_id "2"... I need to Average together the 5 reviews for product_id "1" and average together the 3 reviews for product_id "2" but not average together product_id "1" and product_id "2". So I need to average together the product's reviews independently of each other, and then ORDER the main query be those average values.

Link to comment
Share on other sites

Alright, come to this result:


SELECT $tbl_name.product_id 
     , AVG($tbl_name3.review_product_rating) AS avg_rating 
  FROM $tbl_name 
  JOIN $tbl_name3 
    ON $tbl_name3.product_id = $tbl_name.product_id 
WHERE $tbl_name.product_category = '$cat' 
    BY $tbl_name.product_id
    BY avg_rating


The above works perfectly for the basic product_category view. But when it comes to my search page it shows no results.


Here's the search mysql query:

$likeValues = "$tbl_name2.keyword LIKE '%" . implode("%' OR $tbl_name2.keyword LIKE '%", $keywords) . "%'";

$sql10000= "SELECT * FROM $tbl_name JOIN $tbl_name2 USING(product_id) WHERE $likeValues GROUP BY product_id ORDER BY $sort_by_selected2 LIMIT $start, $limit";

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.