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:


id, title


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


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.


1 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 */

/* Query 4: Insert tags for each article that contains in the title the word mercedes */
INSERT INTO `#__k2_tags_xref` (`tagID`,`itemID`)
    FROM `#__k2_items` AS i
    WHERE LOCATE('mercedes', title) > 0
  • Why do you use DISTINCT in the subquery? Is the table alias necessary? I thought not. Also, > 0 can be removed. If name in #__k2_tags is declared as a UNIQUE KEY then LIMIT 1 can be omitted. It might be good to mention UNIQUE KEYS in the schemas. Feb 3, 2019 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.