Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2514
  • Last Modified:

Mysql group_concat() alternative in 3.23.x

I need a functional alternative to the group_concat function since its not possible for me to upgrade to 4.1 at this time.

Im creating a temporary table that stores all keyword_ids of all relevant item_ids for that site, format is item_id is PRIMARY and keyword_ids is a comma seperated list of keyword ids (eg 1,2,3). Each item has sub-properties , each sub-property has its own record in the sub-property db and is related via an item_id col. in that sub-property db, the keyword_ids col. is in the sub-property db. I need to have keyword_ids be a list of all the keyword_ids for that item, the query I have now is working fine, however its returning multiple rows for each item_id.

-------------------------------
id | item_id | keyword_ids
-------------------------------
1  |1          | 1,2,3          
2  |1          | 1,2            
3  |1          | 1                
4  |1          | 4,5,6          
5  |2          | 7                
6  |2          | 1,4,2          
7  |2          | 6,3,4            
-------------------------------

Im getting that by this...
CREATE TEMPORARY TABLE (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY  (id),KEY item_id (item_id)) TYPE=MyISAM SELECT DISTINCT item_id,keyword_ids FROM my_item_joined_db WHERE site_id = x

(my_item_joined_db = few left joins that grab the particular keyword_ids for the items on that partikcular section of the site).

What I need is to have that temp table only have one row per item_id (ie GROUP BY item_id) with all the keyword_ids for that item_id concat'ed together (ie for item_id 1 keyword_ids would be 1,2,3,1,2,1,4,5,6). I need to do this all via mysql, is there a way to do it via creating another temp table and dumping from another select, etc.? Thanks.
0
jcsuperska
Asked:
jcsuperska
  • 3
  • 2
1 Solution
 
psadacCommented:
It works well on mysql 4.0.18 (it should work with 3.X versions also) :

SELECT @prev_item_id := NULL;

SELECT  MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),
             @prev_item_id := item_id
FROM mytable
GROUP BY item_id;

very standard sql, no ? ;-)
0
 
jcsuperskaAuthor Commented:
That sounds like it should work great, unfortunatly mysql is returning this error and any help with this would be greatly appreciated.

Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'

That mean Im gonna need to upgrade to 4.0.18 or anything look obviously wrong?
0
 
psadacCommented:
can you post your complete query and the error message ?
0
 
jcsuperskaAuthor Commented:
Query is:
SELECT @prev_item_id := NULL;CREATE TEMPORARY TABLE IF NOT EXISTS keyword_table_site_2 (id bigint(20) unsigned NOT NULL auto_increment,PRIMARY KEY (id),KEY item_id (item_id)) TYPE=MyISAM SELECT items.id as item_id,MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids)),@prev_item_id := item_id FROM sub_items LEFT JOIN items ON sub_items.item_id = items.id WHERE items.site_id = 2 AND items.site_date <= NOW() GROUP BY item_id

MySQL said:
Incorrect column name 'MAX(@keyword_ids := IF(@prev_item_id = item_id, CONCAT_WS(',', @keyword_ids, keyword_ids), keyword_ids))'

Thank you for your help.
0
 
jcsuperskaAuthor Commented:
Your solution as I thought was absolutly correct. Only issue was being caused by the fact that I was selecting the results into a TEMP table and the col names were not being aliased. So when I added as keyword_ids to the MAX(....) it worked like a charm. Always the simple things that seem to be the cause , thanks again for helping me get the group_concat() or bust thinking out of my head =), just had to get the wheels turning.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now