search SQL for possibly duplicated user names using PHP

Question

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.

Use `LOWER` for both parts if you want to avoid guly surprises... — Marco, Jan 23 '12 at 23:19
I think the main thing is $myresult is only executing the query, it's not counting the results. — bowlerae, Jan 23 '12 at 23:31
if it is not counting the result, how can i count the result? — user1082764, Jan 23 '12 at 23:35
Well, viewing the code you posted, you aren't closing the function braces, and your query isn't setup correctly. as far as I know, mysql_query() doesn't allow binding parameters (i.e. `:userName`), and if it did, I don't see where you're binding it. Also, you're checking that a column with the name being the value of $userName (whatever that is) is equal to :userName, which unless it is ":userName", it isn't. Which means it will return 0 rows. — imkingdavid, Jan 24 '12 at 00:09

score 1 · Answer 1 · answered Jan 23 '12 at 23:20

1

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
}

answered Jan 23 '12 at 23:20

CodeZombie

5,367
3
30
37

I got this error "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource" using this code "$myresult = mysql_num_rows($checkduplicates);" – user1082764 Jan 23 '12 at 23:22
That means something is incorrect with your MySQL query most likely. – Nick Chubb Jan 23 '12 at 23:23
@user1082764: Check my sample. You need to pass the resource, not the SQL statement. – CodeZombie Jan 23 '12 at 23:23
it gives me the same warning: "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource" – user1082764 Jan 24 '12 at 00:22
This code is not doing any error checking. It will break if the query fails. How to add proper error checking is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) – Pekka Jan 24 '12 at 00:35

Marco · Answer 2 · 2012-01-24T00:38:48.750

1

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.

edited Jan 24 '12 at 00:38

answered Jan 23 '12 at 23:21

Marco

56,740
14
129
152

I believe the left side of the WHERE equation needs to be a column name, not a user name. And there should be apostrophes around the user name on the right side. – Tom Haws Jan 23 '12 at 23:57
@TomHaws: in a query both parts of `WHERE` could have field names! – Marco Jan 24 '12 at 00:11
yes, it gives me this warning: "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource" – user1082764 Jan 24 '12 at 00:17
This code is not doing any error checking. It will break if the query fails. How to add proper error checking is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) – Pekka Jan 24 '12 at 00:35
@Pekka: do you like it more now? Thanks for your comment :) – Marco Jan 24 '12 at 00:39
@user1082764: watch edited post and check if your query is doing something bad... – Marco Jan 24 '12 at 00:41
@Marco, yes, but the WHERE is not useful if it equates two equal strings 'username' and 'username'. Legal, yes. Userful, no. He needs to check whether a `column name` is equal to a 'string'. – Tom Haws Jan 24 '12 at 04:08
@TomHaws: why do you think the second part does not contain a column name? Watch the query: `SELECT userName`, so *userName* is a column... am I missing something? – Marco Jan 24 '12 at 06:27
@Marco: Thanks for your patience. Here's what I see: 1) the column name is `userName`. 2) LOWER('".$userName."') is not the column name because it varies. 3) LOWER(userName) is not the column name because `username` is not the column name. So I am thinking I don't see the column name in the WHERE clause. – Tom Haws Jan 24 '12 at 22:11

score 1 · Answer 3 · answered Jan 23 '12 at 23:22

1

You should try this...

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

It will return false if there is a duplicate username.

answered Jan 23 '12 at 23:22

Nick Chubb

1,463
6
16
26

This looks right but i have updated my question, i keep getting a php warning that i added to the bottom of my question – user1082764 Jan 23 '12 at 23:29

bowlerae · Answer 4 · 2012-01-23T23:30:05.877

1

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

edited Jan 23 '12 at 23:30

answered Jan 23 '12 at 23:23

bowlerae

924
1
14
37

1

mysql_num_rows() expects a MySQL resource and not a SQL statement. – CodeZombie Jan 23 '12 at 23:25
The query is right. B just didn't process it right. Use bowlerae's or my SQL. – Tom Haws Jan 23 '12 at 23:58
This code is not doing any error checking. It will break if the query fails. How to add proper error checking is outlined in the [manual on `mysql_query()`](http://php.net/mysql_query) or in this [reference question.](http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension) – Pekka Jan 24 '12 at 00:36
1

look I'm giving them the same responses as everyone else, they wrote the question without error checking to I'm giving the answer without error checking it's not my responsibility to write the whole dang thing for them. – bowlerae Jan 24 '12 at 00:40

score 1 · Answer 5 · answered Jan 23 '12 at 23:42

1

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

answered Jan 23 '12 at 23:42

bjelli

9,752
4
35
50

ill do my homework on PDO because i have never heard of it before. I just have removed security for legibility, i am just having trouble getting the database to check for duplicates :( – user1082764 Jan 23 '12 at 23:46
Prepared statements are the best, but not the *only* way to avoid SQL injection. It's perfectly possible to use `mysql_query()` if you know what you are doing. Still +1, nice example... Although, like the other answers, it *could* use some error checking – Pekka Jan 24 '12 at 00:35

score 0 · Answer 6 · answered Jan 23 '12 at 23:55

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."'";

search SQL for possibly duplicated user names using PHP

6 Answers6