Ensuring unique user info for username, email and phone

PHP
1

Hi,

In my MySQL DB, I have a user table with id, username, email, phone columns. Username, email and phone columns are unique, i.e. no two users can have the same username, no two users can have the same email, no two users can have the same phone.

When creating a new user with my PHP script, checking for uniqueness is easy, I simply check if any of the provided username, email or phone already exists in the table.

My issue is with updating the user info. How can I do a PHP check, to see if either the username, email or phone already exists on another user when I am updating the user info?

For example, let’s say I have two users in my table with usernames user1 and user2. I want to edit the username for user1. How can I make sure that if I enter user2 as username, my PHP script will give an alert because there is another user with that username?

MySQL has the Unique key, but I want to ensure uniqueness via my PHP script. Or, does MySQL send an error response when a duplicate value for a unique key is trying to be inserted? I checked error logs but didn’t see.

What I am trying to figure out is the logic/algorithm, not the specific code.

Thanks for any ideas.

2

mysql will throw an error if you try and insert another username which is the same if you have it set to unique. But that probably isn’t the best way to check.

I’d just do as you have done when they create a username. Check if it exists in the DB → if it doesn’t then add it. If you are updating just do the same thing - Check if it exists in the DB → if it doesn’t then update it

1 Like
3

The error typically gets outputted via the database API you are using. So if you are using PDO, it will give you some kind of error. Same with mysqli_.

1 Like
4

I guess I can go with checking existence, the reason I wanted to seek other opinions is that I wanted to keep queries at a minimum. In my new user creation script, I use one single query to do the check, like follows:

function user_exists($username, $email, $phone) {
	$q = DB::$d->prepare("SELECT user_id FROM users WHERE user_username = ? OR user_email = ? OR user_phone = ?");
	$q->bind_param('sss', $username, $email, $phone);
	$q->execute();
	$r = $q->get_result();
	return ($r->fetch_assoc() > 0) ? true : false;
}

The above function returns true if any of the username, email or phone already exists in the database.

For user update check, it looks I will need to do three queries, one for username, one for email and one for phone separately, in other words, a function user_username_exists(), user_email_exists() and user_phone_exists(). Even if I do one function, it will still be three queries.

The user update function is as follows (this is to be used after the check):

function user_update($user_id, $username, $password, $email, $phone) {
	$q = DB::$d->prepare("UPDATE users SET user_username = ?, user_password = ?, user_email = ?, user_phone = ? WHERE user_id = $user_id");
	$q->bind_param('ssss', $username, $password, $email, $phone);
	$q->execute();
}

I am not much nowledgeable about catching MySQL errors, any ideas how I could catch this specific error for unique duplicate entry?

5

don’t know to be honest. The mysql docs will no doubt tell you how to catch the errors.

I wouldn’t worry about running another query to check a username tbh it will be such a quick query you won’t even notice unless you have millions of entries and even then it probably won’t even worry too much.

Queries are for querying, errors are for when something goes wrong. I wouldn’t use the mysql error output as a way of querying your data input.

1 Like
6

Your reasoning makes sense. This interface is just for an admin, I mean it will be used by one single person, to add or update very few users, very rarely. I will go with doing the check as you suggested. Thank you.

7

The correct method is to attempt the insert and capture the database duplicate error if any. Do not check if the record exists first. You will create a race condition by doing so. There is no need for code gymnastics. Just let the database do what it does. KISS.

1 Like
8

Thanks for your input. So, I found the error code for duplicate entry, which is 1062 and currently I have the following setup:

function user_add($username, $password, $email, $phone) {
	$q = DB::$d->prepare("INSERT INTO users (user_username, user_password, user_email, user_phone) VALUES (?, ?, ?, ?)");
	$q->bind_param('ssss', $username, $password, $email, $phone);
	$q->execute();
	if (DB::$d->errno == 1062) return false;
}

I use the above function in my script as follows:

if (!user_add($username, $password, $email, $phone)) $user_exists = true;

But this returns $user_exists as true when I enter a new user with totally new information. Am I doing the error check wrongly in my function? Thank you.

EDIT:

I finally have the following and it seems to be working as I wanted:

function user_add($username, $password, $email, $phone) {
	$q = DB::$d->prepare("INSERT INTO users (user_username, user_password, user_email, user_phone) VALUES (?, ?, ?, ?)");
	$q->bind_param('ssss', $username, $password, $email, $phone);
	if (!$q->execute()) return DB::$d->errno;
}

if (user_add($username, $password, $email, $phone) == 1062) $user_exists = true;

I will apply the same concept to the user_update function too.

9

How times change! Not so long ago a whole family could have the same phone number :rolleyes:

SELECT 7 FROM users WHERE user_username=? AND user_id<>?

where the first ? is the entered username whose existence you want to check and the second ? is user_id of the user you want to update. If the query returns a row (with value 7) it means another user with the username exists and you should stop and display relevant error.

I admit that the theoretically most correct way is to insert and catch a potential error but I don’t like the approach because the logic is less clear and non-standard (checking for DB specific error codes). Also, the database will not tell you which unique constraint was violated so you don’t know which value caused the error and thus can’t present a specific error message. The only advantage is that this method is concurrency safe. But you would need a really very busy system with people trying to update the table simultaneously all the time for this to become an issue.

1 Like
10

