PHP mySql data to JSON file

Question

So, i need to query my database by php and then convert the query into a .json file to use Google Charts.

How can i convert a mysql query through PHP to a .json file somewhat like this:

{
  cols: [{id: 'A', label: 'NEW A', type: 'string'},
         {id: 'B', label: 'B-label', type: 'number'},
         {id: 'C', label: 'C-label', type: 'date'}
        ],
  rows: [{c:[{v: 'a'}, {v: 1.0, f: 'One'}, {v: new Date(2008, 1, 28, 0, 31, 26), f: '2/28/08 12:31 AM'}]},
         {c:[{v: 'b'}, {v: 2.0, f: 'Two'}, {v: new Date(2008, 2, 30, 0, 31, 26), f: '3/30/08 12:31 AM'}]},
         {c:[{v: 'c'}, {v: 3.0, f: 'Three'}, {v: new Date(2008, 3, 30, 0, 31, 26), f: '4/30/08 12:31 AM'}]}
        ],
  p: {foo: 'hello', bar: 'world!'}
}

PS: this example is quoted from google

Nikhil Mohan · Accepted Answer · 2013-06-26 12:07:58Z

11

You can use json_encode function.

Fetch data from db and assign it to an array
Then use json_encode($result_array). This will produce the json result. Click here
Use file_put_contents function to save the json result to your .json file

Following is an example code,

$result = mysql_query(your sql here);    
$data = array();
while ($row = mysql_fetch_assoc($result)) {
    // Generate the output in desired format
    $data = array(
        'cols' => ....
        'rows' => ....
        'p' => ...
    );
}

$json_data = json_encode($data);
file_put_contents('your_json_file.json', $json_data);

edited Jun 26, 2013 at 12:07

answered Jun 26, 2013 at 10:26

Nikhil Mohan

8916 silver badges13 bronze badges

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

1 Comment

Correia JPV Over a year ago

exactly.get the result from json_encode($array) and send it to a .json file so i can get it later to output in a google chart

Necktwi · Accepted Answer · 2013-06-30 12:52:22Z

1

convert mysql table to php object using mysql_fetch_object and then to json with json_encode

mysql_fetch_object fetches row. so use loop to construct a table object.

code:

$result = mysql_query("select * from mytable");
$table=array();
while($row=$mysql_fetch_object($result)){
  $table.push($row);
  unset($row);
}
echo json_encode($table);

edited Jun 30, 2013 at 12:52

answered Jun 26, 2013 at 10:30

Necktwi

2,6758 gold badges43 silver badges70 bronze badges

Collectives™ on Stack Overflow

PHP mySql data to JSON file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related