mysqli insert - but only if not a duplicate [dupli

Question

This question already has answers here:

if not exists insert in MySql (2 answers)

Closed 6 years ago.

I'm a Java developer who just got handed the task of "some quick easy DB stuff" - except I don't know much about PHP/MySQL...I need to insert a record into a DB - but only if the email field doesn't match one that already exists in the DB. Here's what I've gleaned so far for my PHP code:

// Grab the values from the HTML form:
$newUserName = $_POST['newUserName'];
$newUserName = $mysqli->real_escape_string($newUserName);
$newUserEmail = $_POST['newUserEmail'];
$newUserEmail = $mysqli->real_escape_string($newUserEmail);

// Now search the DB to see if a record with this email already exists:
$mysqli->query("SELECT * FROM RegisteredUsersTable WHERE UserEmail = '$newUserEmail'");

Now I need to see if anything came back from that search - meaning the email already exists - and if so I need to alert the user, otherwise I can go ahead and insert the new info into the DB using:

$mysqli->query("INSERT INTO RegisteredUsersTable (UserName, UserEmail) VALUES ('".$newUserName."', '".$newUserEmail."')");

Any ideas?

Answer 1

As for your first query, to soften the load on servers, use count() instead.

$mysqli->query("SELECT count(*) FROM RegisteredUsersTable WHERE UserEmail = '$newUserEmail'");

This way, you can just check if you've gotten a result higher than 1. If the result is greater than 1, then the username exists (Since a row was returned).

To check the data returned, you need to simply execute the statement, then fetch the results. Part of the fun is learning, so here's the documentation

Answer 2

Working from your code, this should point you in the right direction. there are, perhaps, better ways to structure your database that will make better use of it.

<?php

$mysqli = new mysqli("localhost", "iodine", "iodine","iodine");

// Grab the values from the HTML form:
/*
$newUserName = $_POST['newUserName'];
$newUserName = $mysqli->real_escape_string($newUserName);
$newUserEmail = $_POST['newUserEmail'];
$newUserEmail = $mysqli->real_escape_string($newUserEmail);
*/
$newUserName = "Test User";
$newUserEmail = "[e-mail]";

// Now search the DB to see if a record with this email already exists:
echo "SELECT * FROM RegisteredUsersTable WHERE UserEmail = '$newUserEmail'", "\n";
$result = $mysqli->query("SELECT * FROM RegisteredUsersTable WHERE UserEmail = '$newUserEmail'");

if (!$result) {
  die($mysqli->error);
}
echo "num_rows = ".$result->num_rows."\n";
if ($result->num_rows > 0) {
   echo "Duplicate email\n";
   // do something to alert user about non-unique email
} else {
  $result = $mysqli->query("INSERT IGNORE INTO RegisteredUsersTable (UserName, UserEmail) VALUES ('".$newUserName."', '".$newUserEmail."')");
  if ($result === false) {echo "SQL error:".$mysqli->error;}
}

?>

Answer 3

Consider putting a unique index on this particular table. The following code will add the index and remove any current duplicates:

ALTER IGNORE TABLE `RegisteredUsersTable` ADD UNIQUE INDEX unique_email (`UserEmail`);

Once this is added, use INSERT IGNORE or INSERT...ON DUPLICATE KEY UPDATE. They will only preform the insert if there is no duplicates.

$mysqli->query("INSERT IGNORE INTO RegisteredUsersTable (UserName, UserEmail) VALUES ('".$newUserName."', '".$newUserEmail."')");

Mysql will throw an error because the email is already in the database. However, the IGNORE command is telling the script to not pay any attention to errors for this query because, in this case, you expect it for a duplicate row.

Also, there is a way to alert your user with a failure or success message, even with INSERT IGNORE. Use MYSQL LAST_INSERT_ID(). If an ID was given, it was inserted. If not, then the email was already there (or there was another error).