handling duplicate records in mysql Insert statement

Question

Im i have a php form to enter data into a mysql database, my question is how do i handle duplicate records for example if an ID number already exsists how do i check if exsists then if it does throw an error message, for expale saying "ID is already in the database",here is an example of an insert statement, however i would like it to check the database for duplicate records before entering

$values = $_POST;
foreach ($values as &$value) {
    $value = mysql_real_escape_string($value);
}

$sql1="INSERT INTO loan (loan_id)
VALUES ('$values[loan_id]')";

$result = mysql_query($sql1);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

user1191247 · Accepted Answer · 2012-04-15 14:31:13Z

1

As long as there is a suitable UNIQUE KEY or PRIMARY KEY on the field that needs to be unique you will receive an error when you try to insert the duplicate record. You can then test for this error in your code.

$values = $_POST;
foreach ($values as &$value) {
    $value = mysql_real_escape_string($value);
}

$sql1="INSERT INTO loan (loan_id) VALUES ('$values[loan_id]')";

$result = mysql_query($sql1);
if (!$result) {
    if (mysql_errno() == 1586) {
        echo "ID is already in the database";
    } else {
        die('Invalid query: ' . mysql_error());
    }
}

answered Apr 15, 2012 at 14:31

user1191247

13k2 gold badges25 silver badges33 bronze badges

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

Comments

Nesim Razon · Accepted Answer · 2012-04-15 14:30:11Z

0

do a select like : select count(loan_id) from load where load_id=$values[loan_id] than if result>0 don't insert and give error message "ID is already in the database" to user, otherwise insert it.

answered Apr 15, 2012 at 14:30

Nesim Razon

9,8043 gold badges38 silver badges48 bronze badges

Comments

Andreas Linden · Accepted Answer · 2012-04-15 14:33:34Z

0

eigther check before inserting or do and update.

check

"SELECT COUNT(*) FROM loan WHERE loan_id = '" . $values['load_id'] . "'"

update

"INSERT INTO loan (loan_id, some_field) VALUES ('". $values['loan_id'] ."', '". $values['some_field'] ."') ON DUPLICATE KEY UPDATE some_field = VALUES(some_field)";

answered Apr 15, 2012 at 14:33

Andreas Linden

12.8k7 gold badges54 silver badges70 bronze badges

Comments

garnertb · Accepted Answer · 2012-04-15 14:35:49Z

0

Add a unique index constraint on the loan_id column. MySQL will throw an error when your application attempts to insert another row with the same value.

SQL will look similar to:

CREATE UNIQUE INDEX loan_id_index ON table_name (loan_id);

edited Apr 15, 2012 at 14:35

answered Apr 15, 2012 at 14:30

garnertb

9,62439 silver badges38 bronze badges

Collectives™ on Stack Overflow

handling duplicate records in mysql Insert statement

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related