Sharing reviews with multiple product id's

[marcopolo]

I have the same product listed in multiple categories which the category defines how it is packed. So for example:

Category 1 - Product A (for example this product has a product_id of 1)
Category 2 - Product A (for example this product has a product_id of 100)
Category 3 - Product A (for example this product has a product_id of 1000)

I have added a custom product define within the database table PRODUCTS called: product_review_id which for example the above products all have define as 60

I would like to show the reviews based on product_review_id instead of product_id

So for example if a customer leaves a review Category 1 - Product A I would like that review to be seen on all three products since they share the same
product_review_id of 60.

I can not get it to work any help would be appreciated.

[DrByte]

You didn't say which "display" of reviews you're talking about.
If it's on the product page, you'll need to edit this query:
https://github.com/zencart/zencart/b...rs.php#L55-L59

If it's on the "reviews" page, you'll need to edit the various queries in /includes/modules/pages/product_reviews/header_php.php and maybe also the product_reviews_info directory.

Note: links are based on Zen Cart v1.5.7

[marcopolo]

Thank you that's the section of code I was trying to modify and for whatever reason could not get it to work. I will try again I'm not much of a coder so I most likely did something wrong. I'm on version v1.5.5f of Zen Cart.

You didn't say which "display" of reviews you're talking about.
If it's on the product page, you'll need to edit this query:
https://github.com/zencart/zencart/b...rs.php#L55-L59

If it's on the "reviews" page, you'll need to edit the various queries in /includes/modules/pages/product_reviews/header_php.php and maybe also the product_reviews_info directory.

Note: links are based on Zen Cart v1.5.7

[marcopolo]

Almost have this working the displaying of reviews based on the custom product define products_review_id is working.

I have products_review_id in TABLE products and in TABLE reviews. I imported my reviews that were from a different source into the reviews table and added the table field products_review_id there. The displaying of the reviews based on this is done using the code below:

  $reviews = $db->Execute('SELECT SUM(reviews_rating) AS rating, COUNT(reviews_id) AS total_review FROM ' . TABLE_REVIEWS . ' WHERE status = 1 AND (products_id = ' . (int)$productId . ' OR products_review_id IN (SELECT products_review_id FROM ' . TABLE_PRODUCTS . ' WHERE products_id = ' . (int)$productId . '))'); 

Now the issue going forward is that every new review made within Zencart I will need to write to TABLE reviews the products_review_id based on the product_id the review is being made for. The code needs to reference the TABLE products and get the products_review_id from there to write it to the TABLE reviews.

I can not get it to work can you give an example of the code that will do this?

Then if not to much trouble a second piece of code I can use as an if statement to hide the reviews from products that the products_review_id in TABLE products is empty?

Thanks for your help!

[carlwhat]

Almost have this working the displaying of reviews based on the custom product define products_review_id is working.

I have products_review_id in TABLE products and in TABLE reviews. I imported my reviews that were from a different source into the reviews table and added the table field products_review_id there. The displaying of the reviews based on this is done using the code below:

  $reviews = $db->Execute('SELECT SUM(reviews_rating) AS rating, COUNT(reviews_id) AS total_review FROM ' . TABLE_REVIEWS . ' WHERE status = 1 AND (products_id = ' . (int)$productId . ' OR products_review_id IN (SELECT products_review_id FROM ' . TABLE_PRODUCTS . ' WHERE products_id = ' . (int)$productId . '))'); 

Now the issue going forward is that every new review made within Zencart I will need to write to TABLE reviews the products_review_id based on the product_id the review is being made for. The code needs to reference the TABLE products and get the products_review_id from there to write it to the TABLE reviews.

I can not get it to work can you give an example of the code that will do this?

Then if not to much trouble a second piece of code I can use as an if statement to hide the reviews from products that the products_review_id in TABLE products is empty?

Thanks for your help!

there are a multitude of ways to solve this problem. frankly, i do not like the idea of writing to the TABLE_REVIEW a products_review_id. and in reviewing your code, i see no need.

