0

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? – Jacob Peattie Feb 28 '18 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 '18 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. – Jacob Peattie Feb 28 '18 at 11:32
-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
",'ARRAY_A');

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

SELECT
    p1.*,
    wm2.meta_value
FROM 
    wp_posts p1
LEFT JOIN 
    wp_postmeta wm1
    ON (
        wm1.post_id = p1.id 
        AND wm1.meta_value IS NOT NULL
        AND wm1.meta_key = "_thumbnail_id"              
    )
LEFT JOIN 
    wp_postmeta wm2
    ON (
        wm1.meta_value = wm2.post_id
        AND wm2.meta_key = "_wp_attached_file"
        AND wm2.meta_value IS NOT NULL  
    )
WHERE
    p1.post_status="publish" 
    AND p1.post_type="post"
ORDER BY 
    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. – Jacob Peattie Feb 28 '18 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 '18 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.