With PHP MySQL Query retrieve fetch all WordPress Blog categories in JSON REST API

PHP MySQL database query for fetching categories data in JSON Format.

 

I run my WordPress website with MySQL database so need to fetch the database categories post data in JSON format and use it on another website created with REST API. In the below code, I will provide MySQL queries that can run on the WordPress MySQL database to get a list of published posts in the published category. Also, include the $_GET method key for anyone who can not access the code or hit unnecessary hits on databases.

 

Get all categories id, post title, name, and image path in JSON format code with PHP and MySql query.

Below MySql query for retrieving categories from wp_posts, wp_terms, wp_postmeta.

<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, DELETE, HEAD, PUT');
header("Access-Control-Allow-Headers: X-Requested-With");

$key = $_GET['key'];
if(empty($key) || ($key != '8edca156-080b-4aa1-884b-11f15d8tygva156')){
	$response=array(
				'status' => 0,
				'status_message' =>'Access Denied.'
			);
		header('Content-Type: application/json');
		echo json_encode($response);
			exit;
}
// Database configuration

$dbHost     = "localhost";
$dbUsername = "XXXXXXXXXXXX";
$dbPassword = "XXXXXXXXXXXX";
$dbName     = "XXXXXXXXXXXX";

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
if ($db -> connect_errno) {
  echo "Failed to connect to MySQL: " . $db -> connect_error;
  exit();
}
	
$request_method=$_SERVER["REQUEST_METHOD"];
	switch($request_method)
	{
		case 'GET':
			// Retrive Post
			if(!empty($_GET["id"]))
			{
				$product_id=intval($_GET["id"]);
				get_post($product_id);
			}
			else
			{
				get_post();
			}
			break;
		default:
			// Invalid Request Method
			header("HTTP/1.0 405 Method Not Allowed");
			break;
	}
    
    function get_post()
	{
		global $db;
          
          $getCategary = $_POST['categaryValue'];         
            echo $getCategary;
        if($getCategary == ''){
            $getCategary = 'News';
        }
        
     //   echo $getCategary;
          
			$query = $db->query("SELECT wp_posts.id,
       wp_posts.post_title,
       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_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  25");
        	
		$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);
		
}
?>