1

So I'm working on my registration process of pset7. What's the best way to make sure that an e-mail address can only be used for one account?

In my php controller I use this sql query to check if the email address is already used in my users database.

/* check if email is already used */
$search = query("SELECT * FROM users WHERE email='?' ", $_POST["email"]);
if ($search == 1)
    apologize("Email address is already used");

It happens that $search always contains 0, even if I entered an email adress that is already used by another user.

The SQL statement SELECT * FROM users WHERE email='example@example.com' does return a row if the email address is found in the database...

How could I get this to work? Is it better to make the email column unique? which setting would I need to change?

Thanks!

(Edited post)

  • Have you tried putting the email address in quotes? select * from users where email = 'jharvard@gmail.com'. And similarly, $search = query("SELECT * FROM users WHERE email='?'", $_POST["email"]); – Melde Oct 5 '14 at 14:58
  • This works for the query in phpMyAdmin, but not within the website. after: $search = query("SELECT * FROM users WHERE email='?'", $_POST["email"]); $search, when dumped, contains nothing. even if the email adress is present in the sql table – Jesse Oct 5 '14 at 16:05
3

Your current query will return FALSE if it failed and return data in the form of rows if succesful. You know there can only be one match in the database, if any, but the query function does not know that. It returns all data the same, whether its 1 row or an array of many rows. As such, the rows returned must be accessed with an array index, just like an int array or char array in c.

you would access the first row of query results with the following: $search[0]["email"]

assuming the name of your field containing the email address is email

If you reformat your approach a little bit and instead try and insert query, knowing that if the insert fails because it's a duplicate key, you'll be better off.

You would have to set the email address field in the database to be unique

if(query("INSERT INTO users (fields to be inserted) VALUES (values to be inserted)") === false))
{
  \\render error message about duplicate username or email address
}
| improve this answer | |
  • Thank you. Should I set the email adress fiels as a primary key or a unique key? When I try to set the email adress field as primary key I get ALTER TABLE 'users' DROP PRIMARY KEY, ADD PRIMARY KEY('email') and when I try to set the field as a unique key I get #1062 - Duplicate entry '' for key 'email' ... – Jesse Oct 6 '14 at 17:39
  • primary key should be id, unique keys for username and email – lethaljd Oct 6 '14 at 17:43
  • Thanks! It works now! I get now why I got the duplicate entry error.. Most of my users had an unset email value, so when I tried to make email unique, sql realised that there were duplicate email adresses that weren't unique! Thanks!!! – Jesse Oct 6 '14 at 17:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .