HDFilmMaker2112 Posted June 7, 2011 Share Posted June 7, 2011 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 https://forums.phpfreaks.com/topic/238673-order-by-avg/ Share on other sites More sharing options...
HDFilmMaker2112 Posted June 7, 2011 Author Share Posted June 7, 2011 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 https://forums.phpfreaks.com/topic/238673-order-by-avg/#findComment-1226514 Share on other sites More sharing options...
HDFilmMaker2112 Posted June 8, 2011 Author Share Posted June 8, 2011 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"; Link to comment https://forums.phpfreaks.com/topic/238673-order-by-avg/#findComment-1226766 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.