PHP 8.1 PDO Prepared Statements Not Binding Parameters Correctly with Nested Arrays
I'm learning this framework and I'm experiencing an scenario with PDO prepared statements in PHP 8.1 where I'm trying to insert data from a nested array into a MySQL database. My nested array structure looks like this: ```php $data = [ ['name' => 'John Doe', 'email' => 'john@example.com', 'tags' => ['developer', 'php']], ['name' => 'Jane Smith', 'email' => 'jane@example.com', 'tags' => ['designer', 'css']], ]; ``` I want to insert each user into a `users` table while also inserting their tags into a `tags` table and linking them via a `user_tags` many-to-many relationship table. My current insertion code looks like this: ```php $pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); foreach ($data as $user) { $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)"); $stmt->execute([':name' => $user['name'], ':email' => $user['email']]); $userId = $pdo->lastInsertId(); foreach ($user['tags'] as $tag) { $tagStmt = $pdo->prepare("INSERT INTO tags (tag) VALUES (:tag) ON DUPLICATE KEY UPDATE id=id"); $tagStmt->execute([':tag' => $tag]); $tagId = $pdo->lastInsertId(); $linkStmt = $pdo->prepare("INSERT INTO user_tags (user_id, tag_id) VALUES (:user_id, :tag_id)"); $linkStmt->execute([':user_id' => $userId, ':tag_id' => $tagId]); } } ``` The question arises because when I try to insert users with tags, I get an behavior `SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry` even though I'm using `ON DUPLICATE KEY UPDATE` for tags. The tags table has a unique index on the `tag` column, but it seems that the `$tagId` variable is not returning the ID of the existing tag if it already exists. Instead, it always returns the last inserted ID from the previous insert, which is causing the integrity constraint violation. I've also tried changing the way I handle `INSERT` for the tags by first checking if the tag exists or not before performing the insert, but that approach is less efficient because it would result in a select query for each tag. What can I do to properly bind the existing tag IDs when they already exist in the database? Any help or insight into improving this logic would be greatly appreciated! I'm developing on Ubuntu 22.04 with Php. What am I doing wrong?