Lookup New User for existing Email & Username

[barkster]

I'm looking for a simplier solution to check when creating a new user to see if username or email has already been used.  I know how to do it with SELECT WHERE email or username but I would have to loop through the results to find out which is which if there are one of each already.  Is there a simplier way to find out for instance if I do a select and do something like a in_array to see if for instance if email="currentlookup email" if in array then it found email else it had to have found username match.  Don't want to have to loop through the results unless I have too.  Hopefully I explained correctly.  Thanks

[onlyican]

What i do is
<?php
$query = "SELECT * FROM table WHERE username = '".$user."'";
$result = mysql_query($query);

if($result != 0){
//Continue validating, or adding to DB
}else{
echo "I am afraid the username ".$user." has been taken
}
?>

[barkster]

That is what I have now and that only checks to see if username is already taken.  I want to check to see if username or email is already used.  Then I need to determine which it is.  I was trying to do it all in one step  like

[code]
$query = "SELECT * FROM table WHERE username = '".$user."' OR email = '".$email."'";
$result = mysql_query($query);
if(mysql_num_rows($result)>0) {
//then find out which is already taken somehow
} else {
//insert record
}
[/code]

[maxic0]

Try this...

[code]
<?php
$result = mysql_query("SELECT * FROM users WHERE username = '".$user."' OR email = '".$email."'");
$number = mysql_num_rows($result);
if ($number > 0)
{
    echo 'Username or email already in use.</br> Please go back and use another Email and or Username.';
}else{


}
?>
[/code]

[HuggieBear]

Might not be appropriate, but why not use the email address AS the username, people are less likely to forget their email than their username and then you don't have to worry about additional overhead in your code.

Just a suggestion  ::)

Regards
Rich

[daiwa]

just a friendly reminder to always escape anything you send you the db so your $user should be mysql_real_escape_string($user)
same for email.

[barkster]

Yeah I thought about using email for username but didn't run into this problem until now and that would take some time to change.  I'll just loop them I guess

[jefkin]

With a decent version of MySQL:


  "SELECT MAX(IF(email = '{$email}', 1, 0)) AS em_dup, MAX(IF(user = '{$user}', 1, 0)) AS us_dup from users"


Jeff

[onlyican]

thats MySQL 5 and above aint it
or MSQL

[jefkin]

[quote author=onlyican link=topic=107387.msg430935#msg430935 date=1157753435]
thats MySQL 5 and above aint it
or MSQL
[/quote]

mysql -V

mysql  Ver 14.7 Distrib 4.1.11, for mandrake-linux-gnu (i586)

When I found out 4.1.11 supported it, I jumped for joy  :D

Jeff

[maxic0]

Just use mine... Mine works fine!

[jefkin]

Sure it works maxic0,

But it doesn't do what barkster wanted:

[quote author=barkster link=topic=107387.msg430837#msg430837 date=1157744184]
That is what I have now and that only checks to see if username is already taken.  I want to check to see if username or email is already used.  Then I need to determine which it is.  I was trying to do it all in one step  like
[/quote]

With the above query barkster can test or either or both conditions.

Jeff

[onlyican]

I am running MySQL V 4.2.1
SELECT MAX(IF(email = '{[email protected]}',1, 0)) AS em_dup, MAX IF(username='{jamie}', 1, 0)) AS us_dup FROM findafriend;
and I got

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(username='{jamie}', 1, 0)) AS us_dup FROM findafriend' at lin

(I am using MySQL front, so I dont get all the error

[barkster]

Thanks Jeff you are right, I am looking for you solution but thank onlyican.... I'll try it, I'm using v5.

[barkster]

I'm going to play with that query some more Jeff but when I ran it with know username and email it showed zero for both values returned.

SELECT MAX(IF(zEmail = '{[email protected]}', 1, 0)) AS em_dup, MAX(IF(zUsername = '{barkster}', 1, 0)) AS us_dup from Users

[jefkin]

[quote author=onlyican link=topic=107387.msg431027#msg431027 date=1157766141]
I am running MySQL V 4.2.1
SELECT MAX(IF(email = '{[email protected]}',1, 0)) AS em_dup, MAX IF(username='{jamie}', 1, 0)) AS us_dup FROM findafriend;
and I got

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(username='{jamie}', 1, 0)) AS us_dup FROM findafriend' at lin

(I am using MySQL front, so I dont get all the error
[/quote]

Sorry onlyican, I was presenting the SQL as if in a string for PHP.

In my PHP, a double quoted string with {$somevar} in it interpolates the '{' and '}' as part of the variable refrence.

To phrase the query more like other examples I've seen here:

[code]
'SELECT MAX(IF(email = \'' . $email . '\',1, 0)) AS em_dup, MAX IF(username=\'' . $user . '\', 1, 0)) AS us_dup FROM findafriend'
[/code]

or for your query for pasting into a mysql interface directly:

[code]
"SELECT MAX(IF(email = '[email protected]',1, 0)) AS em_dup, MAX IF(username='jamie', 1, 0)) AS us_dup FROM findafriend"
[/code]

Jeff

[barkster]

Worked for me without the brackets, cool thanks!

[onlyican]

Very coool
[code]
"SELECT MAX(IF(email = '[email protected]',1, 0)) AS em_dup, MAX(IF(username='jamie', 1, 0)) AS us_dup FROM findafriend"
[/code]
Slight typo, there was a mising bracket betweeen the 2nd MAX and IF

[jefkin]

Thanks onlyican,

I was getting sleepy :D

Jeff