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, Sep 04 '12 at 12:17
**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), 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](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Sep 04 '12 at 12:31

Mihai Iorga · Accepted Answer · 2012-09-04T12:30:52.917

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 04 '12 at 12:30

answered Sep 04 '12 at 12:17

Mihai Iorga

39,330
16
106
107

Thanks for your answer, but now it totally ignores the if statement. It always says yes, but isn't adding something in database. – Marnix Sep 04 '12 at 12:23
I guess it's because you already have that key inserted, `name` is KEY ? – Mihai Iorga Sep 04 '12 at 12:25
the `INSERT IGNORE` will not produce any errors. updated answer – Mihai Iorga Sep 04 '12 at 12:31

Database: Check for duplicate error

1 Answers1