Prevent Duplicate Entries in PHP MySQL

Question

I have the following in my PHP.

$stmt = $conn->prepare("INSERT IGNORE INTO savesearch (user, searchedFor, sortOrder, buildURLString, aspectFilters, oneSignalId, totalEntries)
  VALUES (?, ?, ?, ?, ?, ?, ?)");
  $stmt->bind_param("sssssss", $user, $searchedFor, $sortOrder, $buildURLString, $aspectFilters, $oneSignalId, $totalEntries);

  // set parameters and execute
  $user = $_POST['user'];
  $searchedFor = $_POST["searchedFor"];
  $sortOrder = $_POST["sortOrder"];
  $buildURLString = $_POST["buildURLString"];
  $aspectFilters = $_POST["aspectFilters"];
  $oneSignalId = $_POST["oneSignalId"];
  $totalEntries = $_POST["totalEntries"];

  if ($stmt->execute()) {
    $output->success = true;
    echo json_encode($output);
  } else {
    $error->error = mysqli_error($conn);
    echo json_encode($error);
  }

However, IGNORE is not being picked up, it continues to add entries. Is there another good way to fix this?

Id like to see if the USER and the URL is the same, dont add, echo duplicate entry.

Have you indexed the particular column, for which you want to avoid duplicates as PK? — Penguine
– Penguine, Commented Dec 17, 2018 at 7:09
make unique attribute in database..and check affected rows ..if any row affected then send success response — ASHISH SHARMA
– ASHISH SHARMA, Commented Dec 17, 2018 at 7:11
Use Unique key constraints and you also validate duplicate check validation from frontend. follow this w3schools.com/sql/sql_unique.asp. — Jasim Juwel
– Jasim Juwel, Commented Dec 17, 2018 at 7:12

Martin · Accepted Answer · 2018-12-17 07:10:36Z

1

IGNORE is actually mostly for the opposite of what you want here. Instead, you can amend your MySQL table something like:

ALTER TABLE savesearch ADD UNIQUE KEY(user, buildURLString)

Then remove your IGNORE keyword

answered Dec 17, 2018 at 7:10

Martin

6,7054 gold badges28 silver badges28 bronze badges

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

2 Comments

letsCode Over a year ago

ok i was able to add those as unique. in PHP, how would i echo that out?

letsCode Over a year ago

thanks for the tip. for anyone else seeing this, this is what I needed for the SQL. CREATE UNIQUE INDEX WHATEVER_HERE ON tbl_name(row_name,row_name);

Collectives™ on Stack Overflow

Prevent Duplicate Entries in PHP MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related