How to return ID of existing row if there is a duplicate row during MySQL INSERT?

Question

I have two tables: movies and genres, and a junction table movieOfGenre for many-to-many relationships between the movies and genres tables. Now, when I am using transactions to insert movies and genres into my database using PHP, genres were being created twice even if there was a duplicate. Then I updated the code with ...ON DUPLICATE KEY UPDATE... and now if there is an existing genre with the same name, the auto_increment ID of the existing genre is being updated when I use this transaction query:

mysqli_autocommit($connect, false);
$query_success = true;
$stmt = $connect->prepare("INSERT INTO movies (id, title, plot, rating, releaseDate, duration, language, country, posterUrl, trailerUrl) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("ssssssssss", $defaultId, $title, $plot, $rating, $releaseDate, $duration, $language, $country, $poster, $trailer);
if (!$stmt->execute()) {
    $query_success = false;
}
$movieId = mysqli_insert_id($connect);
$stmt->close();

foreach ($genres as $genre) {
    $stmt = $connect->prepare("INSERT INTO genres (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID()");
    $stmt->bind_param("ss", $defaultId, $genre);
    if (!$stmt->execute()) {
        $query_success = false;
    }
    $genreId = mysqli_insert_id($connect);
    $stmt->close();

    $stmt = $connect->prepare("INSERT INTO movieofgenre (movieId, genreId) VALUES (?, ?)");
    $stmt->bind_param("ii", $movieId, $genreId);
    if (!$stmt->execute()) {
        $query_success = false;
    }
    $stmt->close();
}

if ($query_success) { 
    mysqli_commit($connect);
} else { ?>
    alert('Error adding movie.');
    <?php mysqli_rollback($connect);
}

How do I write the query so that if during insertion there is an existing row with the same genre.name, it returns the ID of the existing row without updating it?

Thanks in advance.

You cannot get that value directly from the insert. But you can simply query it (run another select after you inserted it instead of querying for mysqli_insert_id). Btw, it is a bad idea to update the id (e.g. because all entries in movieofgenre for that genre will become useless or have to be updated). — Solarflare
– Solarflare, Commented Jul 21, 2017 at 17:23

Toby Speight · Accepted Answer · 2017-07-28 14:12:39Z

2

genres.name should be a unique column:

CREATE TABLE genres
...
UNIQUE KEY name

use IGNORE - duplicate exception will not be thrown
```
INSERT IGNORE INTO genres (name) VALUES (?)
```
get the id by name:
```
SELECT id FROM genres WHERE name = ?
```

edited Jul 28, 2017 at 14:12

Toby Speight

32.4k58 gold badges83 silver badges118 bronze badges

answered Jul 21, 2017 at 18:11

Inna Tichman

6266 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fernan Vecina · Accepted Answer · 2017-07-21 17:36:17Z

1

I'm not sure this help but you may want to check example below. Lazy to do php coding this days, But I have similar issue with MYSQL last time if I understand your issue correctly :P

INSERT INTO sample_table (unique_id, code) VALUES ('$unique_id', '$code')
ON DUPLICATE KEY UPDATE code= '$code'

Hope this resolve your problem

answered Jul 21, 2017 at 17:36

Fernan Vecina

1631 silver badge9 bronze badges

Collectives™ on Stack Overflow

How to return ID of existing row if there is a duplicate row during MySQL INSERT?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related