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:
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
- Review Title
- Review Details
- Create Date
- 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.