Jump to content

Archived

This topic is now archived and is closed to further replies.

barkster

Lookup New User for existing Email & Username

Recommended Posts

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

Share this post


Link to post
Share on other sites
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
}
?>

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
I am running MySQL V 4.2.1
SELECT MAX(IF(email = '{info@disabledfriends.net}',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

Share this post


Link to post
Share on other sites
Thanks Jeff you are right, I am looking for you solution but thank onlyican.... I'll try it, I'm using v5.

Share this post


Link to post
Share on other sites
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 = '{barkster@phpfreaks.com}', 1, 0)) AS em_dup, MAX(IF(zUsername = '{barkster}', 1, 0)) AS us_dup from Users

Share this post


Link to post
Share on other sites
[quote author=onlyican link=topic=107387.msg431027#msg431027 date=1157766141]
I am running MySQL V 4.2.1
SELECT MAX(IF(email = '{info@disabledfriends.net}',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 = 'info@disabledfriends.net',1, 0)) AS em_dup, MAX IF(username='jamie', 1, 0)) AS us_dup FROM findafriend"
[/code]

Jeff

Share this post


Link to post
Share on other sites
Very coool
[code]
"SELECT MAX(IF(email = 'info@disabledfriends.net',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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.