Converting results from a SQL query to JSON format, is an important task if you are developing an iOS application which grab data from your web application.
For this tutorial I'm going to create a new database, named jsontest, in MySQL.
CREATE DATABASE IF NOT EXISTS `jsontest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `jsontest`;
Then I'm going to create a simple table:
CREATE TABLE IF NOT EXISTS `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`surname` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
Last, let me populate the table with some data:
INSERT INTO `people` (`id`, `name`, `surname`, `age`) VALUES
(1, 'pippo', 'Mr Pippo', 108),
(2, 'Paperino', 'Mr Paperino', 109);
It's time to write some code.
The following code make a query to the database and then print out the results into a table.
<html> <head> <title>SQL To JSON Test</title> </head> <body> <?php $host = "localhost"; $user = "root"; $pass = "root"; $database = "jsontest"; $connection = mysql_connect($host, $user, $pass) or die("Could not connect to host."); mysql_select_db($database, $connection) or die("Could not find database."); $result = mysql_query("SELECT * FROM people"); echo "<table border='0'> <tr> <th>name</th> <th>surname</th> <th>age</th> </tr>"; while($row = mysql_fetch_array($result)) { print "<tr><td>".$row['name']."".$row['surname']."".$row['age']."</td></tr>"; } echo "</table>"; mysql_close($connection); ?>
It's time to output results in JSON format, I'm going to modify a few lines of code after the mysql_query statement:
$rows = array(); while($row = mysql_fetch_array($result)) { $rows[] = $row; print "<tr><td>".$row['name']."".$row['surname']."".$row['age']."</td></tr>"; } echo "</table>"; print json_encode($rows); mysql_close($connection);
Yeah! really nothing more.
Gg1
Hi. Thank you for this great tutorial. Could you please help me by showing me how I could format the php file to output the following data just as it is.
{
“timeline”:
{
“headline”:”Headline”,
“type”:”default”,
“text”:”Subhead”,
“startDate”:”2012,1,26″,
“date”: [
{
“startDate”:”2011,12,12″,
“endDate”:”2012,1,27″,
“headline”:”test headline”,
“text”:”Lorem ipsum dolor sit amet, consectetuer adipiscing elit,”,
“asset”:
{
“media”:””,
“credit”:””,
“caption”:””
}
},
{
“startDate”:”2012,1,26″,
“endDate”:”2012,1,27″,
“headline”:”another headline”,
“text”:”test this is just a test.”,
“asset”:
{
“media”:”http://youtube.com”,
“credit”:””,
“caption”:””
}
}
I really appreciate your help