CODEWITHSUNDEEP
phpmysql
sadmansh
sadmansh

Reputation: 927

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

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.

Upvotes: 0

Views: 149

Answers (2)

Inna Tichman
Inna Tichman

Reputation: 626

  1. genres.name should be a unique column:

    CREATE TABLE genres
...
UNIQUE KEY name

  2. use IGNORE - duplicate exception will not be thrown 

    INSERT IGNORE INTO genres (name) VALUES (?)

  3. get the id by name:

    SELECT id FROM genres WHERE name = ?

Upvotes: 2

Fernan Vecina
Fernan Vecina

Reputation: 153

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

Upvotes: 1

Related Questions