0

I am trying to generate a JSON file from a mysql database by using PHP. So far, I have:

<?php

error_reporting(-1);

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

$i=0;
while($row=mysql_fetch_array($result)) { 
$response[$i]['post_status']  = $row['post_status']; 
$response[$i]['post_title']= $row['post_title'];
$data['posts'][$i] = $response[$i];
$i=$i+1;
} 

$json_string = json_encode($data);

$file = 'file.json';
file_put_contents($file, $json_string);
?>

This will create the file.json file but the file only contains "null".

user715564
  • 1,650
  • 2
  • 25
  • 60

3 Answers3

0

Try something like this.

error_reporting(-1);

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

$data = array();

while ($row = mysql_fetch_array($result)) {
    $data['posts']['post_status'][] = $row['post_status'];
    $data['posts']['post_title'][] = $row['post_title'];
}

$json_string = json_encode($data);

$file = 'file.json';
file_put_contents($file, $json_string);
Ben Fortune
  • 31,623
  • 10
  • 79
  • 80
0

Random guess: json_encode expects UTF-8 encoded data and will exhibit the behavior you describe on any non-UTF-8, non-ASCII input. The data you're getting from the database is likely Latin-1 encoded.

Either set your database connection to utf8 to receive UTF-8 encoded data directly from the database (see UTF-8 all the way through), or use (and I hate to say this, because this function is so often abused it's not even funny, but it's correctly applied here) utf8_encode on all data you get from the database to convert it from Latin-1 to UTF-8.

So either:

// set the connection charset
mysql_set_charset('utf8');

$result = mysql_query("SELECT post_status, post_title FROM wp_posts");

$data = array();
while ($row = mysql_fetch_assoc($result)) { 
    $data['posts'][] = $row;
} 

$json_string = json_encode($data);

...

or:

$result = mysql_query("SELECT post_status, post_title FROM wp_posts");

$data = array();
while ($row = mysql_fetch_assoc($result)) { 
    $row = array_map('utf8_encode', $row);
    $data['posts'][] = $row;
} 

$json_string = json_encode($data);

...
Community
  • 1
  • 1
deceze
  • 510,633
  • 85
  • 743
  • 889
  • I tried both examples you provided but I am still not getting any data returned. The file only contains an empty set of brackets. – user715564 Oct 08 '13 at 14:00
  • Are you actually getting any data?! Have you tried `var_dump($data)`? – deceze Oct 08 '13 at 14:03
  • I did and it doesn't output anything. – user715564 Oct 08 '13 at 14:05
  • Then you're not getting any data from the database!? – deceze Oct 08 '13 at 14:07
  • Right, that's the problem. The script runs ok and creates the file but the only content in the file is an empty set of brackets. – user715564 Oct 08 '13 at 14:10
  • So your database table is empty...!? – deceze Oct 08 '13 at 14:10
  • Haha no it's not. That's why I am confused. I am trying to pull the data from a complete WordPress website. There is definitely data in the table. – user715564 Oct 08 '13 at 14:12
  • Are you connected to the right database? Have you debugged your script to see if the loop is executing at all? Have you checked for MySQL errors? What have you done to ensure that you're definitely pulling the right data from the right table? – deceze Oct 08 '13 at 14:13
-1

Most likely to be UTF-8 problem with special characters, try this

<?php

error_reporting(-1);

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

$i = 0;
while ($row = mysql_fetch_array($result)) {
    $response[$i]['post_status'] = htmlentities($row['post_status'],ENT_COMPAT, 'ISO-8859-1');
    $response[$i]['post_title'] = htmlentities($row['post_title'],ENT_COMPAT, 'ISO-8859-1');

    $data['posts'][$i] = $response[$i];
    $i = $i + 1;
}

$json_string = json_encode($data);

$file = 'file.json';
file_put_contents($file, $json_string);
?>
A-R
  • 81
  • 5