0

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

  • Image URLs are not stored in the database. – Jacob Peattie Oct 2 at 5:39
  • Then what is inside wp_postmeta where meta_key is _wp_attached_file? – ali Oct 2 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. – Jacob Peattie Oct 2 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 Oct 2 at 21:04
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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