CODEWITHSUNDEEP
phpmysql
shone83
shone83

Reputation: 23

PHP MYSQL prevent duplicates based on certain columns

For example, I have a table:

ID FName   LName  Street     Number Phone   Note
1  John    Smith  6th Ave    17     6465165 some random text
2  Peter   Murphy Barker Ave 22     6545213 some random text
3  Michael Adams  Centre St  34     5451351 some random text

How can I prevent duplicate entry John Smith who live in 6th Ave 17, but allow some John Smith who live in Devoe Ave for example?

I have more columns that this, but I want to prevent duplicate only based on FName, LName, Street and Number...

Upvotes: 0

Views: 103

Answers (2)

Dan Ionescu
Dan Ionescu

Reputation: 3423

Create an unique index on FName, LName, Street and Number... This will make the insert statement that would have inserted a duplicate fail, you can then catch the exception in your code and do something when this happens.. I've updated my answer to include a PDO example:

<?php
$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "your password";

$link = new PDO("mysql:host=$dbhost;dbname=$dbname","$dbusername","$dbpassword");

$statement = $link->prepare("INSERT INTO testtable(fname, lname, street, number)
        VALUES(:fname, :lname, :street, :number)");

try {
    $statement->execute(
        array(
            'fname' => 'John',
            'lname' => 'Smith',
            'street' => '6th Ave',
            'number' => '17',
        )
    );
} catch (PDOException $e) {
    if ($e->errorInfo[1] == 1062) {
        // duplicate entry
    } else {
        //other error
    }
}

Upvotes: 1

syed
syed

Reputation: 289

First step would be to set a unique key on the table like below:

ALTER TABLE table ADD UNIQUE KEY (FName,LName, Street, Number)

and then add data into it using INSERT IGNORE. any INSERTS leading to a duplicate entry in these two columns combined will now return a MySQL error instead of going through.

NOTE : INSERT statement would be quietly disregarded after using UNIQUE.

Upvotes: 0

Related Questions