MySql query for fetch/ retrieve category data in WordPress with image thumbnail

by: myfreeonlinetoolsPosted on:

MySql query for fetch WP post_id, post_title, post_date & name with using thumbnail for a particular WordPress category.

WordPress also provides some methods for retrieving post data with thumbnails but you can also fetch them with MySQL query. Also, do with same with MySql join in array format. If you are required to display WordPress data on another website use the MySql query. Show below SQL query which you can show thumbnail image with post data.

 

$query = $db->query("SELECT wp_posts.id,
       wp_posts.post_title,
       wp_posts.post_date,
       wp_terms.name,
       (SELECT guid
        FROM   wp_posts
        WHERE  id = wp_postmeta.meta_value) AS image
FROM   wp_posts,
       wp_postmeta,
       wp_term_relationships,
       wp_terms
WHERE  wp_posts.id = wp_term_relationships.object_id
       AND wp_terms.term_id = wp_term_relationships.term_taxonomy_id
       AND wp_terms.name = 'Business Tips'
       AND wp_posts.post_status = 'publish'
       AND wp_posts.post_type = 'post'
       AND wp_postmeta.post_id = wp_posts.id
       AND wp_postmeta.meta_key = '_thumbnail_id'
ORDER  BY wp_posts.post_date DESC
LIMIT  8");

$response=array();
				
		while($row = $query->fetch_assoc())
		{
			$remove[] = "'";
			$remove[] = '"';
			//$remove[] = '?';
			$remove[] = chr(054);

			$response[] = str_replace( $remove, "", $row );
			//$response[] = mb_convert_encoding($row,'HTML-ENTITIES','UTF-8');
		}
		
		header('Content-type: application/json; charset=utf-8');
		echo json_encode($response);

Above code will learn you write custom PHP, MySql code to fetch blog post data with thumbnail. Its also use for writing WordPress plugins which requires data to be a beautiful way or you can also create REST API in JSON format to use in other websites or other website applications.