Reviews on category page

[Dan123]

Hello!
I would be very grateful if somebody could help me with the sql query to display reviews on categories pages. Right now I am using the dgReviews module which is awesome but I would like to change it a bit and display not just any reviews but only reviews from products in that category. I am not a php coder but as far as I understand I need to use the products_to_categories table, but how I have no idea.

Here is the query on how to display latest ten reviews, could somebody point me what changes needed to do what I am asking above?

 $review_status = " and r.status = '1'";
  /* This is where you change the parameter value to output 1000 charaters or equivelent */
  $reviews_query_raw = "select r.reviews_id, r.customers_name, r.reviews_rating, p.products_id, p.products_image, pd.products_name,
                    substring(reviews_text, 1, 70) as reviews_text
                    from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, "
                           . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                    where p.products_status = '1'
					where p.products_status = '1'
                    and p.products_id = r.products_id
                    and r.reviews_id = rd.reviews_id
                    and rd.languages_id = '" . (int)$_SESSION['languages_id'] . "'
                    and p.products_id = pd.products_id
                    and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'" .
                    $review_status . "
                    order by r.reviews_id desc limit 10";

Thank you very much for your help!
Dan

[Dan123]

Well,
I found a way to display reviews from a specific category only, it is just, as I mentioned before, am not a coder so it would be good if somebody more experienced than me could take a look at it to make sure it does not have any "holes".
Thanks

  $reviews_query_raw = "select r.reviews_id, r.customers_name, r.reviews_rating, p.products_id, p.products_image, c.categories_id, pd.products_name,
                    substring(reviews_text, 1, 70) as reviews_text
                    from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, "
                           . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " c, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                    where p.products_status = '1'
                    and p.products_id = r.products_id
					and p.products_id = c.products_id
					and c.categories_id = " . $current_category_id . "
                    and r.reviews_id = rd.reviews_id
                    and rd.languages_id = '" . (int)$_SESSION['languages_id'] . "'
                    and p.products_id = pd.products_id
                    and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'" .
                    $review_status . "
                    order by r.reviews_id desc";

[philip937]

Well,
I found a way to display reviews from a specific category only, it is just, as I mentioned before, am not a coder so it would be good if somebody more experienced than me could take a look at it to make sure it does not have any "holes".
Thanks

  $reviews_query_raw = "select r.reviews_id, r.customers_name, r.reviews_rating, p.products_id, p.products_image, c.categories_id, pd.products_name,
                    substring(reviews_text, 1, 70) as reviews_text
                    from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, "
                           . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " c, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                    where p.products_status = '1'
                    and p.products_id = r.products_id
					and p.products_id = c.products_id
					and c.categories_id = " . $current_category_id . "
                    and r.reviews_id = rd.reviews_id
                    and rd.languages_id = '" . (int)$_SESSION['languages_id'] . "'
                    and p.products_id = pd.products_id
                    and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'" .
                    $review_status . "
                    order by r.reviews_id desc";

rather than trying to join the products to categories table you would probably be better (safer) selecting:
p.products_master_categories_id
or is it
p.master_categories_id
as this will already be part of the products table.

cant rememebr off the top of my head,
and then do
and p.products_master_categories_id = " . $current_category_id
for the WHERE clause...

[Dan123]

Yeap,
you are totally right! I was probably too lazy to check that the product table has the master category id as well.
Thank you very much for your help, Phil!

[philip937]

No probs glad to help, hope it does what you need it too.