kirk112 Posted October 2, 2007 Share Posted October 2, 2007 Hi, I am trying to group fields together, which I have managed using GROUP_CONCAT, but when I concat on a SMALLINT datatype the results is return as ls_id locations 1 [bLOB - 2 Bytes] 2 [bLOB - 2 Bytes] instead of ls_id locations 1 '1', '2' 2 '1', '3' This is the query that I have been using. SELECT ls_id, GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'') AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id Does anyone know where I am going wrong? Thanks for your help! Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/ Share on other sites More sharing options...
effigy Posted October 2, 2007 Share Posted October 2, 2007 I don't think DISTINCT can be used in that manner. What if you move it after SELECT? Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360069 Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 Hi effigy If I take out the distinct altogether I still have the same problem, I.E. ls_id locations 1 [bLOB - 10 Bytes] 2 [bLOB - 2 Bytes] Cheers Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360073 Share on other sites More sharing options...
fenway Posted October 2, 2007 Share Posted October 2, 2007 I don't think DISTINCT can be used in that manner. What if you move it after SELECT? Acutally, it is valid that way... but try removing all parameters to GROUP_CONCAT(). Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360074 Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 I have taken it all the way back to SELECT GROUP_CONCAT(ls_id) FROM ip_locations AS ip WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id And I am still getting the same blob result Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360076 Share on other sites More sharing options...
fenway Posted October 2, 2007 Share Posted October 2, 2007 Then there's something wrong with the way php is handling this... that's php output, not mysql output. Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360125 Share on other sites More sharing options...
Barand Posted October 2, 2007 Share Posted October 2, 2007 GROUP_CONCAT(ls_id) and you GROUP BY ls_id ? ??? ??? Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360250 Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 Hi Barand I have removed the GROUP BY ls_id and I am still getting the same incorrect results I have been running this query in phpMyAdmin. If I run this query and replace GROUP_CONCAT(ls_id) to a varchar field (instead of an smallint) it run correctly, does anyone have any idea on where to start debugging this I have been going around in circles for days Thanks! Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360402 Share on other sites More sharing options...
Barand Posted October 2, 2007 Share Posted October 2, 2007 Don't you want to group locations for each ls_id? SELECT ls.ls_id, GROUP_CONCAT(DISTINCT ip.location SEPARATOR ',') as locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls.ls_id Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360415 Share on other sites More sharing options...
kirk112 Posted October 2, 2007 Author Share Posted October 2, 2007 No idea why this works but if I change the original query to SELECT ls_id, CONCAT('\'', GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id it works perfect, but a soon as I remove the first concat, it reverts back to giving me all the results as a blob. Any ideas why?? Thanks Barand for your help, got myself mixed up with the fields that I wanted to select, should be able to work it out after I have gotten some sleep. Thanks again everyone! Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360433 Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 Ah yes, I forgot about that bug... came across that one a long time ago while using an ODBC driver using an older version of the connector (not that I can find any refererence to it anymore). Apparently, it gets confused and "forgets" to stringify it. Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360799 Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Hi Fenway, thanks for your response explained a lot. Just one more thing when using group_concat it always retrieves one row even if the where does not match any fields, I can over come this by using 'having length' but this seems a little bit messy, just wondering if there is a better way to achieve this? SELECT CONCAT('\'',GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip WHERE ip.ip_code = 'jkj' HAVING length(locations) > 0 Again thanks for your time Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360832 Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 What do you mean? If there's no match, you shouldn't get any rows back, group_concat() or not... Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360926 Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Everytime there is no match I still get a NULL row returned???? Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360935 Share on other sites More sharing options...
fenway Posted October 3, 2007 Share Posted October 3, 2007 Everytime there is no match I still get a NULL row returned???? NULL row? From just a single table? Sounds implausible... and select * doesn't give you a null row? Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-360946 Share on other sites More sharing options...
kirk112 Posted October 3, 2007 Author Share Posted October 3, 2007 Hi fenway, I have attached a screen shot of this, I really don't understand why a NULL row is returned every time, if I run select * it returns an empty data set. [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-361144 Share on other sites More sharing options...
fenway Posted October 4, 2007 Share Posted October 4, 2007 Hmmm... maybe all aggregate function always return a single row, like count, sum, avg, etc... and group_concat behaves like one of these too! Link to comment https://forums.phpfreaks.com/topic/71521-group_concat/#findComment-362024 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.