0

Is there a limit on a value string in php Associative array?

I have a query that returns value of GROUP_CONCAT as CSV for an array element. When I run the query directly, I see all values being returned. When I print the array in PHP, the string is truncated.

Not sure how get around it? Any ideas?

More info: I just var_dump the results I got from query. Here it is. It shows 1024. Looks like there is a limit. What is it?

  array(5) {
   ["FIELD_ID"]=>
      string(3) "232"
   ["answers"]=>
      string(1024) "48,50,52,54,56,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1042,1043,1044,1045,1046,1047,1048,1049,1050,1051,1052,1053,1054,1055,1056,1057,1058,1059,1060,1061,1062,1063,1064,1065,1066,1067,1068,1069,1070,1071,1072,1073,1074,1075,1076,1077,1078,1079,1080,1081,1082,1083,1084,1085,1086,1087,1088,1089,1090,1091,1092,1093,1094,1095,1096,1097,1098,1099,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1122,1123,1124,1125,1126,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1179,1180,1181,1182,1183,1184,1185,1186,1187,1188,1189,1190,1191,1192,"
     ["ANSWER_TYPE"]=>
     string(2) "SS"
   ["FIELD_TYPE"]=>
     string(2) "DA"
   ["main_field_name"]=>
    string(0) ""
  }

More Info: The issue still exists even after setting the variable group_concat_max_len to 5000. Any other ideas? Any issues with PDO?

After restarting MySQL, it works. Thanks!

Kevin Rave
  • 13,876
  • 35
  • 109
  • 173
  • 2
    GROUP_CONCAT has a limit in mysql, php limit is theoretically is max memory limit. Show us a code – mrok Aug 12 '12 at 22:17
  • When I run the query, it shows complete CSV. – Kevin Rave Aug 12 '12 at 22:20
  • how do you "print the array in PHP" - ex var_dump cuts data for readability. Any code example is still welcome. – mrok Aug 12 '12 at 22:24
  • I tried both var_dump and print_r. Both print the same. My validation is failing because of this. – Kevin Rave Aug 12 '12 at 22:26
  • try echo strlen($your_array['answers']) - 1024 is just length of that string. It is the same as string(3) "232" - 3 characters in string – mrok Aug 12 '12 at 22:32

3 Answers3

1

You might need to read up on the length of the max concat here.

Command-Line Format        --group_concat_max_len=#
Option-File Format         group_concat_max_len
Option Sets Variable       Yes, group_concat_max_len
Variable Name              group_concat_max_len
Variable Scope  Global, Session

Dynamic Variable           Yes
  -- Permitted Values
Platform Bit Size          32
Type                       numeric
Default                    1024
Range                      4 .. 4294967295

  -- Permitted Values
Platform Bit Size          64
Type                       numeric
Default                    1024
Range                      4 .. 18446744073709547520

Edit: I find it rather amusing that the string that is returned to you is 1024 in length - which just happens to be the default max length in mysql. Coincedence?

Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • I guess, this might be the issue. I need to update this setting and se if it works. However, when I run the query in phpMyAdmin, it shows complete string, though. – Kevin Rave Aug 12 '12 at 22:37
  • The issue still exists even after setting the above variable to 5000. Any other ideas? – Kevin Rave Aug 14 '12 at 05:52
1

When you're using GROUP_CONCAT in mysql with the default length limit of 1024, anything above that in your results will get cut off. This happened to me as well.

To determine if this is the issue, try this:

// echo string length to determine what your variable value length is
echo strlen($variable_here);

Then check what the concat limit is set to in mysql by executing this query: 

SHOW VARIABLES LIKE 'group_concat_max_len'

I ran it in phpMyAdmin. If you get the same value from strlen and the SHOW VARIABLES query, you'll need to update your my.conf file or ask your host to do it. In WHM/cPanel, it is located in /etc/my.conf and in that file you'll need to have something like this:

[mysqld]  #<-- Under that
group_concat_max_len=5120  #<-- add this

I chose 5x1024 to make it 5120 just to not have to deal with it again. Once you've saved the conf file back to the server, you'll need to restart the mysql service. If you're using WHM, you can do that under Home » Restart Services » SQL Server (MySQL)

Once I did the above, the strlen did in fact echo out 3519 and the value was no longer cut off.

Chris
  • 893
  • 10
  • 23
0

Your question is somewhat tricky to answer becuase: 1. you give very little information on the actual size of your string. 2. You're asking several questions (array size and string size).

You should check out this question and then supply us with extra information like which version of php you're using and also as @FLuffeh says in his comment please show us some code.

Community
  • 1
  • 1
Daniel Figueroa
  • 10,348
  • 5
  • 44
  • 66