What is the correct way to write data in 2 related tables?

Question

PHP

Good evening.
There are 2 tables: tags & post_tags .
I know only 2 ways to write tags to the database:
1

$tags = array_map('trim', explode(',', $_POST["tags"])); // A,B,C,
$tags_array = array();
$tags_sql = $db->super_query("SELECT id, title FROM `tags` WHERE title REGEXP '" . implode('|', $tags) . "'", true);

foreach($tags_sql as $tag){
$tags_array[$tag["title"]] = $tag["id"];
}

foreach($tags as $tag) {
  $tag = trim($tag);
  if (isset($tags_array[$tag])){
    $db->query("INSERT INTO `post_tags` (post_id, tag) VALUES ('$post_id', '{$tags_array[$tag]}') ");
  }
  else {
    $db->query("INSERT INTO `tags` (title) VALUES ('$tag') ");
    $id = $db->insert_id()-1;
    $id++;
    $db->query("INSERT INTO `post_tags` (post_id, tag) VALUES ('$post_id', '$id') ");
  }
}

2

$tags = array_map('trim', explode(',', $_POST["tags"])); // A,B,C,
$tags_array = array();
$tags_sql = $db->super_query("SELECT id, title FROM `tags` WHERE title REGEXP '" . implode('|', $tags) . "'", true);

foreach($tags_sql as $tag){
$tags_array[$tag["title"]] = $tag["id"];
}

$gg_tags = array();
foreach($tags as $tag) {
  $tag = trim($tag);
  if (isset($tags_array[$tag])){
    $gg_tags["exist"][] = "('$post_id', '{$tags_array[$tag]}')";
  }
  else {
    $gg_tags["new_title"][] = "('$tag')";
  }
}
if (!empty($gg_tags["exist"])){
  $db->query("INSERT INTO `_post_tags` (post_id, tag) VALUES " . implode(",", $gg_tags["exist"]) . " ");
} 
else {
  $db->query("INSERT INTO `tags` (title) VALUES " . implode(",", $gg_tags["new_title"]) . " ");
  $tags_sql = $db->super_query("SELECT id, title FROM `tags` WHERE title REGEXP '" . implode('|', $tags) . "'", true);

  foreach($tags_sql as $tag){
    $gg_tags["new_id"][] = "('$post_id', '{$tag["id"]}')";
  }
  $db->query("INSERT INTO `post_tags` (post_id, tag) VALUES " . implode(",", $gg_tags["new_id"]) . " ");
}

As a result, the table should look like this:
tags
id | title
1 | A
2 | B
3 | C
post_tags
post_id | tag
1 | 1
1 | 2
1 | 3

1. I would not like to use the option for the fact that many queries are made, and in addition to tags, there are many more tables for which the same design is needed.
   
2. I'm not sure if the option is correct. If it's still correct, I'm not sure that this is the best option even if it produces fewer requests (according to my calculations).
   

I don't know if I'm trying to do it right or not, are these 2 options the only ones or are there others?
I would be grateful for advice - help!

Answer 1

If I understood you correctly, then you:
1. have an array of tags $tags = array("tag1", "tag2", "tag3") at the input
2. want to save new ones to the database
3. want to get the id of all tags and assign them to some post
Try this:
1. INSERT IGNORE INTO `tags` (title) for all tags, those that are will be ignored
2. SELECT id FROM `tags` WHERE `title` IN (...) for all tags you need
3 INSERT IGNORE INTO `post_tags` for all id's received from the second request
At least this will reduce the amount of code and make it easier to understand.
+ add a caching layer to store tags that have already been added to the database (there won’t be too many of them, after all)

Answer 2

The problem was solved in this way:

$tags = $db->safesql( htmlspecialchars( strip_tags( stripslashes( trim( $_POST['tags'] ) ) ), ENT_COMPAT, $config['charset'] ) );
$tags = array_filter(array_map('trim', explode(',', $tags)));
$tags_box = array();

$all_post_tags = $db->super_query("SELECT tags.*, post_tags.* FROM " . PREFIX . "_tags tags LEFT JOIN " . PREFIX . "_post_tags post_tags ON (tags.id=post_tags.tag) WHERE post_tags.post_id=$post_id", true);

foreach($all_post_tags as $tag){

  $tags_box["all_post_tags"][] = $tag["title"];
  $tags_box["all"][$tag["title"]] = $tag["tag"];

  if (!in_array($tag["title"], $tags)){
    $tags_box["remove"][] = $tag["id"];
  }

  $tag["posi"]--;
  $tags_box["upd_posi"] = ($tags[$tag["posi"]] == $tag["title"]) ? 0 : 1;
  $tag["posi"]++;
  $tags_box["upd_posi_indexes"][$tag["title"]] = $tag["posi"];
  $tags_box["upd_posi_id"][] = $tag["id"];

}
$tag_id = 0;
foreach($tags as $tag_posi => $tag_title){

  $tag_posi++;
  $tag_id++;

  if (!in_array($tag_title, $tags_box["all_post_tags"])){

    $tags_box["insert_new_tags_title"][] = "('$tag_title')";
    $tags_box["insert_new_tags_id"][] = $tag_title;
    $tags_box["post_tags"][] = "('$post_id', '{$tag_id}', '$tag_posi')";
    
  }
  $tags_box["positions"][$tag_title] = $tag_posi;
  if (isset($tags_box["upd_posi_indexes"][$tag_title])){	
    $tags_box["upd_posi_new"][] = $tags_box["upd_posi_indexes"][$tag_title];
  }
}
if (!empty($tags_box["remove"])){
  $db->query("DELETE FROM " . PREFIX . "_post_tags WHERE post_id='$post_id' AND tag REGEXP '".implode('|', $tags_box["remove"])."'");
} 
if (!empty($tags_box["insert_new_tags_title"])){

  $finded_tags = $db->super_query("SELECT * FROM " . PREFIX . "_tags WHERE title REGEXP '" . implode('|', $tags_box["insert_new_tags_id"]) . "'", true);

  foreach($finded_tags as $tag){

    if (in_array($tag["title"], $tags) && $tags_box["all"][$tag["title"]] != $tag["title"]){
      $tags_box["finded"][] = "('$post_id', '{$tag["id"]}', '{$tags_box["positions"][$tag["title"]]}')";
    }

  }
  if (!empty($tags_box["finded"])){
    $db->query("INSERT INTO " . PREFIX . "_post_tags (post_id, tag, posi) VALUES " . implode(",", $tags_box["finded"]) . "");
  }
  else {		
    $db->query("INSERT INTO " . PREFIX . "_tags (title) VALUES " . implode(",", $tags_box["insert_new_tags_title"]) . "");
    $db->query("INSERT INTO " . PREFIX . "_post_tags (post_id, tag, posi) VALUES " . implode(",", $tags_box["post_tags"]) . " ");
  }
}
if ($tags_box["upd_posi"]){
  $db->query("UPDATE " . PREFIX . "_post_tags SET posi = ELT(field(tag, ".implode(',', $tags_box["upd_posi_id"])."), ".implode(",", $tags_box["upd_posi_new"]).") WHERE tag REGEXP '".implode('|', $tags_box["upd_posi_id"])."' AND post_id=$post_id");
}

I would be grateful if someone could provide some alternative!