MySQL to Json

[crocodilu2008]

Why this not working ?
Our SQL table.

username   email

tommy 	tommy@example.com
jane  	jane@example.com
jack  	jack@example.com

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{username:'",username,"'"),
               CONCAT(",email:'",email),"'}")
          )
     ,"]") 
AS json FROM users;

A MySQL-query that returns JSON.

[
     {username:'tommy',email:'tommy@example.com'},
     {username:'jane',email:'jane@example.com'},
     {username:'jack',email:'jack@example.com'}
]

I found this here Mysql to Json

Thanks

[Atli]

Hi.

How is it not working?
Are you getting any error messages?

We can't really help unless we know what the problem is.
Simply knowing that there is a problem is of very little use.

[crocodilu2008]

Always i have this error
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
AS json FROM users' at line 7"

[Atli]

The expression you use with the GROUP_CONCAT function is invalid.

You are basically doing this:
[code=mysql]GROUP_CONCAT("s tring", "string")[/code]
while the function expects something more like this:
[code=mysql]GROUP_CONCAT("e xpression")[/code]

To use the CONCAT functions like you do, you would have to enclose them both in their own CONCAT function, so that they returned a single string to the GROUP_CONCAT function.

Like:
[code=mysql]
GROUP_CONCAT(
CONCAT(
CONCAT("{userna me:'", username, "'"),
CONCAT(",email: '", email, "'}")
)
)[/code]