SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data type sql table limits

    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:
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by 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/...n_group-concat
    helps?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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

  5. #5
    SitePoint Member
    Join Date
    May 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok Sorry I got it working, I used ORDER BY wi.imgZindex directly in the Group_Concat of img.
    thanks all.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •