I'm doing a custom gallery for a portfolio page in a website and It works well if I simply use the URL of the media uploaded this way :

SELECT * FROM wp_posts AS p INNER JOIN wp_postmeta AS m ON p.ID=m.post_id 
WHERE p.post_type='attachment' AND m.meta_key='_wp_attached_file'

The thing is, if I upload too many big files, the page gets sloooow. This is why I'd like to use thumbnail but I have no idea how to select them accordingly in the database. They're created in the wp-content/uploads folder, but I can't see which meta_key to select in the database.

Also noticable, my databse doesn't have any "_thumbnail_id" in the meta_key column.

Anyone has an answer ?

  • Is there any particular reason you're using SQL for this? And not a WP_Query? or get_posts() or something? Could you maybe include the code that shows what you're doing with the results? Feb 28, 2018 at 10:32
  • Jacob, I'm just more comfortable with it, since I have a lot of conditions about whereas a media show up or not, didn't know there was a get posts function.
    – Gawet
    Feb 28, 2018 at 10:59
  • All the sizes are stored together in _wp_attachment_metadata, but you're going to need to unserialize and handle any missing sizes yourself. It'll be much easier if you use WordPress functions. Feb 28, 2018 at 11:32

1 Answer 1

$Featured_image = $wpdb->get_results("
    SELECT p.*
      FROM net_5_postmeta AS pm
     INNER JOIN net_5_posts AS p ON pm.meta_value=p.ID 
     WHERE pm.post_id = $da_id
       AND pm.meta_key = '_thumbnail_id' 
     ORDER BY p.post_date DESC 
     LIMIT 15

A related solution, to query for posts WITHOUT providing a post ID (ordered by post date, and using the wp_ database prefix):

    wp_posts p1
    wp_postmeta wm1
    ON (
        wm1.post_id = p1.id 
        AND wm1.meta_value IS NOT NULL
        AND wm1.meta_key = "_thumbnail_id"              
    wp_postmeta wm2
    ON (
        wm1.meta_value = wm2.post_id
        AND wm2.meta_key = "_wp_attached_file"
        AND wm2.meta_value IS NOT NULL  
    AND p1.post_type="post"
    p1.post_date DESC
  • This is selecting posts with an attachment, that's not what Gawer wanted. Gawet is asking how to get the URLs for the resized versions of attachments. Feb 28, 2018 at 10:28
  • Hi Dharmishtha, unfortunetely, my database doesn't have any "_thumbnail_id" in the meta_key, any idea about the cause ?
    – Gawet
    Feb 28, 2018 at 10:54

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.