Data type sql table limits

[codename_jan]

Hi I created a relations sql statement between three tables, the problem is that my table tx and img is limited to only 1024 characters, is there a way I can change the data typ to display more than that, like use type = TEXT??

This is my statment:

SELECT
CONCAT('{"weID":"',wb.weID, ',"size_h":"',wb.size_h, ',"size_w":"', wb.size_w,'","text":[') as bs,

CONCAT (GROUP_CONCAT(DISTINCT'{"weID":"',wt.weID,'","txtID":"',wt.txtID, '","txtText":"', wt.txtText, '","txtColour":"', wt.txtColour,'"}')) as txt,

CONCAT(GROUP_CONCAT(DISTINCT '{"weID":"',wi.weID,'"',',"imgName":"',wi.imgName,'","imgName":"', wi.imgName,'","imgMTX5":"', wi.imgMTX5,'","imgMTX6":"',wi.imgMTX6,'","imgWidth":"',wi.imgWidth,'","imgHeight":"', wi.imgHeight,'","imgZindex":"', wi.ImgZindex,'"}'))as img

FROM wb, wt, wi WHERE wb.weID = wt.weID and wb.weID = wi.weID GROUP BY wb.weID ORDER BY GROUP_CONCAT(DISTINCT wb.weID) ASC

[r937]

@da manual

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;

– http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

helps?

[codename_jan]

thank you so much that did the job. what I did was changed the vale of group_concat_max_len from my.ini permanently, that worked very well.

thanks once again.

[codename_jan]

Sorry to start this thing up agin, but I am having problems using the ORDER BY on wi.ImgZindex, please have a look at my sql, I have added the ‘ORDER BY’ to wi.ImgZindex ASC but that does not work, I tried ASC and DESC but the table does not change what so ever the only thing I can see that it seams to change the order of the entire structure relavent to wb.weID.

please let me know how I can achieve this, thank you.

SELECT
CONCAT('{"weID":"',wb.weID, ',"size_h":"',wb.size_h, ',"size_w":"', wb.size_w,'","text":[') as bs,

CONCAT (GROUP_CONCAT(DISTINCT'{"weID":"',wt.weID,'","txtID":"',wt.txtID, '","txtText":"', wt.txtText, '","txtColour":"', wt.txtColour,'"}')) as txt,

CONCAT(GROUP_CONCAT(DISTINCT '{"weID":"',wi.weID,'"',',"imgName":"',wi.imgName,'","imgName":"', wi.imgName,'","imgMTX5":"', wi.imgMTX5,'","imgMTX6":"',wi.imgMTX6,'","imgWidth":"',wi.imgWidth,'","imgHeight":"', wi.imgHeight,'","imgZindex":"', wi.ImgZindex,'"}'))as img

FROM wb, wt, wi WHERE wb.weID = wt.weID and wb.weID = wi.weID GROUP BY wb.weID ORDER BY GROUP_CONCAT(DISTINCT wb.weID) AND wi.ImgZindex ASC

[codename_jan]

ok Sorry I got it working, I used ORDER BY wi.imgZindex directly in the Group_Concat of img.
thanks all.