Prevent duplicate content in database does NOT work!

[angelali]

But in some ebooks and also in some video tutorials i have read and watched, they did the same... 

[AyKay47]

But in some ebooks and also in some video tutorials i have read and watched, they did the same... 

 

then you should toss them out.

[angelali]

I tried your query, but now it is becoming like before, I mean, it is saving even the email or username ias already taken.. Seriously, I have found many people encounter this problem of checking...

[Drummin]

Did you try as AyKay suggested

if(mysql_num_rows(mysql_query($verify)) != 0)

 

Or

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

if (isset($_POST['fname']) && isset($_POST['lname'])&& isset($_POST['emailr']) && isset($_POST['user']) && isset($_POST['pass'])) {
//Assignng variables		
$firstname = stripslashes($_POST['fname']);
$lastname = stripslashes($_POST['lname']);	
$email = stripslashes($_POST['emailr']);
$uname = stripslashes($_POST['user']);
$pwd = stripslashes($_POST['pass']);

//Database
$connect = mysql_connect('localhost', 'root', '') or die ('Connection Failed');
mysql_select_db('registration', $connect) or die ('Connection Failed');

//Registration codes

if (empty($firstname) || empty($lastname) || empty($email) || empty($uname) || empty($pmd)) {
echo '<p class="error">All fields are required to fill!</p>';
return false;
} elseif (strlen($firstname) && (strlen($lastname) < '2')) {
echo '<p class="error">Invalid first name or last name!</p>';
return false;
} elseif (filter_var($firstname, FILTER_VALIDATE_INT) || (filter_var($lastname, FILTER_VALIDATE_INT))) {
echo '<p class="error">First name or last name cannot be integers!</p>';
return false;
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
echo '<p class="error">Email address not valid!</p>';
return false;	
} elseif (strlen($uname) && (strlen($pmd) < '6' )) {
echo '<p class="error">Username or password must be minimum 6 characters!</p>';
return false;
} else {

//Escape variables		
$email = mysql_real_escape_string(stripslashes($_POST['emailr']));
$uname = mysql_real_escape_string(stripslashes($_POST['user']));
$verify = "SELECT * FROM login WHERE emailaddress = '$email' AND username = '$uname'";

if(mysql_num_rows(mysql_query($verify)) != 0)
{
echo '<p class="fail">This email or username is already taken!</p>';
} else {

//Escape other variables		
$firstname = mysql_real_escape_string(stripslashes($_POST['fname']));
$lastname = mysql_real_escape_string(stripslashes($_POST['lname']));	
$pwd = mysql_real_escape_string(stripslashes($_POST['pass']));
$pmd= md5($pwd);
$query = "INSERT INTO login (id, firstname, lastname, emailaddress, username, password) VALUES('', '$firstname', '$lastname', '$email', '$uname', '$pmd')";
mysql_query($query, $connect);
echo '<p class="fail">Successful!</p>';
}
}
}
}
?>

 

[angelali]

yes i tried, but not work..  :'( Oh God, why ???/ It has been 3 hours I'm on this...

[Pikachu2000]

If you set up the indexes in your database table like you said you did, you can't possibly be getting duplicate entries.

[angelali]

I am not getting duplicate contents anymore, the only problem remains is that the message to show the user that an email address or a username has already taken is not displaying.. To do that, I have to read the rows in the fields of both email address and the username in the table Login, that's why I did this. The user should know if an email or username has already taken...

[Drummin]

I've run the last code I posted on a test DB and each time it has performed as expected, saying name is already taken or inserting new values to DB if match is not found.  Mind you, I don't have unique indexes on my DB except for the `id` field which is AUTO_INCREMENT.

[AyKay47]

then this condition:

 

if(mysql_num_rows(mysql_query($verify)) != 0)
{
echo '<p class="fail">This email or username is already taken!</p>';
}

 

is returning FALSE, for some reason. The values are not comparing correctly to the values in the databse. Echo your SQL statement and verify the values.

 

if(mysql_num_rows(mysql_query($verify)) != 0)
{
    echo '<p class="fail">This email or username is already taken!</p>';
}
else
{
    echo $verify;
    exit;
    //rest of code will not get executed
}

[angelali]

@drummin, I did try but dont know why it is not working on mine... Implement it in my codes and try it...you will see it is not working..

[angelali]

there is no mistake in my database, else the code I did earlier would not work even im getting this warning message...

[Drummin]

Can you post your latest version?

[angelali]

Here are the latest one:

 

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

if (isset($_POST['fname']) && isset($_POST['lname'])&& isset($_POST['emailr']) && isset($_POST['user']) && isset($_POST['pass'])) {
//Assignng variables		
$firstname = mysql_real_escape_string(stripslashes($_POST['fname']));
$lastname = mysql_real_escape_string(stripslashes($_POST['lname']));	
$email = mysql_real_escape_string(stripslashes($_POST['emailr']));
$uname = mysql_real_escape_string(stripslashes($_POST['user']));
$pwd = mysql_real_escape_string(stripslashes($_POST['pass']));
$pmd= md5($pwd);
//Database
$connect = @mysql_connect('localhost', 'root', '') or die ('Connection Failed');
@mysql_select_db('registration', $connect) or die ('Connection Failed');

//Registration codes

if (empty($firstname) || empty($lastname) || empty($email) || empty($uname) || empty($pmd)) {
echo '<p class="error">All fields are required to fill!</p>';
return false;
} elseif (strlen($firstname) && (strlen($lastname) < '2')) {
echo '<p class="error">Invalid first name or last name!</p>';
return false;
} elseif (filter_var($firstname, FILTER_VALIDATE_INT) || (filter_var($lastname, FILTER_VALIDATE_INT))) {
echo '<p class="error">First name or last name cannot be integers!</p>';
return false;
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
echo '<p class="error">Email address not valid!</p>';
return false;	
} elseif (strlen($uname) && (strlen($pmd) < '6' )) {
echo '<p class="error">Username or password must be minimum 6 characters!</p>';
return false;
} else {
$verify = "SELECT * FROM login WHERE emailaddress = '$email' AND username = '$uname'";
if(mysql_num_rows(mysql_query($verify)) !== 0)
{
echo '<p class="fail">This email or username is already taken!</p>';
} else {
$query = "INSERT INTO login (id, firstname, lastname, emailaddress, username, password) VALUES('', '$firstname', '$lastname', '$email', '$uname', '$pmd')";
mysql_query($query, $connect);
echo '<p class="fail">Successful!</p>';
}
}
}
}
?>

 

