Jump to content


Photo

Lookup New User for existing Email & Username


  • Please log in to reply
18 replies to this topic

#1 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 08 September 2006 - 07:24 PM

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

#2 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 08 September 2006 - 07:32 PM

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
}
?>
Tell me the problem, I will try tell you the solution

#3 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 08 September 2006 - 07:36 PM

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

$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
}


#4 maxic0

maxic0
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 08 September 2006 - 07:43 PM

Try this...

<?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{


}
?>


#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 08 September 2006 - 07:56 PM

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#6 daiwa

daiwa
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 08 September 2006 - 08:19 PM

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.

#7 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 08 September 2006 - 08:42 PM

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

#8 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 08 September 2006 - 10:05 PM

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

#9 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 08 September 2006 - 10:10 PM

thats MySQL 5 and above aint it
or MSQL
Tell me the problem, I will try tell you the solution

#10 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 08 September 2006 - 10:12 PM

thats MySQL 5 and above aint it
or MSQL


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

#11 maxic0

maxic0
  • Members
  • PipPipPip
  • Advanced Member
  • 70 posts

Posted 08 September 2006 - 11:41 PM

Just use mine... Mine works fine!

#12 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 08 September 2006 - 11:48 PM

Sure it works maxic0,

But it doesn't do what barkster wanted:

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


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

Jeff

#13 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 09 September 2006 - 01:42 AM

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
Tell me the problem, I will try tell you the solution

#14 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 09 September 2006 - 02:19 AM

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

#15 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 09 September 2006 - 02:26 AM

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

#16 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 09 September 2006 - 05:03 AM

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


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:

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

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

"SELECT MAX(IF(email = 'info@disabledfriends.net',1, 0)) AS em_dup, MAX IF(username='jamie', 1, 0)) AS us_dup FROM findafriend"

Jeff

#17 barkster

barkster
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 09 September 2006 - 02:08 PM

Worked for me without the brackets, cool thanks!

#18 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 09 September 2006 - 02:37 PM

Very coool
"SELECT MAX(IF(email = 'info@disabledfriends.net',1, 0)) AS em_dup, MAX(IF(username='jamie', 1, 0)) AS us_dup FROM findafriend"
Slight typo, there was a mising bracket betweeen the 2nd MAX and IF
Tell me the problem, I will try tell you the solution

#19 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 09 September 2006 - 06:31 PM

Thanks onlyican,

I was getting sleepy :D

Jeff




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users