GROUP_CONCAT multiple fields with a different separator in ARRAY

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?

Contents hide

Answers:

Method 1

Method 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating