[SOLVED] Checking the database for duplicates?

[Epidemic]

I am making a registration form using PHP. This is what I have got so far.

 

// Get values from form
$username=$_POST['username'];
$password=$_POST['password'];

// Insert data into mysql
$sql="INSERT INTO $tbl_name(username, password)VALUES('$username', '$password')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
header("location:index.php?r=1");
}

else {
header("location:index.php?r=2");
}

// close connection
mysql_close();

 

However this script doesnt check for multiple inputs so if two people sign up with the same username it cannot check. How can I check if the username is already in use and if it is how can I get it to direct to index.php?r=3

 

Thanks,

 

Josh

[shawjames]

I do it with a query like this:

 

SELECT count(*) FROM db_table WHERE username='$username'

 

then, if $result[0] > 0 there is already an entry with that $username.  go to your r=3

[revraz]

<?php
function checkUnique($table, $field, $compared){
if (get_magic_quotes_gpc()) {
$table = stripslashes($table);
$field = stripslashes($field);
$compared = stripslashes($compared);
}
$table = mysql_real_escape_string($table);
$field = mysql_real_escape_string($field);
$compared = mysql_real_escape_string($compared);

$result = mysql_query("SELECT $field FROM $table WHERE $field = '$compared'");
if(mysql_num_rows($result)==0) {
return TRUE;
}
else {
return FALSE;
}
}
?>

[Epidemic]

I tried this but it didn't work.

 

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form
$username=$_POST['username'];
$password=$_POST['password'];

//Account check
$sql="SELECT count(*) FROM $tbl_name WHERE username='$username'";
$result=mysql_query($sql);

//Check to see if username exists or not.
if($result > 0){
header("location:register.php?r=1");
}

// Insert data into mysql
$sql="INSERT INTO $tbl_name(username, password)VALUES('$username', '$password')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
header("location:index.php?r=1");
}

else {
header("location:index.php?r=2");
}

// close connection
mysql_close();
?>

 

Any idea's on why?

[abdfahim]

The problem is in the following line

//Check to see if username exists or not.
if($result > 0){
header("location:register.php?r=1");
}

$result is a php result resource and dont have any straight forward numeric or text value. You have to process this variable to get what you desire. In this case, you should write

 

//Check to see if username exists or not.
if(mysql_num_rows($result) > 0){
header("location:register.php?r=1");
}

 

[Epidemic]

It still doesnt work.

[abdfahim]

can you tell exactly what is the problem now?

[Epidemic]

It still inoputs the new user into the database even if the username already exists.

[Stephen]

Check mysql_num_rows with the query selecting the user from the database. If its 0 then let them register.

[abdfahim]

ok, put an exit after header,like

if(mysql_num_rows($result) > 0){
header("location:register.php?r=1");
exit();
}

 

this should solve. If problem still persists, post your latest code.

[Epidemic]

The exit(); fixed it . Thanks alot!

[Epidemic]

Sorry for the double post but when I uploaded it to my webhosting from my computer it stopped working. It now just goes to:

 

register.php?r=1

 

Whenever you try to register with a username that hasn't been used.

 

I do know that the PHP versions have changed from my server to the server I uploaded to. They changed from 5.2.6 to 5.2.5

 

[GingerRobot]

That would be because you are selecting a row number, then grabbing the number of rows. It will always return one row. You need to check the value of that row:

 

$sql="SELECT count(*) FROM $tbl_name WHERE username='$username'";
$result=mysql_query($sql);
$num = mysql_result($result,0);
if($num > 0){
    header("location:register.php?r=1");
    exit();
}

[Epidemic]

Unfourtantly it doesnt work, your code allows it to register multiple users of the same username.

[GingerRobot]

Do you think we could see the actual code you are now using? It's a bit difficult to keep track of without it.

[Epidemic]

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form
$myusername=$_POST['username'];
$mypassword=$_POST['password'];

//Account check
$sql="SELECT count(*) FROM $tbl_name WHERE username='$username'";
$result=mysql_query($sql);
$num = mysql_result($result,0);

//Check to see if username exists or not.
if($num > 0){
header("location:register.php?r=1");
exit();
}

// Insert data into mysql
$sql="INSERT INTO $tbl_name(username, password)VALUES('$myusername', '$mypassword')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
header("location:index.php?r=1");
}

else {
header("location:index.php?r=2");
}

// close connection
mysql_close();
?>

 

Thats the complete code, it doesn't contain mySQL user info (for obvious reasons )

[Epidemic]

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Get values from form
$myusername=$_POST['username'];
$mypassword=$_POST['password'];

//Account check
$sql="SELECT count(*) FROM $tbl_name WHERE username='$username'";
$result=mysql_query($sql);
$num = mysql_result($result,0);

//Check to see if username exists or not.
if($num > 0){
header("location:register.php?r=1");
exit();
}

// Insert data into mysql
$sql="INSERT INTO $tbl_name(username, password)VALUES('$myusername', '$mypassword')";
$result=mysql_query($sql);

// if successfully insert data into database, displays message "Successful".
if($result){
header("location:index.php?r=1");
}

else {
header("location:index.php?r=2");
}

// close connection
mysql_close();
?>

 

Thats the complete code, it doesn't contain mySQL user info (for obvious reasons )

[Epidemic]

Cant anyone help me?

[abdfahim]

you store the posted username in the variable $myusername and use the variable $username in your sql query!! how this will work?

[Epidemic]

You just fixed it for me

 

Thanks sooo much!