Prevent duplicate content in database does NOT work!

[angelali]

I am having a big problem in MySQL and a part in my PHP coding as well.

 

I created a registration form, where the user will have to fill his names, email address, choose a username and so on. I do not want duplicate email address and username in my database, thus, if a user choose either an email address or a username which has already taken, he will be notified.

 

To prevent this duplication, I have set both the email address and username fields as UNIQUE KEY in MySQL.

 

My problems are:

Even by setting both the username and email address fields as UNIQUE KEY, it is not working as I can register using the same email address or username. How to solve this?

 

I have coded also to prevent this problem of duplicate so that the user will be notified to choose another email or username, but I am having a warning.

 

My PHP codes:

<?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($_POST['fname']);
$lastname = mysql_real_escape_string($_POST['lname']);    
$email = mysql_real_escape_string($_POST['emailr']);
$uname = mysql_real_escape_string($_POST['user']);
$pwd = mysql_real_escape_string($_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 {
$query = "INSERT INTO login (id, firstname, lastname, emailaddress, username, password) VALUES('', '$firstname', '$lastname', '$email', '$uname', '$pmd')";
mysql_query($query, $connect);    
if (mysql_num_rows(mysql_query("SELECT * FROM login WHERE emailaddress = '$email' username = '$uname'"))) {
echo '<p class="fail">This email or username is already taken!</p>';
}
}
}
}
?> 

 

The warning message I am getting:

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\miniimagehosting\register.php on line 44

[AyKay47]

this line:

 

if (mysql_num_rows(mysql_query("SELECT * FROM login WHERE emailaddress = '$email' username = '$uname'"))) {

 

should read:

 

if (mysql_num_rows(mysql_query("SELECT * FROM login WHERE emailaddress = '$email' AND username = '$uname'"))) {

 

you forgot the AND in the SQL statement.

[Pikachu2000]

There are several problems with your code, but let's start with this: Why are you running the insert query before the query to check if the data already exists? Using that logic, the second query will always show that the values are already there, because you just inserted them.

[angelali]

Dude, thank you for replying, I swear to God, I swear to God that before I posted this issue here, I did put the "AND", but I got the error  and even Dreamweaver told me it was an error. But anyway, I did it again and now it is working and also not working... I mean sometimes it is not storing in the database and notifies the user his email address is already taken and sometimes it is saving without notification. Why?

 

I want to know also one thing, even I put the two fields which are the email address and username as UNIQUE key, why duplicate content is still stored? It's weird!

 

Here are my codes again, I changed it:

<?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($_POST['fname']);
$lastname = mysql_real_escape_string($_POST['lname']);	
$email = mysql_real_escape_string($_POST['emailr']);
$uname = mysql_real_escape_string($_POST['user']);
$pwd = mysql_real_escape_string($_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))) {
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);	
}
}
}
}
?>

[angelali]

Pikachu, I corrected it, can you tell me the other errors...as duplicate email addresses and username are still being saved...

[Pikachu2000]

It may be something to do with the way the indices are set up. Run this query in phpMyAdmin, and post the output.

 

EXPLAIN CREATE TABLE login

[angelali]

huhh... I got this #1064 - 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 'CREATE TABLE login' at line 1 

[Pikachu2000]

Sorry, I meant SHOW CREATE TABLE, not EXPLAIN . . .

[angelali]

Here they are:

 

CREATE TABLE `login` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`firstname` varchar(50) NOT NULL,

`lastname` varchar(50) NOT NULL,

`emailaddress` varchar(50) NOT NULL,

`username` varchar(50) NOT NULL,

`password` varchar(50) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `emailaddress` (`emailaddress`,`username`),

UNIQUE KEY `username` (`username`),

UNIQUE KEY `emailaddress_2` (`emailaddress`,`username`),

UNIQUE KEY `emailaddress_3` (`emailaddress`,`username`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1

 

[angelali]

I have re-created the database, here is your results now:

 

CREATE TABLE `login` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`firstname` varchar(50) NOT NULL,

`lastname` varchar(50) NOT NULL,

`emailaddress` varchar(50) NOT NULL,

`username` varchar(50) NOT NULL,

`password` varchar(50) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `emailaddress` (`emailaddress`,`username`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

 

[Pikachu2000]

You don't need the indices that span both emailaddress and username, so you can drop those. You do need to add a UNIQUE index to the emailaddress field. When you define an index that spans more than one field, the values in all of those fields together are considered the value for the purpose of the index. So, the way it's currently defined, username must be unique, and the combined username and email address fields must be unique, but emailaddress does not need to be unique on its own.

[Pikachu2000]

Note that my post above refers to the first SHOW CREATE TABLE output you posted . . .

[angelali]

So, if I understand well, I should make only one field UNIQUE right? And it is the email address only to be unique but not the username? 

[Drummin]

Excuse me for jumping in here, but doesn't mysql_num_rows() return a value regardless of results?  Shouldn't mysql_num_rows be compared to a value?

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

 

Or this way

$verify = "SELECT username FROM login WHERE emailaddress = '$email' AND username = '$uname'";
$result = mysql_query($verify);
$query_data = mysql_fetch_row($result);
if ($query_data[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);	
}

 

Again sorry for jumping in.

[Pikachu2000]

So, if I understand well, I should make only one field UNIQUE right? And it is the email address only to be unique but not the username? 

 

Not quite. You need a UNIQUE index on username, and a UNIQUE index on emailaddress. You don't need a UNIQUE index that spans both username and emailaddress.

[angelali]

I recreated it and it is working now. No duplicate content  But now, I will try the code you suggested, so that I can notify the user about the duplication...

[Pikachu2000]

Excuse me for jumping in here, but doesn't mysql_num_rows() return a value regardless of results?  Shouldn't mysql_num_rows be compared to a value?

 

Technically, that would be a cleaner way to code it, but using an if( mysql_num_rows($result) ) conditional works also because mysql_num_rows() returns a zero for no rows returned, which also evaluates to FALSE in a loose comparison. The more efficient way to handle queries like checking if a username or email address is already in the database is typically a SELECT COUNT() query, of course.

[angelali]

@Drummin, I tried your code...but in vain..  :'(

[Pikachu2000]

Take into consideration that 'username' and 'username ' are different strings. You need to trim values while validating them.

[Drummin]

And using COUNT()

$verify = "SELECT COUNT(*) as cnt FROM login WHERE emailaddress = '$email' AND username = '$uname'";
$result = mysql_query($verify);
$query_data = mysql_fetch_row($result);
if ($query_data['cnt']==1){
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);	
}

[angelali]

You mean when declaring its variable above, I should insert the Trim in it? I did it like this:

 

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

[angelali]

@Drummin, nope does not work! I did code a similar thing some days ago, and I did (mysql_errno($db)> 0) and it worked in another website, but it is not working in this one, thus I used another way...like you see above in the codes...

[angelali]

Guys, I changed the codes, it is working but I got this warning:

Warning: mysql_num_rows() expects parameter 1 to be resource, string given in C:\xampp\htdocs\miniimagehosting\register.php on line 61

 

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

[Drummin]

Slight modification putting mysql_real_escape_string after DB connect and just before DB query.  Not sure it will help with current error but at least $pwd validation should work not having a md5($pwd).

 

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

[AyKay47]

because $verify is an SQL statement, a mysql_query resource needs to be passed to mysql_num_rows. e.g:

 

$verify = "SELECT * FROM login WHERE emailaddress = '$email' AND username = '$uname'";

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