Chapter 8: Script 9.3 - Edit_User.Php, Line 53 - Test For Unique Email Address

[markifornia]

On page 274, it explains why the query is written this way, the reason below:

 

"To write one query that will work for both possibilities, don't check to see if the email address is being used, but rather see if it's being used by anyone else..."

 

 

On Line 53:

 

$q = "SELECT user_id FROM users WHERE email='$e' and user_id != $id";

 

 

I don't see how this code is checking to see if being used by anyone else, I'm confused with this query.

 

Is there an easier way this could be explained or elaborated more for better comprehension?

 

 

 

Thanks,

Mark

[Paul Swanson]

That query will SELECT a user_id if the email address is found in the users table only if the user_id NOT (!=) the same as the current user. So if a dataset is returned, it would indicate that another user_id is already using that email address.

[markifornia]

What an interesting query, it took a while to get my head around it. I could almost write up so many different versions of how it could be explained, at least for myself. Nonetheless, you've explained it clearly, thanks Paul!

 

...

 

Line 60

 

if (mysql_affect_rows($r) == 0 ) { // The query did not find another email that matches the current users email address

 

// UPDATE query

 

} else { // The query found another email address in the database that matches the current users email address

 

// already registered

 

}

[bahaa]

Here is 2 functions to check for unique email address, one used for insert and the other for update

when you try to update a record and want to check for unique email, you have to check for all records except the one you are trying to use

 

function uniqueEamilUpdate($email, $ID){

global $sdbc;

$query = "SELECT userEmail, userID

FROM user

WHERE userEmail ='". mysqli_real_escape_string($sdbc, $email)."'

AND userID !='". mysqli_real_escape_string($sdbc, $ID)."'";

$result = mysqli_query($sdbc, $query);

return $result;

}

 

 

 

and here is a function to check for unique email on insert

 

function uniqueEamil($email){

global $sdbc;

$query = "SELECT userEmail, userID

FROM user

WHERE userEmail ='". mysqli_real_escape_string($sdbc, $email)."'";

$result = mysqli_query($sdbc, $query);

return $result;

}

[markifornia]

Thanks bahaa, what is assiged to $sdbc?

 

Is it the below:

mysqli_query($dbc, $q);

 

Also, is the function missing another parameter of $ID?

function uniqueEmail($email)

 

Very useful functions, I've never created functions with query statements, I like this idea. It's awesome.

[bahaa]

$sdbc is my connection.

 

The function is not missing any thing, I posted 2 functions, the one without id is used when sign in up for new user and in this case there is no id to check for.

the function that takes ID as a parameter is used when for updating.

[markifornia]

Ah, sorry misread. I do see now that the second function is just selecting the user_id.