Database: Check for duplicate error

Question

I have a PHP code and a field in the database which is a unique field. If people fill in the form and if the $_POST['name'] is already in the database it gives an error.

That's what I have and want, but now I want to check if there's an error so I can handle it in a if / else statement.

This is my code:

$db = new database();
$sql = "INSERT INTO product_groepen (name) VALUES (".$_POST['name'].")";
$result = $db->executeQuery($sql);
if ($result)
{
    $db->executeQuery($sql);
    $page .= 'Yes';
} else {
    $page .= 'No';
}

The error:

Warning: PDO::query() [pdo.query]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 's' for key 2 in /classes/database.class.php on line 26

It works, and when it isn't working it says 'no', but the error remains.

You could possibly use the mysql insert ... on duplicate key update syntax - to get past this. — Fluffeh
– Fluffeh, Commented Sep 4, 2012 at 12:17
Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables. — eggyal
– eggyal, Commented Sep 4, 2012 at 12:31

Mihai Iorga · Accepted Answer · 2012-09-04 12:30:52Z

2

try with INSERT IGNORE to ignore insert if it's duplicate. Also if you are still using mysql_* you have an mysql injection vulnerability, escape it:

$db = new database();
$sql = "INSERT IGNORE INTO product_groepen (name) VALUES ('".mysql_real_escape_string($_POST['name'])."')";
$result = $db->executeQuery($sql);
$affected = mysql_affected_rows($result); // you must have that function something like $db->affectedRows ?
if ($affected){
    $page .= 'Yes';
} else {
    $page .= 'No';
}

and make sure you don't execute the query twice

edited Sep 4, 2012 at 12:30

answered Sep 4, 2012 at 12:17

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

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

3 Comments

Marnix Over a year ago

Thanks for your answer, but now it totally ignores the if statement. It always says yes, but isn't adding something in database.

Mihai Iorga Over a year ago

I guess it's because you already have that key inserted, name is KEY ?

Mihai Iorga Over a year ago

the INSERT IGNORE will not produce any errors. updated answer

Collectives™ on Stack Overflow

Database: Check for duplicate error

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related