Checking to see if username is already in use

[smc]

Hiya,

Okay well this is confusing me. This is the code I have right now but for reasons you can probably see it's not doing the job correctly

[code=php:0]
if ( $submit == "true" ) {
$cms_root_path = '../';
include($cms_root_path . 'config.php');
$dbh=mysql_connect ($dblocation, $dbusername, $dbpassword)
or die ("Sorry - I got lost and couldn't find the database. Please try back later.");
$rs = @mysql_select_db("$dbdatabase", $dbh)
or die("Sorry - the database decided to give me trouble. Please try back after I've put it in it's place.");
$execute=mysql_query("select * from users where username=$username")
or die (mysql_error());
}
[/code]

Basically I want the PHP script to check and make sure the username they are trying to submit is not already in use before adding it to the DB.

Any help on where I should go from here would be much appreciated!!

[Psycho]

[code]<?php
if ( $submit == "true" ) {
$cms_root_path = '../';
include($cms_root_path . 'config.php');
$dbh=mysql_connect ($dblocation, $dbusername, $dbpassword)
or die ("Sorry - I got lost and couldn't find the database. Please try back later.");
$rs = @mysql_select_db("$dbdatabase", $dbh)
or die("Sorry - the database decided to give me trouble. Please try back after I've put it in it's place.");
$execute=mysql_query("select * from users where username=$username")
or die (mysql_error());
}
if (mysql_num_rows($execute)>0) {
  echo "That username is already used";
} else {
  echo "That is a new username";
}
?>[/code]

[smc]

$execute=mysql_query("select * from users where username=$username")
or die (mysql_error());

That terminates the script though because it doesn't find anything if the person is a new user

And when it is a duplicate user I get this


Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/een/public_html/admin/admin_registeruser.php on line 53
Sorry - I messed up and didn't execute the SQL query. Please try back later.

[Hypnos]

You will get a warning if you do mysql_num_rows on an empty result set. You can avoid it with an if statement, or just put an @ in front of mysql_num_rows.

Like:
if(@mysql_num_rows($result) > 0)

That will hide any warnings from that function.

[smc]

Alright now it just keeps continuing down the line until it reaches the main SQL where it detects there is a duplicate primary key instead of stopping at the die; point. See if you can make any sense of this:

[code=php:0]
//This checks to make sure the username isn't already in use

if ( $submit == "true" ) {
$cms_root_path = '../';
include($cms_root_path . 'config.php');
$conn = mysql_connect ($dblocation, $dbusername, $dbpassword)
or die ("Sorry - I got lost and couldn't find the database. Please try back later.");
$result = @mysql_select_db("$dbdatabase", $conn)
or die("Sorry - the database decided to give me trouble. Please try back after I've put it in it's place.");
$query=@mysql_query("select * from users where username=$username");
$tempvar = @mysql_num_rows($execute);
if ( $tempvar >0 ){
$usernamedused = "used";
}
}

if ( $usernameused == "used" ){
$errortxt = "Sorry, that username is already in use.";
$cms_root_path = '././';
include($cms_root_path . 'admin_registerusers_fail.php');
die;
}
[/code]

[Philip]

$tempvar = @mysql_num_rows($execute);
should be
$tempvar = @mysql_num_rows($query);

This is because your query variable is "query" not "execute"


And personally, I don't think you should use the @ sign, it really helps in debugging. If you are able to completely debug your code, you shouldn't have to worry about these errors showing up anywhere ;)

[Nhoj]

A more effecient way of doing it.

[code=php:0]//This checks to make sure the username isn't already in use

if ($submit == 'true') {
include('../config.php');
$conn = @mysql_connect($dblocation, $dbusername, $dbpassword)
or die ('Sorry - I got lost and couldn\'t find the database. Please try back later.');
$result = @mysql_select_db($dbdatabase, $conn)
or die('Sorry - the database decided to give me trouble. Please try back after I\'ve put it in it\'s place.');
$found = mysql_result(mysql_query('SELECT count(0) FROM `users` WHERE `username` = "'.$username.'" LIMIT 1'), 0, 0);

if ($found > 0){
$errortxt = 'Sorry, that username is already in use.';
include('././admin_registerusers_fail.php');
die;
}
}[/code]

[smc]

Ah thanks for that catch

Nhoj I've used your suggestion for the consolidated version using $found, if it wouldn't be too much trouble though could you describe how the line $found works (ie. count(0) ) so I can learn how to do it myself?