Is there a better way?? Check fields against db

[ehask]

Ok I know there has to be a better way!! I am using javascript to check all required fields are filled in but now need to make sure there username(email addy) and their chosen screen name are not already in the database.

Here is my code:

   // Validate For Duplicates!!!!
     $check_user ="Select * from user_login where username ='".$email1."'";
     $result_user = mysql_query($check_user); 
     $total_users = mysql_num_rows($result_user);
     $check_scrn ="Select * from user_login where name ='".$scrnname."'";
     $result_scrn = mysql_query($check_scrn); 
     $total_scrn = mysql_num_rows($result_scrn);
       if ($total_users == 0 && $total_scrn == 0) {
         // Do first insert
         $ins = mysql_query($addus);
         $id = mysql_insert_id();
         $password = randomPass();

     // Now do second insert to Authentication table
     mysql_query($addauth);
     mailnewuser($email1,$password,$firstname);
     unset($cmd);
     echo '<meta http-equiv="refresh" content="0;URL=add_use.php?user_added=yes">'; }
    else {
     echo '<table align="center" width="450"><tr><td colspan="2" bgcolor="red"><div align="center">Screenname or Email Exists!! Please Change</div></td></tr></table>';
    } 

Both of the fields in each of their respective tables are set to unique. The form is being submitted to itself so that if there is an error I can repopulate the form but I need a way to validate and then return which of the two is a duplicate instead of saying
Screenname or Email Exists!! Please Change

Any help would be awesome!!!

[ZibingsCoder]

This one is pretty much already done for you...

<?php
// Validate For Duplicates!!!! 
     $check_user ="Select * from user_login where username ='".$email1."'"; 
     $result_user = mysql_query($check_user);  

     $total_users = mysql_num_rows($result_user); 
     $check_scrn ="Select * from user_login where name ='".$scrnname."'"; 
     $result_scrn = mysql_query($check_scrn);  

     $total_scrn = mysql_num_rows($result_scrn); 
       if ($total_users == 0 && $total_scrn == 0) { 
         // Do first insert 
         $ins = mysql_query($addus); 
         $id = mysql_insert_id(); 
         $password = randomPass(); 

     // Now do second insert to Authentication table 
     mysql_query($addauth); 
     mailnewuser($email1,$password,$firstname); 
     unset($cmd); 
     echo '<meta http-equiv="refresh" content="0;URL=add_use.php?user_added=yes">'; } 
    else {
    	if ( $total_users != 0 )
    	{
     		echo '<table align="center" width="450"><tr><td colspan="2" bgcolor="red"><div align="center">Email already Exists!! Please Change</div></td></tr></table>'; 
    	}

    	else if ( $total_scrn != 0 )
    	{
    		echo '<table align="center" width="450"><tr><td colspan="2" bgcolor="red"><div align="center">Screename already exists!! Please change</div></td></tr></table>';
    	}
    }

So...uh...there ya go.

[TheDefender]

Same principle as ZibingsCoder's, but a little differently done. This way, you only have the table code once, instead of twice. This way, you can echo both in the same table cell as well.

$error = "";
if ($total_users == 0 && $total_scrn == 0) { 

     // Do first insert 
     $ins = mysql_query($addus); 
     $id = mysql_insert_id(); 
     $password = randomPass(); 

     // Now do second insert to Authentication table 
     mysql_query($addauth); 
     mailnewuser($email1,$password,$firstname); 
     unset($cmd); 
     echo '<meta http-equiv="refresh" content="0;URL=add_use.php?user_added=yes">'; 

} else {

  if($total_users != 0) {
    $error .= "Email Address already exists.<BR />";
  } elseif($total_scrn != 0) {
    $error .= "Username already exists.<BR />";
  }

  echo '<table align="center" width="450"><tr><td colspan="2" bgcolor="red"><div align="center">$error</div></td></tr></table>'; 
}

[ehask]

Man I dont know how I did not figure that out. Been working to hard on this app I guess

Thanks!!!

Eric

[mrhappiness]

why not simply having the username and the mail to be UNIQUE?

than you could do your insert

if mysql_insert_id returns something the entry has been inserted otherwise check mysql_error, it tells you something about "duplicate key" and also the column's name causing the duplicate key error

for example

CREATE TABLE user_login(
user_id int primary key auto_increment,
user_name varchar(50),
user_mail varchar(200),
UNIQUE KEY user_name (user_name),
UNIQUE KEY user_mail (user_mail)
);

and your php

$sql = "INSERT INTO users_login SET user_name = '".$thename."', user_mail = '".$themail."'";
mysql_query($sql);
if ($id = mysql_insert_id())
  echo 'Entry successfully made.';
elseif (preg_match('%(#1062)(.*) (.*)$%si', mysql_error(), $matches))
  if ($matches[3] == 2)
    echo 'Error: Name already exists';
  elseif ($matches[3] == 3)
    echo 'Error: Mail already exists';

this does one INSERT only and not two selects and one insert and i therefor consider it to be have a better performance

the message for example is

#1062 - Duplicate entry 'nomail@localhost' for key 3

key 1 is the primary key