4

I am trying to display WC product images (thumbnails) or just get the url to that image from database. But I can't seem to find the column where the url are stored with the following. One thing I want my query to have is ability to select images of a certain category. I know _wp_attached_file is the meta key and through its associated post ID I can grab the image path but not sure how to write it.

select * from wp_posts where post_type = "product"

All helps appreciated

4
  • Image URLs are not stored in the database. Commented Oct 2, 2019 at 5:39
  • Then what is inside wp_postmeta where meta_key is _wp_attached_file?
    – ali
    Commented Oct 2, 2019 at 6:21
  • Not a URL. It's the path, relative to the content directory, which is defined in PHP. If you know what the URL to the content directory is, which you probably do, you can manually add the path to that URL, but you can't query the database for a full URL to a file. Commented Oct 2, 2019 at 6:26
  • Noted, I changed it to path so its more clear. Thanks. I am sure whatever the following query returns I should add a php function to get the beginning part of it like you said, do you happen to know what that is?
    – ali
    Commented Oct 2, 2019 at 21:04

2 Answers 2

6

The post thumbnail is not stored as a URL. The post thumbnail is stored as the attachment ID in the _thumbnail_id post meta. The actual file is stored as the _wp_attached_file post meta for that attachment post. WordPress does not store the full URL for attachments in the database. It only stores the path to the file, relative to the uploads directory.

This query will list product IDs and the path to their thumbnail files. You will then need to append those to the URL to your uploads directory:

SELECT 
    p.ID,
    am.meta_value
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'

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' );
0
0

@JacobPeattie already provided you with the correct way of getting WP path in wordpress with wc_get_product(..) and ->get_iamge method.

However this is posible to construct query in a way to get the full path to url like so:

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

Outputs:

+-----+---------------------------------------------------------------+
| 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)                                            

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.