CODEWITHSUNDEEP
mysql
Jay Julian Payne
Jay Julian Payne

Reputation: 780

MySQL Query - Concatenate & Merge Tags

Our articles database has two tag-system tables:

1) tags contains:
tag_id
text (like politics)

2) tagmaps contains:
tag_id
article_id

I want to list all tags for each article (eg: "politics diplomacy al gore") and insert them in the keywords field in our search table (using article_id & keywords fields).

I know I can do this in phpmyadmin, but not sure how...

Upvotes: 0

Views: 410

Answers (2)

Jay Julian Payne
Jay Julian Payne

Reputation: 780

From a friend:

update engine4_core_search left join (select resource_id as tagid , group_concat(engine4_core_tags.text SEPARATOR ', ') as keywords from engine4_core_tagmaps left join engine4_core_tags on engine4_core_tagmaps.tag_id=engine4_core_tags.tag_id group by resource_id) keywordstable on engine4_core_search.id=keywordstable.tagid set engine4_core_search.keywords=keywordstable.keywords where engine4_core_search.type='artarticle' and engine4_core_search.id <10

That will update 10 rows....

Upvotes: 0

Taryn
Taryn

Reputation: 247650

If you want the data to be returned in the same field, then you can use GROUP_CONCAT():

select m.article_id,
    group_concat(t.text) tags
from tags t
inner join tagmaps m
    on t.tag_id = m.tag_id
group by m.article_id

Result from this would be:

article_id  | tags
1           | tag1, tag2, tag3

Then if you want them inserted into you table:

insert into search (article_id, keywords)
select m.article_id,
    group_concat(t.text)
from tags t
inner join tagmaps m
    on t.tag_id = m.tag_id
group by m.article_id

If you don't want the text values in the same field:

insert into search (article_id, keywords)
select m.article_id,
    t.text
from tags t
inner join tagmaps m
    on t.tag_id = m.tag_id

If you are trying to UPDATE the search table, then you can use:

update search
set keywords = (select  group_concat(t.text separator ', ') tags
                from tags t
                inner join tagmaps m
                    on t.tag_id = m.tag_id
                where search.article_id = m.article_id
                group by m.article_id);

See SQL Fiddle with Demo

Upvotes: 2

Related Questions