Get full path to WooCommerce product image with SQL
Both WordPress / WooCommerce products are stored in wp_posts table. Images are stored in key, value (Notice NoSQL style in relational database) in wp_postmeta table. If you want to query for products with a URL to the corresponding images, you would use the fallowing sql:
SELECT
p.ID,
CONCAT((SELECT option_value FROM wp_options o WHERE o.option_name = "siteurl"), "/wp-content/uploads/", am.meta_value) AS siteurl
FROM
wp_posts p
LEFT JOIN
wp_postmeta pm ON
pm.post_id = p.ID AND
pm.meta_key = '_thumbnail_id'
LEFT JOIN
wp_postmeta am ON
am.post_id = pm.meta_value AND
am.meta_key = '_wp_attached_file'
WHERE
p.post_type = 'product'
AND p.post_status = 'publish'
AND am.meta_value IS NOT NULL;Output:
+-----+---------------------------------------------------------------+
| ID | siteurl |
+-----+---------------------------------------------------------------+
| 374 | https://rolagra.dv/wp-content/uploads/2021/11/products-01.jpg |
| 375 | https://rolagra.dv/wp-content/uploads/2021/11/products-04.jpg |
| 376 | https://rolagra.dv/wp-content/uploads/2021/11/products-05.jpg |
| 377 | https://rolagra.dv/wp-content/uploads/2021/11/products-08.jpg |
| 378 | https://rolagra.dv/wp-content/uploads/2021/11/products-09.jpg |
| 379 | https://rolagra.dv/wp-content/uploads/2021/11/products-06.jpg |
| 380 | https://rolagra.dv/wp-content/uploads/2021/11/products-02.jpg |
| 381 | https://rolagra.dv/wp-content/uploads/2021/11/products-03.jpg |
| 454 | https://rolagra.dv/wp-content/uploads/2021/11/products-04.jpg |
+-----+---------------------------------------------------------------+
9 rows in set (0.002 sec) As you can notice, the full URL need to be composed. WordPress does not store a full URL to the image. Therefore, you need to use some CONCATENATION. This is why we query for siteurl from our option table and manually add /wp-content/uploads/ part sice we know that full URL have the fallowing format:
https://rolagra.dv/wp-content/uploads/2021/11/products-01.jpg
The correct way to get product image in WordPress in PHP
Since the site URL and uploads directory are dynamic, and can be controlled via PHP, it doesn’t make sense to store the full URL. WordPress uses PHP to determine what the URL to the file is, based the site configuration and the relative path to the file. It also doesn’t make sense to store the URL, because some operations require the path, not the URL.
Also note that the only reason you’d do this with SQL is if you were accessing the file paths outside of WordPress, and not using the REST API. If you’re inside a WordPress/WooCommerce template, then the correct way to display the product image is:
$product = wc_get_product( $product_id );
echo $product->get_image( 'full' );