K2 Add tag to article if the keyword is in the article title

Question

This seems to be a bit out of my league and knowledge of MySQL. So if someone can help it would be much appreciated.

I would like to do the following:

1. Enter/define a keyword e.g. mercedes
2. Find all K2 articles that have that keyword in the title
3. Than to each of those articles assign my keyword mercedes as a tag.

Now... There are three tables with relevant columns listed:

k2_items

id, title

k2_tags

id, name, published (value 1 is assigned if tag is published)

k2_tags_xref

id, tagID, itemID

So, query should select all items from k2_items table that have keyword in their title, check if keyword is already defined as tag in k2_tags, if not than create a new tag. After that, new k2_tags_xref entry should be generated to connect keyword tag with K2 article item.

I still didn't have database course on my university so I'm kind of out of my league with this one, and it was supposed to be just a simple touchup for the site I'm developing.

Any help with this would be much appreciated, and I'm sure it'll help community later on.

Thanks!

Answer 1

There are a couple of queries that you need to execute. Please use these queries first on a test environment. There is an idea:

/* Query 1: Select the id of the tag */ 
SELECT id FROM `#__k2_tags` WHERE `name` = 'mercedes' LIMIT 1;

/* Query 2: If previous query returned null, create a new tag */
INSERT INTO `#__k2_tags`
SET `name` = 'mercedes',
`published` = 1;

/* Query 3: Use this to get the last id or use id from Query 1 */
SELECT LAST_INSERT_ID();

/* Query 4: Insert tags for each article that contains in the title the word mercedes */
INSERT INTO `#__k2_tags_xref` (`tagID`,`itemID`)
(
    SELECT DISTINCT 'YOUR_TAG_ID', i.id
    FROM `#__k2_items` AS i
    WHERE LOCATE('mercedes', title) > 0
)