Sharing reviews with multiple product id's
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.
Re: Sharing reviews with multiple product id's
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
Re: Sharing reviews with multiple product id's
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.
Quote:
Originally Posted by
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
Re: Sharing reviews with multiple product id's
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:
PHP Code:
$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!
Re: Sharing reviews with multiple product id's
Quote:
Originally Posted by
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:
PHP Code:
$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:
PHP Code:
$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.
Re: Sharing reviews with multiple product id's
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.
PHP Code:
$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'];
Re: Sharing reviews with multiple product id's
Quote:
Originally Posted by
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.
PHP Code:
$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:
PHP Code:
$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.
Re: Sharing reviews with multiple product id's
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.
Re: Sharing reviews with multiple product id's
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:
PHP Code:
$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;
}
Re: Sharing reviews with multiple product id's
Quote:
Originally Posted by
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.
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.
PHP Code:
$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.