Here is a quick post that demonstrates how to create some neat JSON data from MySQL using PHP. The script is really simple, and has been working well for me over the last few weeks.
If anything is confusing, it might be worth checking out the previous post about creating XML data from PHP. It explains things in more detail and will help if you are not familiar…
//set up the php headers so that the page doesnt cache etc header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate( "D, d M Y H:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: text/json"); $jsonArray->page = $page; $jsonArray->total = $totalPages; $jsonArray->records = $recordCount; $i=0; while ($row = $db->fetch_array($countRows)) { $jsonArray->rows[$i]['id']=$row['userAccountId']; $jsonArray->rows[$i]['userName']=$row['userName']; $jsonArray->rows[$i]['fullName']=$row['fullName']; $jsonArray->rows[$i]['accessCode']=$row['accessCode']; $jsonArray->rows[$i]['email']=$row['email']; $jsonArray->rows[$i]['addedBy']=$row['addedBy']; $jsonArray->rows[$i]['addedDate']=$row['addedDate']; $jsonArray->rows[$i]['modifiedBy']=$row['modifiedBy']; $jsonArray->rows[$i]['modifiedDate']=$row['modifiedDate']; $i++; } echo json_encode($jsonArray);
The above code will create the following style JSON output.
{"page":1,"total":1,"records":"3","rows":[ {"id":"1","userName":"demo","fullName":"Demo User","accessCode":"","email":"demo@myexample.com","addedBy":"1","addedDate":"2009-05-02 00:00:00","modifiedBy":"10","modifiedDate":"2009-06-04 00:00:00"}, {"id":"2","userName":"sam","fullName":"Sam Lasagne","accessCode":"","email":"sam@myexample.com","addedBy":"2","addedDate":"2009-05-02 00:00:00","modifiedBy":"2","modifiedDate":"2009-08-02 00:00:00"}, {"id":"10","userName":"adrian","fullName":"Adrian Spaghetti","accessCode":"","email":"adrian@myexample.com","addedBy":"1","addedDate":"2009-05-02 00:00:00","modifiedBy":"2","modifiedDate":"2009-09-01 00:00:00"} ]}