mysql group_concat to JSON in ZF - results repeating escape backslashes

Question

Connecting an app to a ZF 1.12 LAMP stack with MySQL 5.6.14 with PHP 5.4.24

I have a long query that is mostly single row results with a couple of multiple row results hence I'm using a group_concat for the multi row results.

I need the results in JSON format - so I'm using the following SQL concat query to output JSON format from MySQL directly - using $select = $this->_db->quoteInto("SELECT...")

The rest of the query / results are fine, so I'll only list the problem line:

GROUP_CONCAT(DISTINCT CONCAT('{name: \"',name,'\", age:\"',age,'\"}') ORDER BY age ASC SEPARATOR '|') AS person_details,

When I explode (using my '|' separator) the results I get look like this:

"person_details": [
            "{name: \"Fred\", age:\"17\"}",
            "{name: \"James\", age:\"23\"}"
],

...with the escape characters repeated in the result...

If I don't include the '\' escape characters the query doesn't work

I've tried :

• the identical query without backslashes in phpMyAdmin and it works fine (ie the results come out in correct JSON format without repeating the included '\' escape characters)
• testing both "$select = $this->_db->select()" and "$select = $this->_db->quoteInto("formats for query in ZF - both give the same result

Answer 1

have you tried to use " instead ' ?

GROUP_CONCAT(DISTINCT CONCAT("{name: \"",name,"\", age:\"",age,"\"}") ORDER BY age ASC SEPARATOR "|") AS person_details,