this is untested, but perhaps you can get the idea. all you need is the product_review_id in the products table. nothing else is needed:

    $sql = "SELECT products_review_id FROM " . TABLE_PRODUCTS . " WHERE products_id = " . (int) $productId;
    $show_reviews = $db->Execute($sql);
    if (empty($show_reviews->fields['products_review_id'])) {
        // dont show reviews
    } else {
        // show reviews
        $review_id = $show_reviews->fields['products_review_id'];
        $reviews = $db->Execute('SELECT SUM(rv.reviews_rating) AS rating, COUNT(rv.reviews_id) AS total_review
FROM ' . TABLE_REVIEWS . ' rv
LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = rv.products_id
WHERE rv.status = 1 AND  rv.products_review_id = ' . $review_id);

    } 

best.

[marcopolo]

Thank you for the code it works but the code below needs to be changed. It's not displaying the reviews with matching product_review_id since now the above code referenced the products table. Which by the way is a better way of doing it I agree.

  $sql = 'SELECT * FROM ' . TABLE_REVIEWS . ' r, ' . TABLE_REVIEWS_DESCRIPTION . ' rd WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND (r.products_id = ' . (int)$productId . ' OR r.products_review_id IN (SELECT products_review_id FROM ' . TABLE_PRODUCTS . ' WHERE products_id = ' . (int)$productId . ')) AND rd.languages_id = ' . $_SESSION['languages_id']; 

[carlwhat]

Thank you for the code it works but the code below needs to be changed. It's not displaying the reviews with matching product_review_id since now the above code referenced the products table. Which by the way is a better way of doing it I agree.

  $sql = 'SELECT * FROM ' . TABLE_REVIEWS . ' r, ' . TABLE_REVIEWS_DESCRIPTION . ' rd WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND (r.products_id = ' . (int)$productId . ' OR r.products_review_id IN (SELECT products_review_id FROM ' . TABLE_PRODUCTS . ' WHERE products_id = ' . (int)$productId . ')) AND rd.languages_id = ' . $_SESSION['languages_id']; 

marco,
when posting code, put the PHP tags on a separate line; it makes it easier to read.... at least for me!

for the code that you posted you can add the left join again, and you will still need the '$review_id' var that we established above...

try something like:

$sql = 'SELECT * FROM ' . TABLE_REVIEWS . ' r, ' . TABLE_REVIEWS_DESCRIPTION . ' rd
LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = r.products_id
WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND rd.languages_id = ' . $_SESSION['languages_id']  . ' 
AND  p.products_review_id = ' . $review_id;
    
    // i think this is correct p.products_review_id (it looks wrong in my above post, but you probably figured that out 

best.

[marcopolo]

Ok that generates the following error:

[17-Feb-2021 13:35:42 America/New_York] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 :: select count(r.reviews_id) as total FROM reviews r, reviews_description rd LEFT JOIN products p on p.products_id = r.products_id WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND rd.languages_id = 1 AND p.products_review_id


Not sure what you meant by "// i think this is correct p.products_review_id (it looks wrong in my above post, but you probably figured that out " Your aoriginal code worked just fine.

[marcopolo]

Ok the above error was just the code missed the part where you said "you will still need the '$review_id' var that we established above..."
So adding that to code still generates an error but it is different:

[17-Feb-2021 15:12:59 America/New_York] PHP Fatal error: 1054:Unknown column 'r.products_id' in 'on clause' :: select count(r.reviews_id) as total FROM reviews r, reviews_description rd LEFT JOIN products p on p.products_id = r.products_id WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND rd.languages_id = 1 AND p.products_review_id = 3000 ==>


I added the code like this:

 $sql = "SELECT products_review_id FROM " . TABLE_PRODUCTS . " WHERE products_id = " . (int)$productId;
    $show_reviews = $db->Execute($sql);
    if (empty($show_reviews->fields['products_review_id'])) {
        // dont show reviews
    } else {
        $review_id = $show_reviews->fields['products_review_id'];
        $reviews = $db->Execute('SELECT SUM(rv.reviews_rating) AS rating, COUNT(rv.reviews_id) AS total_review
FROM ' . TABLE_REVIEWS . ' rv
LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = rv.products_id
WHERE rv.status = 1 AND  rv.products_review_id = ' . $review_id);

$sql = 'SELECT * FROM ' . TABLE_REVIEWS . ' r, ' . TABLE_REVIEWS_DESCRIPTION . ' rd
        LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = r.products_id
        WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND rd.languages_id = ' . $_SESSION['languages_id']  . ' 
        AND p.products_review_id = ' . $review_id;
    } 

[carlwhat]

Ok that generates the following error:

[17-Feb-2021 13:35:42 America/New_York] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 :: select count(r.reviews_id) as total FROM reviews r, reviews_description rd LEFT JOIN products p on p.products_id = r.products_id WHERE r.reviews_id = rd.reviews_id AND r.status = 1 AND rd.languages_id = 1 AND p.products_review_id


Not sure what you meant by "// i think this is correct p.products_review_id (it looks wrong in my above post, but you probably figured that out " Your aoriginal code worked just fine.

marco,
i can point you in the direction, but you will need to be able to do the debugging, and solve these errors.

the code in my original post #5 ONLY worked, because you have modified the TABLE_REVIEWS to have the review_id. i would re-read that post. you only need to modify the TABLE_PRODUCTS to have the review_id; and that is how I would do it. that is why i posted that it was wrong in my original post. i would remove that EXTRA field from the TABLE_REVIEWS.

the error that you are posting is not my code. you have modified it and as such, i would suggest you look it what i posted. the following code works without errors, with ONLY the products table modified to add the 1 field.

$sql = "SELECT products_review_id FROM " . TABLE_PRODUCTS . " WHERE products_id = " . (int)$productId;
    $show_reviews = $db->Execute($sql);
    if (empty($show_reviews->fields['products_review_id'])) {
        // dont show reviews
    } else {
        // show reviews
        $review_id = $show_reviews->fields['products_review_id'];
        $reviews = $db->Execute('SELECT SUM(rv.reviews_rating) AS rating, COUNT(rv.reviews_id) AS total_review
FROM ' . TABLE_REVIEWS . ' rv
LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = rv.products_id
WHERE rv.status = 1 AND  p.products_review_id = ' . $review_id);

        $sql = 'SELECT * FROM ' . TABLE_REVIEWS . ' r 
        LEFT JOIN ' . TABLE_REVIEWS_DESCRIPTION . ' rd on r.reviews_id = rd.reviews_id
LEFT JOIN ' . TABLE_PRODUCTS . ' p on p.products_id = r.products_id
WHERE  r.status = 1 AND rd.languages_id = ' . $_SESSION['languages_id'] . '
AND  p.products_review_id = ' . $review_id;
        $review = $db->Execute($sql);
    } 

whether or not this gives you the desired results i can not tell you.

best.