mysql don't post dupes to table

Question

So I have a database with a table of movies with their respective directors. I have a form where one can add a director to a movie, but I want to not add duplicates (i.e. someone adds a director to a movie that is already in the movie director table.

I have the following, but for the life of me, I cannot figure out why this still adds the duplicate:

if (isset($_POST["submit"])) {
    $movie_id = $_POST[movie];
    $director_id = $_POST[director];
    echo $movie_id;
    echo '<br/>';
    echo $director_id;
    echo '<br/>';


    $add_sql = "INSERT IGNORE INTO MovieDirector (mid, did)
                VALUES ($movie_id, $director_id)";

    if (mysql_query($add_sql, $db_connection)){
        echo "added director to movie ";
    } 
    else { 
        echo "Failed "; 
    }

Possible duplicate of [Best way to avoid duplicate entry into mysql database](http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database) — Tim Biegeleisen, Oct 30 '15 at 05:50

Mike Dinescu · Answer 1 · 2015-10-30T05:56:29.207

I think you are confused about what the IGNORE modifier keyword does for INSERT statements in MySQL. The role of IGNORE is not to prevent duplicate records from happening, but to make errors silent.

So, there are multiple ways you could go about this:

you could modify your schema to not allow duplicate records and either continue to use the IGNORE keyword, or better yet, handle theerrors

ALTER TABLE MovieDirector ADD UNIQUE INDEX(mid, did);

you could also modify your query to not insert a record if one already exists

IF NOT EXISTS(SELECT * FROM MovieDirector WHERE mid = $movieId AND did = $directorId)
INSERT INTO MovieDirector (mid, did) VALUES ($movieId, $directorId)

You probably want both of the above (probably minus the IGNORE keyword.. that's just going to come and bite you later)

This answer would be incomplete if I did not stress that you should really be using parametrized queries instead of adding the variables directly into the query string!

This answer would also be incomplete if we did not stress to STOP. USING. `mysql_*`!!!! It's been deprecated for some time, and outright removed in PHP7. Seriously, there's a [giant red message](http://php.net/manual/en/function.mysql-query.php) explaining this in the documentation. — maiorano84, Dec 05 '15 at 21:40

mysql don't post dupes to table

1 Answers1