MainWP Community

How do I export all products from phpMyAdmin using a MySQL query that do not have a featured image

Trying to export a lot of products out of WooCommerce can be a process which is very slow to run using a plugin that has to use PHP to run. If you have a number of products on a site that do not have featured product images on them then it is possible to run a MySQL query in phpMyAdmin and then save it as a view, then in another new MySQL query in phpMyAdmin you can query that view. Then in the results that the query generates you can then export to a CSV file.

Save this query as view called Product_List.

SELECT
    `wp_posts`.`post_title` AS `post_title`,
    `wp_posts`.`post_name` AS `post_name`,
    `wp_postmeta`.`meta_value` AS `meta_value`,
    `wp_posts`.`ID` AS `ID`
FROM
    (
        `wp_posts`
        JOIN `wp_postmeta` ON (
            (
                `wp_posts`.`ID` = `wp_postmeta`.`post_id`
            )
        )
    )
WHERE
    (
        (
            `wp_posts`.`post_type` = 'product'
        )
        AND (
            `wp_postmeta`.`meta_key` = '_sku'
        )
    )
ORDER BY
    `wp_postmeta`.`meta_value`

Now you can query that view using Product_List

SELECT
Products_List.ID as "Product ID",
Products_List.meta_value as "SKU",
Products_List.post_name as "Product Name",
Products_List.post_title as "Product Title",
'NO' AS "HAS FEATURED Image"
FROM
    Products_List
WHERE
    ID NOT IN (
        SELECT
            Products_List.ID
        FROM
            Products_List
        LEFT JOIN wp_postmeta ON Products_List.ID = wp_postmeta.post_id
        WHERE
            wp_postmeta.meta_key = '_thumbnail_id'
    )
UNION
SELECT
Products_List.ID as "Product ID",
Products_List.meta_value as "SKU",
Products_List.post_name as "Product Name",
Products_List.post_title as "Product Title",
'YES' AS "HAS FEATURED Image"
        FROM
            Products_List
        LEFT JOIN wp_postmeta ON Products_List.ID = wp_postmeta.post_id
        WHERE
            wp_postmeta.meta_key = '_thumbnail_id'

The MySQL query will return the POST ID of the product, product title, sku. This will be very fast to run since you are making the query in MySQL even if you have say 10k products on a site.

2 Likes

The last column in the CSV will have a YES or NO value in it. YES would mean it does have a featured image and NO it does not have a featured image. Then you can delete the last column and remove all entries with YES on them so you have a CSV of only products without featured images set on them.

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.