signup validation script problems

[fairgeek]

I've been working on this for hours, and I can't find the error -
The following code sample is part of a sign-up validation script. The username check works correctly and notifies when there is already a database entry equal to the submitted username. The email check, however, does not give an alert for the same situation.

$query = @mysql_query("SELECT username FROM user WHERE username='$username'");
$ro = @mysql_fetch_assoc($query);
if($ro['username']==$username)
{    // checking if that username is already registered
//WORKS CORRECTLY
$msg=$msg."Username already taken <BR>";
$flag="NOTOK";   //setting the flag to error flag.
} 
$q = mysql_query("SELECT email FROM user WHERE email='$email'");
$r = mysql_fetch_assoc($q);
if($r['email']==$email)
{    // checking if there already is a user with that email registered
//DOESN'T WORK!
$msg=$msg."There already is an account with this email address <BR>";
$flag="NOTOK";   //setting the flag to error flag.
} 

The insertion of a new user in the database is also not working after I added a confirmation email script (the confirmation email is sent with a username, random key to the email - but there is no ID because no new entry in the database is created, the signup is no longer working!)

else 
{
$result = @mysql_query("INSERT INTO user (username, password, birthdate, ip, email, location, money, type, energy,key) VALUES ('$username','$password','$birthdate','$signupdate','$ip','$email','$location','$money','$type','$energy','$key')");               

$row = @mysql_fetch_assoc($result);
$id = $row["id"];
$head =  'From: [e-mail]' . "\r\n" . 
'Reply-To: [e-mail]' . "\r\n" . 
'X-Mailer: PHP/' . phpversion(); 
$subject = "confirmation email"; 
$message = 'Dear '.$username.', this is your activation link to join ___.  In order to confirm your membership please click the following link or paste the url in your browser: http://www.mydomainname.net/confirm.php?id='.$id.'&key='.$key.' Thank you for joining'; 
 if(mail($email, $subject, $message, $head)) 
{send user to homepage to display message, this part works correctly
$msg = "Account created. Please check ".$email." to confirm your membership before you can login below.";
header ("Location: http://mydomainname.net/index.php?msg=$msg"); 
}

What would make these function as intended?
Thanks for your help.

[cgraz]

First edit your post and put php code in [noparse]

[/noparse] tags so it's easier for us to read.

Secondly, get rid of the @ symbols and use mysql_error() on your queries that aren't working. If you're still having trouble, print your query strings. i.e.

print "SELECT username FROM user WHERE username='$username'";

This will show you what you're sending MySQL.

If no errors or problems after both of these tests, then that username or email are not in your table.

[FusionComputers]

Actually, the fetch_assoc function uses case-sensitive keys. If the email field is actually Email in the db, then it won't work. I agree on the @ symbols, they don't need to be there. Code runs fine without.

[fairgeek]

the fields are email and username in the database, which are both set as unique fields, varchar, length 255 .

[FusionComputers]

Humpf. Let me change case on the entry, and see if "FusionComputers" and "fusionComputers" don't play nice with each other.

[FusionComputers]

Yep, that does cause issues. So, what you want to do is either strtoupper() or strtolower() your $POST/$GET data, as well as the data from the DB, in order to get case-insensitive matching.

[fairgeek]

I tried printing the queries, and they are all receiving input correctly,
but the data is still not being added to the database
and the email is not being checked against the values in the database...

the username validation is functioning correctly. I can see the email address at the specified location as well ($try2), so its not an error in case sensitivity:

$query = mysql_query("SELECT username, email FROM user WHERE username='$username'");
$ro = mysql_fetch_assoc($query);
$try = $ro['username'];
$try2 = $ro['email'];
echo $try;
echo $try2;
echo $username;
if($ro['username']==$username)
{    

$msg=$msg."Username already taken <BR>";
$flag="NOTOK";   //setting the flag to error flag.
} 

When I have the email validation script print its values, the print query shows that the correct email is stored in $email, but both variables $etry and $etry2 are empty.

print "SELECT email FROM user WHERE email='$email'";
$q = mysql_query("SELECT email, username FROM user WHERE email='$email'");
$r = mysql_fetch_assoc($q);
$etry = $r['email'];
$etry2 = $r['username'];
 echo $etry;
echo $etry2;
echo $email;
if($r['email']==$email)
{  

$msg=$msg."There already is a user with that email <BR>";
$flag="NOTOK";   //setting the flag to error flag.
} 

[FusionComputers]

Could you post the part of the code where you assign a value to $email?

[fairgeek]

when I echo $email it prints the correct email that was passed to it by the form on the previous page, so I know that isn't the problem

$signupdate = date("Y-m-d H:i:s");
$year = date("Y");
$money="5000";
$ip = GetHostByName($REMOTE_ADDR); 
$username = $_POST['username'] ; 
$password = $_POST['password'] ; 
$password2 = $_POST['password2'] ; 
$email = $_POST['email'] ;
$m = $_POST['month'] ;
$d = $_POST['day'] ;
$y = $_POST['year'] ;
$birthdate="$y-$m-$d";
$key = random_string('alnum', 32);  

$flag="OK";  

$msg="";        

[FusionComputers]

Yeah... this has me stumped... Everything looks good. And, the code works on my end...

[cgraz]

1. Have you used mysql_error() as I suggested?
2. How do you know that there is actually a record in your table with that exact email address?

try a SELECT email FROM user without the where clause in phpmyadmin and make sure that you actually have an entry for this email address.

3. It looks as if you're using this to determine if someone registering on your site is registering with a username that is already taken. In that case, if the username is available, you won't get any results back. Try using COUNT() instead

   $query = mysql_query( "SELECT COUNT(*) AS cnt FROM user WHERE email = '" . $email . "'" ) or die(mysql_error());
   $row = mysql_fetch_object( $query ) or die(mysql_error());
   
   if( $row->cnt == 0 )
   {
   	print 'That username is available';
   }
   else
   {
   	print 'That username is already taken';
   }

[fairgeek]

the username portion of the validation script works correctly

[cgraz]

Is this causing your problem?

$q = mysql_query("SELECT email, username FROM user WHERE email='$email'"); 

You can still use mysql_error(), and also run the query in phpmyadmin (or via PHP if you'd like using print_r( $r )), as I suggested, but drop the WHERE clause. Have you done these things? What is the result?

There are only two possible options if the $email variable is holding the correct value:

1. There is some sort of mysql error
2. There is no record in your table holding that particular email address

[fairgeek]

I ran

 SELECT email FROM user WHERE email='[e-mail]' 

in phpmyadmin, and the result showed there were 0 rows containing this email, although the 1 row the database contains has a field called email that holds the address [e-mail]

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0007 sec)
SQL query: SELECT email
FROM user
WHERE email = '[e-mail]'
LIMIT 0 , 30

This field is set as length 255, varchar, unique. What should I do/what settings should I apply so that it recognizes the email in this row - there are no problems with it being upper/lower case, the field is email (not Email) and the address is as I typed... ?

[cgraz]

If you click "Browse" to view all the records in your table, you're still seeing that email address there?

I've seen people insert a record, then delete it (intentionally or on accident) and couldn't figure out why the record they had inserted was not there.

If your SELECT query doesn't return it, then that record isn't in your table.

Run this in phpmyadmin

INSERT INTO user (username, email) VALUES('testU', '[e-mail]');

Do you get a success, or error for duplicate value in email field?

[fairgeek]

It successfully added the values to a new row...

and

SELECT *
FROM `user`
WHERE `email` = '[e-mail]'
LIMIT 0 , 30

still returns no results

[cgraz]

strange. can you export your table structure and data and post here? I'll load it in and take a look.

[fairgeek]

About half an hour ago I tried changing the type of key for username and email to fulltext just in case that had any effect - it didn't. I previously had them set as UNIQUE keys like the password field. I'll change them back again (just in case you are wondering why they are listed as fulltext below).

-- phpMyAdmin SQL Dump
-- version 2.8.0.1
-- http://www.phpmyadmin.net
-- 
-- Generation Time: Jun 16, 2007 at 02:17 PM
-- Server version: 4.1.20
-- PHP Version: 4.3.11
-- 
-- Database: `fairgee_equiknox`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `user`
-- 

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `birthdate` date NOT NULL default '0000-00-00',
  `signupdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip` mediumtext NOT NULL,
  `lastlogin` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `location` mediumtext NOT NULL,
  `money` mediumint(9) NOT NULL default '0',
  `type` mediumtext NOT NULL,
  `energy` double NOT NULL default '0',
  `rank` mediumint(9) NOT NULL default '0',
  `profilehtml` longtext NOT NULL,
  `profilepic` mediumtext NOT NULL,
  `picwidth` int(11) NOT NULL default '0',
  `picheight` int(11) NOT NULL default '0',
  `email` varchar(255) NOT NULL default '',
  `active` int(11) NOT NULL default '0',
  `key` varchar(32) default NULL,
  `tempactive` tinyint(1) NOT NULL default '0',
  `temppass` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `password` (`password`),
  FULLTEXT KEY `email` (`email`),
  FULLTEXT KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `user`
-- 

INSERT INTO `user` (`id`, `username`, `password`, `birthdate`, `signupdate`, `ip`, `lastlogin`, `location`, `money`, `type`, `energy`, `rank`, `profilehtml`, `profilepic`, `picwidth`, `picheight`, `email`, `active`, `key`, `tempactive`, `temppass`) VALUES (1, 'equiknox', 'equi61307', '0000-00-00', '2007-06-13 00:20:23', '71.204.33.116', '2007-06-13 00:20:30', 'central', 5000, 'regular', 50, 0,  '', '', 0, 0, '[e-mail]', 0, 'a1t456e9iky55lo3s93ms03jfd9k3ujs', 0, ''),
(2, 'testU', '', '0000-00-00', '0000-00-00 00:00:00', '', '2007-06-16 13:49:21', '', 0, '', 0, 0, '', '',0, 0, '[e-mail]', 0, NULL, 0, '');

[cgraz]

This returns one record for me (as it should)

SELECT * FROM user
WHERE email = '[e-mail]'

The problem you were having intially is that you misspelled your own email address. You had entered it as [e-mail] - which is why you weren't getting anything back.

It should work now with row #2

BTW, your password is in clear text. I'd edit your post and change it to something else.

[fairgeek]

and I kept looking at the email field and thinking "there's no way it could be misspelled, looks correct to me..." well I guess it was all a typo afterall! Thanks.

I've already changed the password in the database for the 1st row to something else.