How to Export Product Reviews from Magento 2 Database

Prerequisites

Magento 2 EE, MySQL 8 and PHPMyAdmin, Docker (doing this in a production Environment is not recommended)

Install PHPMyAdmin in Docker

It can be tricky to install PHPMyAdmin in Docker vs a traditional LAMP stack as you need a basic understanding of Docker Network. See commands and comments below:

# Install phpmyadmin in docker

docker pull phpmyadmin

# Create a docker container for phpmyadmin to run, in this case we call it mysqladmin

docker run --name mysqladmin -d -e PMA_ARBITRARY=1 -p 8080:80 phpmyadmin

# Now we need to connect MySQL and phpmyadmin containers so they can communicate with each other. To do so we first create a new bridge network - mysql-network

docker network create -d bridge mysql-network

# Then we connect both containers to this network

docker network connect mysql-network magento2_mysql
docker network connect mysql-network mysqladmin

Up to this point, you PHPMyAdmin login should be accessible by using:

http://localhost:8080

And you might see below login, and wonder what should I use for the Server field?

You can use your MySQL Server IP and you can find this by inspecting your MySQL container:

# Get the server name which is the IP address, this will be the server field for phpmyadmin login

docker inspect magento2_mysql | grep IPAddress

# Here is my return, if you get more than one IP you can try all of them to see which one works.
           
 "SecondaryIPAddresses": null,
     "IPAddress": "",
         "IPAddress": "172.18.0.6",

The MySQL Query to Export Product Reviews

SELECT DISTINCT r.review_id AS 'Review ID',
    p.sku AS 'SKU',
    d.title AS 'Review Title',
    d.detail AS 'Review Details',
    d.nickname AS 'Nickname',
    r.created_at AS 'Create Date',
    k.value AS 'Rating',
    h.value AS 'Product URL'
   
FROM review r 
INNER JOIN catalog_product_entity p ON p.entity_id = r.entity_pk_value 
INNER JOIN review_detail d ON d.review_id = r.review_id 
INNER JOIN rating_option_vote k ON k.review_id = r.review_id 
INNER JOIN catalog_product_entity_varchar h ON h.row_id = p.row_id 
INNER JOIN review_entity_summary j ON j.entity_pk_value = r.entity_pk_value
WHERE h.attribute_id=88 AND
h.value IS NOT NULL AND
j.rating_summary > 0;

This allows you to filter review data such as:

• Review ID
• SKU
• Review Title
• Review Details
• Nickname
• Create Date
• Rating
• Product URL

From the query results you can then export it into a CSV file which is the reason you have to use a tool such as PHPMyAdmin.

Tables Involved to Search for Product Review Details

review: This is where review_id and create_data are stored.
review_details: This gives us review_title, review_details and nickname
catalog_product_entity: We use this table to get the Product SKUs.
catalog_product_entity_varchar: We use this table to get the Product URLs.
review_entity_summary: We use this to get total rating summary, in order to remove the duplicates, we set the rating_summary greater than 0.
rating_option_vote: This is where rating stars are stored.

Above method allows you to download Magento 2 Product Reviews without the need of installing a 3rd party module. You can then import the reviews into other reviews platform such as Yotpo.