PHP MySQL PDO duplicates
I have below code that works great. What I want to do is check the refNo first to see if there are duplicates in MySQL. If a warning message then appears, otherwise the message "ok" appears. How can I do this with PDO? Any help?
(include("db.php"));
$SQLquery = "INSERT INTO mydatabase (refNo, name)
VALUES ('".$_POST["refNo"]."', '".$_POST["name"]."');";
$STH = $dbc->query($SQLquery);
?>
edit: Hi guys, I prefer not to add primary keys. Is there another way?
source to share
If you want to do this without using the database level constraint, you will need to make an additional SELECT statement before inserting into the table. But that doesn't give you any absolute guarantees, as there might be two processes that want to insert the same row at the same time and they will still be successful.
- it will be a little like this; I am not familiar with PDO, but the structure should be the same
$selectQuery = "SELECT * FROM mydatabase
WHERE refno = '".$_POST["refNo"]."'";
$res = $dbc->query( $selectQuery );
if( $res->count() > 0 ) {
// this result already exists; show error
}
else {
// this result is new; put the insert query here
}
source to share
Set refNo as the primary key. You can also create it as unique, but that defeats the goal - your reference number is the unique primary identifier. Perfect choice for a primary key.
Also, change your query
try {
$SQLquery = "INSERT INTO mydatabase (refNo, name) VALUES (:refNo, :name)";
$SQLquery = $dbc->prepare($SQLquery);
$SQLquery->bindValue(':refNo', $_POST['refNo']);
$SQLquery->bindValue(':name', $_POST['name']);
$SQLquery->execute();
} catch (Exception $e) {
die("Insert error");
}
$count = $SQLquery->rowCount();
if ($count == 1) {
echo "Record added!";
}
This binds the post value to prevent SQL injection.
Edit: you can follow up on this with $count = $SQLquery->rowCount();
which will be 1 if the insert was successful, since as you can see you edited your question since you posted it for more information.
source to share