Here is the one which worked earlier but that that warning message: Warning: mysql_num_rows() expects parameter 1 to be resource, string given in C:\xampp\htdocs\miniimagehosting\register.php on line 60

 if(mysql_num_rows($verify) !== 0)

[litebearer]

mysql_real_escape_string MUST be done AFTER connecting to the database

[angelali]

Ok I moved my database connection above the variables where are the my_sql_real....

 

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

if (isset($_POST['fname']) && isset($_POST['lname'])&& isset($_POST['emailr']) && isset($_POST['user']) && isset($_POST['pass'])) {

//Database
$connect = @mysql_connect('localhost', 'root', '') or die ('Connection Failed');
@mysql_select_db('registration', $connect) or die ('Connection Failed');

//Assignng variables		
$firstname = mysql_real_escape_string(stripslashes($_POST['fname']));
$lastname = mysql_real_escape_string(stripslashes($_POST['lname']));	
$email = mysql_real_escape_string(stripslashes($_POST['emailr']));
$uname = mysql_real_escape_string(stripslashes($_POST['user']));
$pwd = mysql_real_escape_string(stripslashes($_POST['pass']));
$pmd= md5($pwd);

[Drummin]

How do you think $pmd= md5($pwd); will work with your $pmd validation code?

AND

as litebearer and I have pointed out mysql_real_escape_string should be done after connecting to the database and is only needed before query as in my examples.

 

Also,

if(mysql_num_rows(mysql_query($verify)) !== 0)

Should be

if(mysql_num_rows(mysql_query($verify)) != 0)

 

ALSO

Pikachu2000's post regarding trim() is valid and should be included for user input.

[AyKay47]

On top of everything that has been said, use the code that I have provided in my last reply to verify the data in the SQL statement.

[angelali]

@Drummin, yes I tried that too earlier... and has not worked.. By the way for the password validation, what do you suggest me? I want the password to be encrypted and also it should be a minimum of 6 characters...

[Drummin]

You do the md5 before INSERT as in my example.

[Pikachu2000]

This really isn't a difficult concept if you understand it. There are 2 ways to accomplish it, either check for the existence of either value with a SELECT COUNT() query before running an insert query, or attempt the INSERT query, and if the db returns a DUPLICATE KEY error, then the record already exists.

 

Assuming $username and $email have already been prepared for use in the query string, this checks for the existence of the values. This is not complete code and can't simply be pasted into your script, but you can make a few changes to it, test it and look at it so you understand the logic behind it. Specifically, I wouldn't use die() to actually give a user an error message, and I'd log any mysql errors along with the query that caused them. I've simplified this code because unless you understand the logic behind the way this works, you'll continue to have a hard time with it.

 

<?php
$query = "SELECT COUNT(1) FROM table WHERE username = '$username' OR email = '$email'";
if( $result = mysql_query($query) ) {
   $array = mysql_fetch_row($result);
   if( $array[0] === 0 ) {
      $query = "INSERT INTO table (username, email) VALUES ('$username', $email)";
      if( $result = mysql_query($query) ) {
         if( mysql_affected_rows() !== 1 ) {
            die( 'Sorry, there was a database error(1)' );
         } else{
            echo 'Username and password successfully registered.';
         }
      } else {
         die( 'Sorry, there was a database error(' . mysql_error() . ')(2)' );
      }
   } else {
      die( 'Sorry, the username or email address you\'ve entered is already in use.' );
   }
} else {
   die( 'Sorry, there was a database error(' . mysql_error() . '(3)');
}

 

I purposely didn't comment the code so you'd be forced to read it and figure it out, step by step.

[angelali]

Well, I will try it... By the way, is it necessary to encrypt password in database?

[angelali]

I successfully corrected the MD5 one. By the way, I inserted it before the INSERT in the codes not in the variables like before... And also, I want to know, if I include the Trim() in the same line as the mysql_real_escape and stripslashes, is it good? Just a question of curiosity, like this:

 

$uname = mysql_real_escape_string(stripslashes(trim($_POST['user'])));

[Pikachu2000]

It should be hashed with a strong hashing algorithm, and a salt. But, let's take one step at a time. It's easier to add components to something that already works rather than add more code to broken code, and then try to figure out why twice as much code is still broken.

[Drummin]

$uname = mysql_real_escape_string(stripslashes(trim($_POST['user'])));

That should be fine.  You want to make sure you're not comparing variables with spaces to those that don't, and not complicating the problem by INSERTING values with spaces.

[Pikachu2000]

You should not be using stripslashes() without first checking to see if magic_quotes_gpc() is on or not.