MySQL GROUP_CONCAT bug. why my queries with GROUP_concat not work

Question

SELECT CONCAT(`date`,',',`viewcount`) 
FROM `stat` 
WHERE `stat`.`id` = 1 
  AND `channelstat`.`date` BETWEEN (SELECT DATE_SUB(NOW(), INTERVAL 90 DAY)) AND NOW()

This query work well and gave me result of last 2 days in CSV kind of format.

I think to generate whole csv file in MySQL. I tried to write my command to do it. When I tried GROUP_CONCAT it's not work well. Now I got rows from date less then 4 August. I am amazed because first queries make it from less then today.

SELECT GROUP_CONCAT(CONCAT(`date`,',',`viewcount`) SEPARATOR '\r\n') 
FROM `stat` 
WHERE `stat`.`id` = 1 
  AND `stat`.`date` BETWEEN (SELECT DATE_SUB(NOW(), INTERVAL 90 DAY)) AND NOW()

in second queries the last 2 line are which is totally invalid

2013-08-04,798
2013-08-

Somebody please tell me why it's not work. Why last line is different when rows are available from today to last 4 months.

The second queries have bugs like

invalid group concat or I am missing something.

Zagor23 · Accepted Answer · 2013-08-30 07:59:30Z

1

I think that your result gets truncated by GROUP_CONCAT.

As the documentation for GROUP_CONCAT says:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

answered Aug 30, 2013 at 7:59

Zagor23

1,96312 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcel Gwerder · Accepted Answer · 2013-08-30 07:50:39Z

0

Please try it:

$res=$mysqli->query("SELECT id,GROUP_CONCAT(client_id) as clients FROM services WHERE id = 3 GROUP BY id");
$row = $res->fetch_array(MYSQLI_ASSOC);
$result = explode(',', $row['clients']); // $row['clients'] contains string 5,6,7
$res->free();

edited Aug 30, 2013 at 7:50

Marcel Gwerder

8,5406 gold badges40 silver badges63 bronze badges

answered Aug 30, 2013 at 7:29

Gautam Tak

1291 silver badge7 bronze badges

Collectives™ on Stack Overflow

MySQL GROUP_CONCAT bug. why my queries with GROUP_concat not work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related