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:

json_logo-555px

    $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