How get featured image via SQL query in WordPress?

Question

I have created a custom JSON API for my website for my Blog Posts, but I cannot find any way to add the featured image URL to the API. Here is my API:

<?php
header("Content-Type: application/json");
require_once 'wp-connect.php';

$result = [];
$sql = <<<SQL
SELECT * 
FROM wp_users
INNER JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post'
INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
) pivoted ON pivoted.user_id = wp_users.ID
ORDER BY wp_posts.ID
SQL;
foreach (mysqli_query($conn, $sql) as $row) {
    $result[] = [
        'post_id' => $row['ID'],
        'user_id' => $row['post_author'],
        'post' => $row['post_content'],
        'firstName' => $row['first_name'],
        'lastName' => $row['last_name'],
        'username' => $row['user_nicename'],
        'avatar' => $row['avatar_hash'],
        'post_date' => $row['post_date'],
        'post_type' => $row['post_type'],
        'post_title' => $row['post_title'],
        'post_name' => $row['post_name'],
        'post_modified' => $row['post_modified'],
    ];
}
echo json_encode($result, JSON_PRETTY_PRINT);
?>

So, is there a way I can add the featured image URL inside my JSON API? Any answer will be appreciated!

Answer 1

To get a featured image you need _thumbnail_id and from that id, you can get the image URL. _thumbnail_id you can find in the post meta table. try the below query.

header("Content-Type: application/json");
require_once 'wp-connect.php';

$result = [];
$sql = <<<SQL
SELECT *,
    (
        SELECT guid
        FROM   wp_posts
        WHERE  id = wp_postmeta.meta_value
    ) AS image 
FROM wp_users
INNER JOIN wp_posts ON wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post'
INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.id AND wp_postmeta.meta_key = '_thumbnail_id'
INNER JOIN (
    SELECT user_id,
           MAX(IF(meta_key = 'first_name', meta_value, NULL)) AS first_name,
           MAX(IF(meta_key = 'last_name', meta_value, NULL)) AS last_name,
           MAX(IF(meta_key = 'avatar_hash', meta_value, NULL))AS avatar_hash
    FROM wp_usermeta
    GROUP BY user_id
) pivoted ON pivoted.user_id = wp_users.ID
ORDER BY wp_posts.ID;
SQL;

foreach ( mysqli_query($conn, $sql) as $row ) {
    $result[] = [
        'post_id'       => $row['ID'],
        'user_id'       => $row['post_author'],
        'post'          => $row['post_content'],
        'firstName'     => $row['first_name'],
        'lastName'      => $row['last_name'],
        'username'      => $row['user_nicename'],
        'avatar'        => $row['avatar_hash'],
        'post_date'     => $row['post_date'],
        'post_type'     => $row['post_type'],
        'post_title'    => $row['post_title'],
        'post_name'     => $row['post_name'],
        'post_modified' => $row['post_modified'],
        'image'         => $row['image']
    ];
}