5

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!

2

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
)
  • 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. – mickmackusa Feb 3 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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