I used to loop through my MySQL queries, in my server side language of
choice, to build JSON. But there is a far better way that will save you
some coding, add to simplicity and might even save some valuable server
time. If you're running MySQL 4.1 or later you can use the nifty function
GROUP_CONCAT() together with the normal CONCAT() function to build all
your JSON straight from your SQL query.
| username |
email |
| tommy |
tommy@tommysplace.com |
| jane |
jane@examples.com |
| jack |
jack@iford.com |
Our SQL table.
SELECT
CONCAT("[",
GROUP_CONCAT(
CONCAT("{username:'",username,"'"),
CONCAT(",email:'",email),"'}")
)
,"]")
AS json FROM users;
A MySQL-query that returns JSON.
[
{username:'tommy',email:'tommy@tommysplace.com'},
{username:'jane',email:'jane@examples.com'},
{username:'jack',email:'jack@jiford.com'}
]
The returned JSON structure. 
You could build XML as well Of course you could use GROUP_CONCAT() to build XML as well (or HTML
tables for that matter). If you want to read up on how to disable the
default comma separator, order things inside the group_concat etc
you'll find all the information you need in the
MySQL manual, section 12.10.1.
Slashdot It!
Digg 
