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!
CodePudding user response:
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']
];
}