GROUP_CONCAT multiple fields with a different separator in ARRAY-CodePudding

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?

CodePudding user response：

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.)

CodePudding user response：

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");