GROUP_CONCAT multiple fields with a different separator in ARRAY

Question

The following code is working perfectly :

$aColumns = array( "t.tablename", "r.book_hours", 
    "GROUP_CONCAT(CASE WHEN r.reserveday = CURDATE() THEN r.formtime ELSE NULL END ORDER BY r.formtime ASC) AS oldBookTime");

But when I add multiple fields from this tutorial StackTutorial

I edit the code become :

$aColumns = array( "t.tablename", "r.book_hours", 
    "GROUP_CONCAT(CASE WHEN r.reserveday = CURDATE() THEN r.formtime, '-', r.book_hours ELSE NULL END ORDER BY r.formtime ASC) AS oldBookTime");

It shows error:

Query 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 ' '-'

Is it I have to escape , '-', ? How to escape it?

Answer 1

It isn't escaping that is the problem, is that the value following THEN must be a single scalar value, not a list of values.

Change this:

GROUP_CONCAT(CASE WHEN r.reserveday = CURDATE() 
THEN r.formtime, '-', r.book_hours 
ELSE NULL END ORDER BY r.formtime ASC) AS oldBookTime

To this:

GROUP_CONCAT(CASE WHEN r.reserveday = CURDATE() 
THEN CONCAT(r.formtime, '-', r.book_hours) 
ELSE NULL END ORDER BY r.formtime ASC) AS oldBookTime

(I inserted linebreaks for the sake of formatting this answer, but they're optional.)

Answer 2

In the CASE statement, you're using 3 results in the THEN branch. This is syntactically wrong. Here you can find the documentation for the CASE statement.

If I get what you want to achieve, you can use the CONCAT() function (documentation here) to concat your values, this way:

$aColumns = array( "t.tablename", "r.book_hours", 
    "GROUP_CONCAT(CASE WHEN r.reserveday = CURDATE() THEN CONCAT(r.formtime, '-', r.book_hours) ELSE NULL END ORDER BY r.formtime ASC) AS oldBookTime");