Jump to content

Prevent duplicate content in database does NOT work!


angelali

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 57
  • Created
  • Last Reply

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

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.