Having an issue updating an intermediate table when an item already exists

Question

I am using a three table structure to deal with a many-to-many relationship. I have one table that has a list of people and another that has a list of items. Sometimes multiple people have the same item and sometimes multiples items are linked to the same person so I set up the following table structure:

CREATE TABLE people (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
fname varchar(128) NOT NULL,
lname varchar(128) NOT NULL,
);

CREATE TABLE items (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(128) NOT NULL UNIQUE,
);

The UNIQUE prevents the item name from repeating.

CREATE TABLE people_items (
pid int(11) NOT NULL,
iid int(11) NOT NULL,
FOREIGN KEY (pid) REFERENCES people(id)
ON UPDATE CASCADE
ON DELETE CASCADE
FOREIGN KEY (iid) REFERENCES items(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

This allows me to link multiple items to multiple people and vice versa. It also allows me to delete unnecessary records from the intermediate table.

All works fine so long as a new item is entered, but if an existing item is entered, the intermediate table is not updated, even though the people table is. I also do not get any errors.

Items are a comma delimited text entry which are exploded and lower cased into $items.

First I insert any new items and retrieve the id:

for ($i=0;$i<count($items);$i++){
$sql="INSERT IGNORE INTO items (name) VALUES (?);";
$stmt=$conn->prepare($sql);
$stmt->bind_param('s',$items[$i]);
$stmt->execute();
$itemid=$stmt->insert_id;

If a new id is returned the following is executed:

if ($itemid){
$sql="INSERT INTO people_items (pid,iid) VALUES (?,?);";
$stmt=$conn->prepare($sql);
$stmt->bind_param('ii',$peopleid,$itemid);//the $peopleid is acquired the same way that the $itemid is acquired above
$stmt->execute();
}

Up to here, everything works just fine. At this point if an existing item is already in the items table, is where my intermediate table does not update. The people table however updates just fine, and the items table does not need to update as it already has the item in it.

Here is where I tried two different approaches to update the intermediate table.

First I kept the select and insert queries separate.

elseif(!$itemid){
$sql="SELECT id,name FROM items WHERE name=?;";
$stmt=$conn->prepare($sql);
$stmt->bind_param('s',$items[$i]);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($iid,$name);
$stmt->fetch();
$sql="INSERT INTO people_items (pid,iid) VALUES (?,?);";
$stmt=$conn->prepare($sql);
$stmt->bind_param('ii',$pid,$iid);
$stmt->execute();
}

Here is my alternative approach which also does not update the intermediate table:

elseif(!$itemid){
$sql="INSERT INTO people_items (pid, iid) SELECT id,name FROM items WHERE name IN (?);";
$stmt=$conn->prepare($sql);
$stmt->bind_param('s',$items[$i]);
$stmt->execute();
}

What am I doing wrong?

Answer 1

Sorry I didn't get to this sooner, but I have figured it out and was busy with work in the meantime. I figure I'd provide the answer in case anyone has the same issue in the future. Anyway, the issue was with my SQL query.

Where I had this:

elseif(!$itemid){
$sql="INSERT INTO people_items (pid, iid) SELECT id,name FROM items WHERE name IN (?);";
$stmt=$conn->prepare($sql);
$stmt->bind_param('s',$items[$i]);
$stmt->execute();
}

I should have had this:

elseif(!$itemid){
$sql="INSERT INTO people_items (pid,iid) VALUES (?,(SELECT id FROM items WHERE name IN (?)));";
$stmt=$conn->prepare($sql);
$stmt->bind_param('ss',$peopleid,$items[$i]);
$stmt->execute();
}