And that is where mysqli_ outranks PDO. mysqli_ has an error code for very specific reasons whereas, PDO has general error codes. I’ve tried this once and I was able to look for a specific error within mysqli_ whereas in PDO, I wasn’t able to find the same error nor any similar error code.

11

But can you check in mysqli which unique constraint was violated? I admit I was somewhat wrong in stating that the database won’t tell you that, mysql will raise an error message like Duplicate entry 'abc' for key 'key_name'. But still even if you can get this message you would need to parse the text in order to find the problematic column - and key_name is not the column name - it can be the same but can also have a different name than the column. This is not a very reliant approach.

12

I don’t recall if you can. I wrote this application a super long time ago and when I was testing it, I had it in one of my older partitions. I’ll have to check once I get home if I still have it. I’ve been trashing my partitions as of late and most of the older stuff I had, they are probably gone now. But if I can find it, I’ll be sure to point it out. All I remember that it did was check for a specific error code and if it was found, I just did the opposite of whatever I was doing so that there was no errors being displayed.

13

I’m curious how mysqli is better than PDO in error reporting because from what I can see, PDO provides the same three error categories as mysqli: error number, error code (SQLSTATE) and error message. AFAIK, the database simply does not provide anything else so mysqli can’t get anything more - or can it?

14

[quote=“Lemon_Juice, post:9, topic:293400, full:true”]I admit that the theoretically most correct way is to insert and catch a potential error but I don’t like the approach because the logic is less clear and non-standard (checking for DB specific error codes). Also, the database will not tell you which unique constraint was violated so you don’t know which value caused the error and thus can’t present a specific error message. The only advantage is that this method is concurrency safe. But you would need a really very busy system with people trying to update the table simultaneously all the time for this to become an issue.
[/quote]

Thank you for your input. My initial intention was, as I asked in the first post, to find the logic to do the check. Then it was suggested to catch and act based on the error returned from the DB instead of adding more queries, which then seemed to be unnecessarily adding more burden on the DB. I did write the PHP code to do the check and it worked fine, but it used three queries to do the check (one for username, one for email, one for phone), and then one more query to do the UPDATE, four in total. Not really a problem for my system as it won’t be a busy one, but still, if something is more efficient, it is more preferrable for me. So, I decided to go with the error catching as mentioned in my last post above. But, I am glad that I now have a better grasp of both approaches and I will go with the one that will make most sense based on the specific cases going forward.

15

Yes, there is some burden on the database but you’d need an extremely update-busy system for it to become an issue. However, there are still ways to lessen it:

1… Merge the three queries into a single one like this:

SELECT
    (SELECT 7 FROM users WHERE user_username='alan' AND user_id<>1) AS username_exists
  , (SELECT 7 FROM users WHERE user_email='a@example.com' AND user_id<>1) AS email_exists
  , (SELECT 7 FROM users WHERE user_phone='123456789' AND user_id<>1) AS phone_exists

2… You can use a hybrid approach: first try to update and if the 1062 error code is returned then do the select check to see which value caused the error. This will not be fully concurrency safe but unless you are working with a crazy busy system it will work fine. So there will be no additional burden unless one of the unique constraints gets violated.

1 Like
16

Could you just use an “OR” clause?

select something from users where username=? or email=? or phone=? and user_id<>1

If you get a result, then you have a duplicate.

@Lemon_Juice, in your query above, would there be any noticeable difference to return a different value for each “_exists” column? I guess either would allow you to decide which one is duplicated.

1 Like
17

Using “AND user_id NOT EQUAL TO current_user_id” never came to my mind. Your suggestion will surely keep it at one query for the check. In fact this looks like the logic I had been seeking from the beginning. Thank you.

18

But this way you don’t get the information which field already has the value. The ‘OR’ version could be enhanced like this:

SELECT
  user_username='alan' AS username_exists
, user_email='a@example.com' AS email_exists
, user_phone='123' AS phone_exists
  
FROM users
WHERE (user_username='alan' OR user_email='a@example.com' OR user_phone='123')
 AND user_id<>1

I’m not sure what you are asking about, you mean not returning 7 but different values from subqueries? It doesn’t matter because either you get a value (like 7) if a row is found or NULL if no row is found. Different values would give you nothing but cosmetics.

1 Like
19

Got it. So here’s a little bit of the code.

if($db->connect_errno == 1045) {

I know that this one was for checking to see if I was able to connect to the database using a random string. Upon doing a quick Google search on connect_errno, in the manual, someone noted and gave the URL to find what each error code means.

http://php.net/manual/en/mysqli.connect-errno.php

20

In PDO you can get the same connection error code when you catch PDOException and use getCode() on it. But I think I know why you mentioned it: when looking at comments in the PHP manual someone offered a workaround for PDOException failing to provide the error code with the getCode() method. The comment is 7 years old now and when I tested it now in PHP 5.6 and 7.1 getCode() returns the correct connection error code. So it looks like this was a bug that was fixed later in PDO and currently there is no difference between mysqli and PDO in the ability to get database errors.

Edit: I see that there is some mess in PDOException error handling. The manual states that the code property of PDOException is a string because it returns SQLSTATE. But my testing gave me an integer code. The truth appears to be:

  1. For connection errors PDOException returns an integer error code.
  2. For other errors (after the connection has been successful) PDOException returns a string error code, which is SQLSTATE value. In order to get the integer code you need to use errorInfo() or access the errorInfo property of PDOException.

This is undocumented behaviour, something to bear in mind.

1 Like