Generate .json file with PHP MySQL

Question

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);

You are calling mysql_query() twice. And the second time, when you try to get the result set, you are calling it on the result from the first $sql. Turn on error reporting and display_errors, and you'll see PHP complain about that. — Michael Berkowski
– Michael Berkowski, Commented May 22, 2013 at 23:23
What @MichaelBerkowski said, also you assign $url and $title to the array $row. — Nikola
– Nikola, Commented May 22, 2013 at 23:24

Tobias G. · Accepted Answer · 2013-05-22 23:24:42Z

3

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);

answered May 22, 2013 at 23:24

Tobias G.

1181 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qebas Over a year ago

I tried your code, and it returns several nulls. Like -> {"posts":[{"url":null,"title":null},...

phponwebsites · Accepted Answer · 2015-04-29 11:43:48Z

1

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

answered Apr 29, 2015 at 11:43

phponwebsites

6717 silver badges9 bronze badges

Comments

km6zla · Accepted Answer · 2013-05-22 23:23:07Z

0

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);
}

answered May 22, 2013 at 23:23

km6zla

4,9272 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Generate .json file with PHP MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related