this code is for insert only, my problem is how to prevent duplicate records before inserting

Question

I want to show data input is already exist array

<?php
if(isset($_POST["med_nom"]))
{
 $connect = new PDO("mysql:host=localhost;dbname=dbs", "", "password");
 for($count = 0; $count < count($_POST["med_nom"]); $count++)
 {
  $query = "INSERT INTO medicinetbl (med_dorder, med_id, med_nom, med_qty, med_prc) VALUES (:med_dorder, :med_id, :med_nom, :med_qty, :med_prc)";
  $statement = $connect->prepare($query);
  $statement->execute(
   array(
    ':med_dorder'  => $_POST["med_dorder"][$count],
    ':med_id'  => $_POST["med_id"][$count], 
    ':med_nom'  => $_POST["med_nom"][$count], 
    ':med_qty' => $_POST["med_qty"][$count], 
    ':med_prc'  => $_POST["med_prc"][$count]
   )
  );
 }
 $result = $statement->fetchAll();
 if(isset($result))
 {
  echo 'ok';
 }
}
?>

You really shouldn't put database access credentials in your question — Nick, May 18 '22 at 23:26
Just SELECT from the database and filter by any criteria you want, which you've decided constitute duplicate data. If it returns a row, then you have a duplicate — ADyson, May 18 '22 at 23:44
The simple solution is to put a unique constraint on the appropriate fields in your database — Phil, May 18 '22 at 23:45
this code to select the same data does not work `$selectQuery = "SELECT * FROM medicinetbl WHERE med_nom = '".$_POST["med_nom"]."'"; $res = $connect->query( $selectQuery ); if( $res->count() > 0 ) { // this result already exists; show error echo "exist"; }` — Daryl Tan Feliz, May 18 '22 at 23:51
@DarylTanFeliz You better change your bank password. Even if you edited, the history is saved — Lucas Henrique, May 18 '22 at 23:53
If I were you, I would change your password, because anyone can look at the edit history and see the original post; I would also change any account that uses that password for safe measure (as anyone looking into your accounts for malicious reasons may come across this and try to gain access to them). — Crimin4L, May 19 '22 at 01:56
Also, don't forget to click the checkmark on the answer that helped you; and if you want, you can also upvote it — Crimin4L, May 19 '22 at 03:12
Thank you for your concerns, I already Change credentials and I also still looking for an answer. — Daryl Tan Feliz, May 19 '22 at 03:24
#Hi guys, I finally solve my own problem. ```$med_nom = $_POST['med_nom'][$count]; $med_id = $_POST['med_id'][$count]; $stmt = $connect->prepare("SELECT med_nom FROM medicinetbl WHERE med_nom = :med_nom"); $stmt->bindParam(":med_nom", $med_nom); $stmt->execute(); if($stmt->rowCount() > 0){ //echo for Data input is already exist``` — Daryl Tan Feliz, May 19 '22 at 09:04

Crimin4L · Answer 1 · 2022-05-20T04:00:17.953

This is how I check for duplicate Row ID's in my table:

# Generate random Row ID
$rid = rand(1000, 9999);

# start query (yours will be different as I have a class created for starting 
# connections with PDO; nonetheless, you should still understand what im doing here)
$query_ridcheck = $db->connect->prepare("SELECT * FROM `table` WHERE rid=:rid");
$query_ridcheck->bindValue(':rid', $rid, PDO::PARAM_STR);
$query_ridcheck->execute();

# if it fetches any data back (while searching for the newly created $rid), 
# then generate another random $rid again and check again. This while loop will
# keep going until it DOESN"T fetch any data back (meaning the random $rid has 
# not been taken already).
while($query_ridcheck->fetch(PDO::FETCH_ASSOC)){
    $rid = rand(1000, 9999);
    $query_ridcheck = $db->connect->prepare("SELECT * FROM `table` WHERE rid=:rid");
    $query_ridcheck->bindValue(':rid', $rid, PDO::PARAM_STR);
    $query_ridcheck->execute();
}

Essentially, you can check for duplicate data before inserting, and choose what todo. I choose to regenerate the data and check again until I got a string/integer that wasn't a duplicate.

After this check, I go onto insert the data into the table using that row ID (since I now know it will not be taken)

Or like @Your_Common_Sense graciously pointed out, instead of repeating code you can also perform the same function like this:

# executes at lease once, then checks if the condition 
# is true; If it is, continue looping until it's false.
do{
    $rid = rand(1000, 9999);
    $query_ridcheck = $db->connect->prepare("SELECT * FROM `table` WHERE rid=:rid");
    $query_ridcheck->bindValue(':rid', $rid, PDO::PARAM_STR);
    $query_ridcheck->execute();
}while($query_ridcheck->fetch(PDO::FETCH_ASSOC));

a do-while loop should be used in this case to avoid code duplication — Your Common Sense, May 19 '22 at 07:24
Honestly, I never even heard of a do-while loop, Thank you for the insight! :D — Crimin4L, May 19 '22 at 21:36

score -2 · Answer 2 · answered May 18 '22 at 23:45

-2

You should search into your data for the values you dont want to be repeated, if there is a record with the values, do not insert.

answered May 18 '22 at 23:45

Isaac Batista

57
3

but this does not working properly $selectQuery = "SELECT * FROM medicinetbl WHERE med_nom = '".$_POST["med_nom"]."'"; $res = $connect->query( $selectQuery ); if( $res->count() > 0 ) { // this result already exists; show error echo "exist"; } – Daryl Tan Feliz May 18 '22 at 23:50
What happens when you execute this code? Theres an error? – Isaac Batista May 19 '22 at 00:33
there's nothing happen, it not saving – Daryl Tan Feliz May 19 '22 at 00:55
Actually, im asking what happens with the select query. Does It get the data correctly? – Isaac Batista May 19 '22 at 09:21

this code is for insert only, my problem is how to prevent duplicate records before inserting

2 Answers2