I have a lot of entries without tags, I want to assign a specific tag to such entries. If a specific word is found in the title of a record, then a certain tag is assigned to it. I think this can be done through a SQL query, but I don’t imagine how to implement this, because the tags and records are in different tables. That is, you need to find records that match the templates, "take" their id , and add this id to the tags table.

  • нужно найти записи, соответствующие шаблонУУУУ, "взять" их id, и этот id добавить в таблицу тегов. Probably so. - Smash
  • But how about doing it? - Lexpex

1 answer 1

Let's make the question less abstract, we introduce three tables:

  • names - records
  • tags - tags
  • tags_names - link table of records and tags

Here is the minimum database dump with these tables.

 CREATE TABLE `names` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `names` (`id`, `name`) VALUES (1, 'Статья'), (2, 'PHP'), (3, 'Статья по MySQL'); CREATE TABLE `tags` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tags` (`id`, `name`) VALUES (1, 'php'), (2, 'mysql'); CREATE TABLE `tags_names` ( `id` int(11) NOT NULL NULL AUTO_INCREMENT, `tag_id` int(11) NOT NULL, `name_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `tags_names` (`id`, `tag_id`, `name_id`) VALUES (1, 1, 1), (2, 2, 1);

We have three articles, two tags, and only one article is associated with both tags. Two other articles should be related to their tags, but this connection is not. This is easily verified with the following query (only the first tag is displayed)

 SELECT names.id AS name_id, names.name AS name, tags.name AS tags FROM names LEFT JOIN tags_names ON names.id = tags_names.name_id LEFT JOIN tags ON tags.id = tags_names.tag_id GROUP BY names.id; +---------+-------------------------+------+ | name_id | name | tags | +---------+-------------------------+------+ | 1 | Статья | php | | 2 | PHP | NULL | | 3 | Статья по MySQL | NULL | +---------+-------------------------+------+

In order to link articles with tags, let's first find articles without tags:

 SELECT names.id AS id, names.name AS name FROM names LEFT JOIN tags_names ON names.id = tags_names.name_id WHERE tags_names.tag_id IS NULL; +----+-------------------------+ | id | name | +----+-------------------------+ | 2 | PHP | | 3 | Статья по MySQL | +----+-------------------------+

You can link the received articles with tags using the LIKE operator. We will have to dynamically create a template for LIKE using the function

 `CONCAT()`. SELECT names.id AS name_id, names.name AS name, tags.id AS tag_id, tags.name AS tag FROM names LEFT JOIN tags_names ON names.id = tags_names.name_id LEFT JOIN tags ON names.name LIKE CONCAT('%', tags.name, '%') WHERE tags_names.tag_id IS NULL; +---------+-------------------------+--------+-------+ | name_id | name | tag_id | tag | +---------+-------------------------+--------+-------+ | 2 | PHP | 1 | php | | 3 | Статья по MySQL | 2 | mysql | +---------+-------------------------+--------+-------+

As can be seen from the results of the previous query, the “PHP” tag was assigned the php tag, and the MySQL article was tagged with the “mysql” tag. Now we just have to write the result using the INSERT ... SELECT ...

 INSERT INTO tags_names SELECT NULL, tags.id AS tag_id, names.id AS name_id FROM names LEFT JOIN tags_names ON names.id = tags_names.name_id LEFT JOIN tags ON names.name LIKE CONCAT('%', tags.name, '%') WHERE tags_names.tag_id IS NULL;

Now, if we request a list of articles and their corresponding tags, we can make sure that they are assigned correctly.

 SELECT names.id AS name_id, names.name AS name, tags.name AS tags FROM names LEFT JOIN tags_names ON names.id = tags_names.name_id LEFT JOIN tags ON tags.id = tags_names.tag_id GROUP BY names.id; +---------+-------------------------+-------+ | name_id | name | tags | +---------+-------------------------+-------+ | 1 | Статья | php | | 2 | PHP | php | | 3 | Статья по MySQL | mysql | +---------+-------------------------+-------+