-2

This is my database Database img

Imagine the blurred text is simply "test".

I have a basic login/signup form which inserts their data into a database. I want to make sure you can't have 2 users with the same email address, I've tried the following code, but for some reasons, it ignores this and just lets me create an account with the same email. How would I edit this code to make it show an error if you pick an email that someone else has used? Any help is appreciated.

$exist = mysqli_query($conn, "SELECT * FROM username WHERE username='".$username."'");

if(mysqli_num_rows($exist) > 0){
    die("email already exists");
}

Here is my $conn.
$conn = new mysqli($servername, $dbusername, $dbpassword, $dbname);

The connection to the DB works fine.

H. Sims
  • 29
  • 6
  • 1
    You say `email` in one point and checking `username` in the code. You need to replace `WHERE username` with `WHERE email` in the query if you're checking `email` column for existing record. – Wolverine Jan 22 '17 at 12:32
  • Sorry, I did need to check both, I just mixed up my code, changing it to email doesn't help, thoguh. – H. Sims Jan 22 '17 at 12:37
  • You mean you want to check both `username` and `email`? If it is so, better use like this `WHERE username = $username OR email = $email` in the query. – Wolverine Jan 22 '17 at 13:02
  • I don't think the answers are correct on the stated duplicate. – Gordon Linoff Jan 22 '17 at 13:45
  • No pictures, thanks. – Strawberry Jan 22 '17 at 13:49
  • you're checking for the wrong column if you're looking to check if an email address exists, rather than a username. You also didn't post any example values in db or where the email value comes from. – Funk Forty Niner Jan 22 '17 at 13:58

2 Answers2

0

I want to make sure you can't have 2 users with the same email address

The best way to ensure this is to have a unique constraint/index on email:

alter table accounts add constraint unq_accounts_email unique (email);

You would appear to want the same for username as well:

alter table accounts add constraint unq_accounts_username unique (username);

Then, when you attempt to insert a row with a duplicate, you will get a constraint violation.

Why is it better to do this in the database? The most important issue is race conditions -- two people who attempt to register the same username or email at the same time. Doing the check and then doing the insert can still allow duplicates.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-3

First of all change your query to select from accounts table.

Then you are using both procedural and object oriented forms of mysqli. You create the connection using object oriented mysqli then you use the procedural mysqli functions.

Let's say you only use object oriented:

$conn = new mysqli($servername, $dbusername, $dbpassword, $dbname);
$result = $conn->query("SELECT * FROM username WHERE username='".$username."'");
$rows = $result->num_rows;

if($rows > 0) {
    die('email already exists');
}

for procedural change your connection initiation to this:

$conn = mysqli_connect($servername, $dbusername, $dbpassword, $dbname);

You can also enforce the database to keep the field unique by adding an UNIQUE KEY on that username column

changepicture
  • 466
  • 1
  • 4
  • 10