Generate .json file with PHP MySQL

I’m trying to generate a json file with PHP with the code below and I’m getting an empty array -> {“posts”:[]}. I’m using WordPress. Can someone assist me. Thanks

    $sql=mysql_query("SELECT * FROM wp_posts"); 

    $response = array();
    $posts = array();
    $result=mysql_query($sql);
    while($row=mysql_fetch_array($result)) 
    { 
    $url['url']=$row; 
    $title['title']=$row;

    $posts[] = array('url'=> $url, 'title'=> $title);

    } 

    $response['posts'] = $posts;

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($response));
    fclose($fp);

Related posts

Leave a Reply

3 comments

  1. There are many mistakes in your code! Please check this:

    $result=mysql_query("SELECT * FROM wp_posts");
    
    $i=0;
    while($row=mysql_fetch_array($result)) { 
    $response[$i]['url']  = $row['url']; 
    $response[$i]['title']= $row['title'];
    $data['posts'][$i] = $response[$i];
    $i=$i+1;
    } 
    
    $json_string = json_encode($data);
    
    $file = 'file.json';
    file_put_contents($file, $json_string);
    
  2. Try this

    header('Content-Type: application/json');    
    $sql=mysql_query("SELECT * FROM wp_posts"); 
    $response = array();
    $posts = array();
    $result=mysql_query($sql);
    while($row=mysql_fetch_array($result)) 
    { 
      $posts['url']=$row['url'];; 
      $posts['title']=$row['title'];
      array_push($response, $posts);
    } 
    $json = json_encode($response);
    $file = 'file.json';
    file_put_contents($file, $json);
    

    Reference: visit here

  3. Problem is in your while loop. Try this:

    while($row=mysql_fetch_array($result)) { 
        $url=$row['url']; 
        $title=$row['title'];
    
        $posts[] = array('url'=> $url, 'title'=> $title);
    }