group_concat(A,' ',B,' ',C) as Name,
then using this php for displaying
<td><?php echo $row['Name']; ?></td>
using this query returns Name X,Y
but i prefer to have the names not comma separated rather line break X then Y new line
Any idea?
-
group_concat(A,'\\n ',B,'\\n ',C) as Name,Joy Acharya– Joy Acharya2020-03-19 06:33:58 +00:00Commented Mar 19, 2020 at 6:33
Add a comment
|
4 Answers
For MySQL (or plain text) output You could use \n as a separator:
SELECT GROUP_CONCAT(column1 SEPARATOR '\n') FROM table1;
I use this very often when I need to get many new-line-separated values in one row for other processing.
1 Comment
akenney
separator '\r\n' exported the way I wanted for ExcelI figured it out. this is the correct way to add line break as seperator in the browser:
group_concat(A,' ',B,' ',C separator '<br>') as Name,
Comments
I don't understand what do you mean by line break between X and Y, but if you need the values to be comma separated you can add any separator to the Group_Concat like that:
group_concat(Name SEPARATOR ' ') as Name
and here is some other separators you can use.
5 Comments
buni
I wanted to use </br> so that the next name goes to a new line
Álvaro González
The separator is just a string. HTML tags have no special meaning in SQL.
Mahmoud Gamal
@ÁlvaroG.Vicario absolutely, but i didn't no why it returns null when i try it???
Thomas B
For those looking for how to insert a line break it can be done if you're generating the query pragmatically. For vb.net you would do something like, "SELECT group_concat(field SEPARATOR '" & vblf & "') FROM table"
Progrower
Also make sure in your html that your not using <?=htmlspecialchars($obj['name']) ?> you have to use <?php echo $obj['name'];?> This stumped me for a long time trying to get a line break in a GROUP_CONCAT.
SELECT Name, GROUP_CONCAT(city SEPARATOR '\n') AS city, nb
FROM
(SELECT 'A' AS Name, 'Agra' AS city, 101 AS nb
UNION ALL
SELECT 'B' AS Name, 'Delhi' AS city, 102 AS nb
UNION ALL
SELECT 'A' AS Name, 'Allahabad' AS city, 101 AS nb) AS a
GROUP BY Name, nb;
