CODEWITHSUNDEEP
phpmysqlgroup-concat
Anto S
Anto S

Reputation: 2449

GROUP_CONCAT() mysql returns limited characters

I have to two tables

tblColorLibrary

id   name
1     test color

tblColors

id  libraryId  colorCode  name
1     1           #fff     Prime Color
2     1           #ddd     Secondry Color
3     1           #E2CFC7  Favorite Color

Below is my query:

$stmt ="SELECT a.id, a.isActive as isActive, a.name as title, GROUP_CONCAT(b.colorCode ) as colors, GROUP_CONCAT(b.name) as name FROM ".$this->tblLibrary." as a JOIN tblcolors as b ON a.id = b.libraryId GROUP BY a.id ORDER BY b.id ASC";

This query will return result like this 

Array
(
    [0] => Array
        (
            [id] => 1
            [isActive] => Y
            [title] => test
            [colors] => #fff,#ddd,#E2CFC7
            [name] => Prime Color, Secondry Color, Favorite Color
        )

)

All goes fine till I have limited records. When I have above 150 records in tblColors, name key gives only limited number of characters. Not getting full records.

I guess there will be limitation in group concat.

Upvotes: 0

Views: 1570

Answers (2)

Dobromir Velev
Dobromir Velev

Reputation: 520

Check the value of group_concat_max_len and increase it as needed.

show variables like 'group_concat_max_len';

Upvotes: 1

Saty
Saty

Reputation: 22532

Increase your group_concat_max_len at mysql database. by default it set to 1024. you can update it by using query

SET GLOBAL group_concat_max_len=100000

and 

SET SESSION group_concat_max_len = 1000000;

Upvotes: 2

Related Questions