Wordpress: Custom shortcode to render image with meta data

manpreet Best Answer 2 years ago

My problem: How to pull the media data out of the database">database in PHP?

I'd like to write a shortcode that renders an image with the alt-text and caption taken automatically from the media database">database. Usage-example:

[img 126 300]  // [img media-id width]

Intended Output:

manpreet 2 years ago

I was hoping for some wordpress-integrated function to get me the needed values in a few lines of code, but with @disinfor's hint I dug a bit into the wordpress database and came to the following results:

• wp_posts.post_title is the title of the image
• wp_posts.post_excerpt is the caption of the image
• wp_posts.post_content is the content of the image's media page (not relevant here)
• wp_postmeta.meta_value WHERE meta_key='_wp_attachment_image_alt' is the alt-text of the image

Code below:

function img_shortcode($atts) {
    global $wpdb;
    try {
        $conn = new \PDO("mysql:host=" . constant('DB_HOST') . ";dbname=" . constant('DB_NAME'), constant("DB_USER"), constant("DB_PASSWORD"));
        // set the PDO error mode to exception
        $conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        $sql =  "SELECT post_title, post_excerpt FROM `". $wpdb->prefix . "posts` WHERE ID=". $atts[0];
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $img = $stmt->fetch(\PDO::FETCH_ASSOC);
        $title = $img['post_title'];
        $caption = $img['post_excerpt'];

        $sql =  "SELECT meta_value FROM `". $wpdb->prefix . "postmeta` WHERE (post_id=". $atts[0] ." AND meta_key='_wp_attachment_image_alt')";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        $alt = $stmt->fetch(\PDO::FETCH_ASSOC)['meta_key'];
    }
    catch(PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
        return NULL;
    }
    // todo: generate html from $alt, $title and $caption (and optionally further shortcode attributes)
}

add_shortcode('img', 'img_shortcode'); // Signature: [img 126] to render image with media id 126