PHP MYSQL prevent duplicates based on certain columns

Question

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...

Give the collums an unique index.

node_modules
– node_modules

2017-02-22 12:30:13 +00:00
Commented Feb 22, 2017 at 12:30 — node_modules
– node_modules, Commented Feb 22, 2017 at 12:30

Dan Ionescu · Accepted Answer · 2017-02-22 20:54:32Z

1

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
    }
}

edited Feb 22, 2017 at 20:54

answered Feb 22, 2017 at 12:34

Dan Ionescu

3,4711 gold badge14 silver badges17 bronze badges

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

2 Comments

shone83 Over a year ago

How to catch the exception in my code? For example, to echo "User exist!"?

Dan Ionescu Over a year ago

glad i could help

syed · Accepted Answer · 2017-02-22 12:41:46Z

0

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.

answered Feb 22, 2017 at 12:41

syed

2891 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL prevent duplicates based on certain columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related