Jump to content

Prevent duplicate content in database does NOT work!


angelali

Recommended Posts

  • Replies 57
  • Created
  • Last Reply

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>';
}
}
}
}
?>

 

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
}

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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'])));

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Archived

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


×
×
  • Create New...

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.