ORDER BY AVG()

[HDFilmMaker2112]

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";

[HDFilmMaker2112]

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.

[HDFilmMaker2112]

Alright, come to this result:

 

SELECT $tbl_name.product_id 
     , AVG($tbl_name3.review_product_rating) AS avg_rating 
  FROM $tbl_name 
LEFT OUTER
  JOIN $tbl_name3 
    ON $tbl_name3.product_id = $tbl_name.product_id 
WHERE $tbl_name.product_category = '$cat' 
GROUP
    BY $tbl_name.product_id
ORDER 
    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";