Insert Rows with using the result from a group of select best way to do it

Question

I have an array of tags inserted in my database, and I need to insert them in another table with itemID and tagID.

The thing is that i don't have the tagID - instead I have a TagName with the name. I need to get the tagID so I can insert it afterward, but I'm wondering if this is possible to achieve in one single query.

I mean search for the name, then get its tagID and then insert it the array, or even inserting them one by one would work.

unless the TagNames are unique you will get back many possible TagIDs when you try to get it. If the Names are unique it begs the question why isnt it the PK? Otherwise this is too broad. — Ňɏssa Pøngjǣrdenlarp, Feb 16 '15 at 00:43
yes TagName is declared as unique. it is a table from joomla k2 its how the tags work in there im just trying to import a batch to the database directly. — , Feb 16 '15 at 01:15

score 0 · Answer 1 · answered Feb 16 '15 at 02:46

if I understood you, my ways is: create two tables first. If you know better way, please let me know.

Table Tag

id keyword

Table C_Tag

id tag_id user_id

Foreign key Tag(id) Reference C_Tag (tag_id)

<?php
$tag=$_POST['tag_name'] //get the tag name
$current_user=$_POST['users_id'];// get the current user id
include 'db_tag.php';

$stmt = $db->prepare("SELECT keyword, id FROM Tag WHERE keyword = :tag "); 

$stmt->bindParam(':tag', $tag);
$stmt->execute();
$row_tag = $stmt->fetchALL(PDO::FETCH_ASSOC);
foreach ($row_tag as $row_tag){
}

//if the keyword exist, only update the C_Tag table
if ($row_tag['term'] == $tag){
  $stmt = $db->prepare("SELECT t.id, t.keyword, ct.tag_id, ct.user_id FROM Tag t , C_Tag ct WHERE t.id=ct.tag_id  ");
  $stmt->bindValue(':current_id',$current_user);
  $stmt->execute(); 
  $row = $stmt->fetchALL(PDO::FETCH_ASSOC);
  foreach ($row as $row){

  }

  $row_id=$row['id'];
  $row_tag_id=$row['tag_id'];
  $row_user_id=$row['user_id'];

  if( $current_user == $row_user_id && $row_id == ! $row_tag_id ){
//make sure same user cannot enter duplicate keyword
$stmt = $db->prepare ("INSERT INTO C_Tag (user_id, tag_id) 
  SELECT :current_id, id 
  FROM tag
  WHERE keyword=:keyword 
");
$stmt->bindValue(':current_id',$current_user);
$stmt->bindValue(':keyword', $tag); 
$stmt->execute(); 
  }
} else {
  //if the keyword is new, I will insert it into both table
}

?>

Thanks for r time conan, well basicaly u have 2 tables, one holds int TagID, varchar TagName the other table holds int TagID, Int ItemID; I have the itemID and also the TagName but i need the TagID from the Tagname I managed to make it with multiple queries but it was way slow what i need is a way to speedup this process making it more eficiently. — , Feb 16 '15 at 07:31

score 0 · Answer 2 · edited Feb 06 '18 at 04:37

this appears to be the solution

How to copy a row and insert in same table with a autoincrement field in MySQL?

insert into zr1f4_k2_tags_xref (id, tagID, itemID) select NULL, tagID, @itemID from zr1f4_k2_tags where name=@tagName

but i cant make it work.

this are the tables

zr1f4_k2_tags
id          int(11)
name        varchar(255)
published   smallint(6)

zr1f4_k2_tags_xref
id      int(11)
tagID   int(11)
itemID  int(11)

I'm trying to insert the ID of the tag related to the tagname and item ID which ill add explicitly. and the id is autonumeric

Insert Rows with using the result from a group of select best way to do it

2 Answers2