CODEWITHSUNDEEP
phpmysql
user1082764
user1082764

Reputation: 2015

search SQL for possibly duplicated user names using PHP

I want to search my database to see if a user that is registering is using a username that is currently in my database. I have registered the same name about 5 times so it SHOULD return false but it returns true.

<?php
function registerUser($userName, $userPassword) {

    $db = new dbinterface();
    $db->connect();

    // check for duplicate data
    $checkduplicates = "SELECT * FROM usersexample WHERE $userName = :userName";
    $myresult = mysql_query($checkduplicates);

    if(mysql_num_rows($myresult) > 0){
        echo $myresult;
        return false;
    }
?>

My table name is usersexample and the field i am searching is userName. ANY and ALL help is appreciated!

Using mysql_num_rows in examples i get this warning: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource.

Upvotes: 1

Views: 469

Answers (6)

Marco
Marco

Reputation: 57573

Did you try:

$checkduplicates = "SELECT userName FROM usersexample 
                    WHERE LOWER('".$userName."') = LOWER(userName)";
$myresult = mysql_query($checkduplicates)
if (!$myresult) {
    die('Invalid query: ' . mysql_error());
} else  {
    $num_rows = mysql_num_rows($myresult);
    if (!$num_rows) {
        die('Invalid query: ' . mysql_error());
    } else return ($num_rows == 0);
}

Please, sanitize user input to avoid SQL injection.

Upvotes: 1

Tom Haws
Tom Haws

Reputation: 1342

I don't know if you are doing something fancy I don't understand, but I would build the query like this:

$checkduplicates = "SELECT * FROM `usersexample` WHERE `userName` = '$userName'";

Or this

$checkduplicates = "SELECT * FROM `usersexample` WHERE `userName` = '".$userName."'";

Upvotes: 0

bjelli
bjelli

Reputation: 10070

Please use prepared statements to avoid sql injection.

As you are using :userName in your SQL it seems you are trying to do this (is your database class based on PDO by any chance?). The :userName part will be replaced by your variable $userName when you do the bindValue.

Use count() in the database to count the number of records found, the database knows best ;-)

$query = $db->prepare("SELECT count(*) AS no_found FROM usersexample WHERE userName = :userName");
$query->bindValue(':userName', $userName, PDO::PARAM_STR);
$query->execute();
$result = $query->fetchObject();

if($result->no_found > 0) 
{
    return false;
}

Upvotes: 1

bowlerae
bowlerae

Reputation: 944

 $getduplicates = mysql_query("SELECT * FROM table WHERE username = $username");
 $duplicates = mysql_num_rows($getduplicates);

 if($duplicates){
     echo "Uh oh someone already has that username";
 }
 else {
     echo "Everything is allllllll good";
 }

Upvotes: 1

Nick Chubb
Nick Chubb

Reputation: 1483

You should try this...

if(mysql_num_rows($myresult) > 0) {
        echo $myresult;
        return false;
    }

It will return false if there is a duplicate username.

Upvotes: 1

CodeZombie
CodeZombie

Reputation: 5377

Use mysql_num_rows() to check the number of rows returned.

Sample:

$myresult = mysql_query($checkduplicates);
$rowcount = mysql_num_rows($myresult);

if($rowcount > 0)
{
    // Account name already in use
}

Upvotes: 1

Related Questions