Why is this code to check duplicate usernames not executing properly?

Question

$conn properly connects to the database.

The users table of the database consists of the following fields id,name, email, username and password. One of the entries in the table contains benedict as the value for the username.

Code:

$userslist = $conn->prepare("SELECT * FROM users WHERE username=?");
$userslist->bind_param("s",$user);
$usersresult=$userslist->execute();
if($userslist->num_rows>0)
{
    $userErr="Username already exists";
    $errors++;
}

Problem:

When I enter a username(which is being stored in $user) with benedict as the value, the code does not detect duplicate id in spite of already having such a username. Further, $userslist->num_rows when printed shows 0.

On the contrary the following code correctly identifies, that a duplicate id already exists and prints the errror. (this proves there is no connection error or table errors)

$query="SELECT * FROM users WHERE username='".$user."'";
    $qresult=mysqli_query($conn,$query);
    if($qresult->num_rows>0)
    {   $userErr="Username already exists";
    $errors++;
    }

I am aware that unique key and PDO is a better solution. But why it doesn't prints proper results while using prepared statements.

affected rows has meaning for update- insert- delete queries. Should you not need found_rows or something a like? edit: num_rows it is — Ivo P, Jun 30 '17 at 08:30
can you output the value of $user? affected_rows should act like num_rows in a SELECT statement. What result do you get if you remove the WHERE clause from your statement? — JParkinson1991, Jun 30 '17 at 08:35
echo $user prints the entered text in username field correctly. When I enter benedict it prints benedict. I tried changing affected_rows to num_rows. Still errors is not being displayed. When I remove the where clause and bin_param placeholder also, it does not print the error. — , Jun 30 '17 at 08:47
Sorry i meant what affected_rows result do you get without the where clause? — JParkinson1991, Jun 30 '17 at 08:51
Prints -1 even after removing where clause and bin_param placeholders. — , Jun 30 '17 at 08:55
savest way would be, to make the username having an unique constraint. When inserting a duplicate name, the query will fail and return an error. Let your php code intercept this error number and give the same "already exsits error" you generate now. That way it is sure no one inserts that name just between the check and the actual insert — Ivo P, Jun 30 '17 at 08:57
that would be apart from the query to start: make the column Username unique using a tool like phpmyadmin or whatever you use to maintain/setup your database. then check if errno == 1062. if so: the query failed because of duplicate i will edit my answer below — Ivo P, Jun 30 '17 at 10:10
$query="SELECT * FROM users WHERE username='".$user."'"; $qresult=mysqli_query($conn,$query); if($qresult->num_rows>0) { $userErr="Username already exists"; $errors++; } This is correctly pointing out that the username already exists but prepared statement does not shows the error — , Jun 30 '17 at 10:18

JParkinson1991 · Answer 1 · 2017-06-30T08:43:15.417

1

An affected rows result of: -1 indicates the query returned an error and therefore wasnt executed.

Check your query for errors in column/tables names etc.

Is your connection working?

Please try the following to debug:

$userslist = $conn->prepare("SELECT * FROM users WHERE username=?");
$userslist->bind_param("s",$user);
$usersresult=$userslist->execute();
print_r($conn->error);

edited Jun 30 '17 at 08:43

answered Jun 30 '17 at 08:37

JParkinson1991

1,256
1
7
17

score 0 · Answer 2 · answered Jun 30 '17 at 08:32

0

Id suggest using PDO to start with, it is much safer for DB queries. http://php.net/manual/en/book.pdo.php . I would also suggest that you use a count for this and not a SELECT *. This is a wasteful check to see if a row exists, it will be more efficient by using a count.

You would do this using PDO like this.

$stmt = $db->prepare("SELECT count(1) FROM users WHERE username=?");
$stmt->execute(array($user));
$result = $stmt->fetch();
if($result)
{
if($result[0] == 1) echo "Username already taken";
else "username free";
}
else
{
echo "error";
}

answered Jun 30 '17 at 08:32

Dan Hastings

3,241
7
34
71

I get that PDO is better but why the code in the question does not work? If I dont use preapred statements it works. $query="SELECT * FROM users WHERE username='".$user."'"; $qresult=mysqli_query($conn,$query); if($qresult->num_rows>0) { $userErr="Username already exists"; $errors++; } This is correctly pointing out that the username already exists but prepared statement does not shows the error. – Jun 30 '17 at 10:21

Ivo P · Answer 3 · 2017-06-30T10:17:10.237

0

So error is not affected row: check why the query failed:

`
 <?php
 $userslist = $conn->prepare("SELECT * FROM users WHERE username=?");
 $userslist->bind_param("s",$user);
 $usersresult=$userslist->execute();



 if($userslist->num_rows>0)
 {
     $userErr="Username already exists";
     $errors++;
 }
 elseif($userlist->affected_rows == -1) {

   echo 'An error occurred: ' . $conn->error;
 }

?> ` but you do realise, in the few milliseconds between the check and an actual insert, there might be an other user inserting that exact same name?

Added: how to do it when username is unique indexed:

<?php
 $userslist = $conn->prepare("INSERT INTO users (username, name, etc) VALUES (?,?, ?)");
 $userslist->bind_param("sss",$user, $name, $etc);
 $usersresult=$userslist->execute();

 if($conn->errno == 1062) {
     $userErr="Username already exists";
     $errors++;
 }
 elseif($conn->errno) {
   echo 'An error occurred: ' . $conn->error;
 }
 else {
    if($userlist->affected_rows == 1) {
       echo 'success';
    } 
    else {
        echo 'unknown why not inserted';
    }
 }


?>

edited Jun 30 '17 at 10:17

answered Jun 30 '17 at 08:33

Ivo P

1,722
1
7
18

As stated affected_rows acts like num_rows in a SELECT statement. this is not the problem. http://php.net/manual/en/mysqli.affected-rows.php – JParkinson1991 Jun 30 '17 at 08:34
Doesn't show the error still even after executing the mentioned code. – Jun 30 '17 at 08:43
run the error call on the $conn object as indicated in my answer – JParkinson1991 Jun 30 '17 at 08:44
$query="SELECT * FROM users WHERE username='".$user."'"; $qresult=mysqli_query($conn,$query); if($qresult->num_rows>0) { $userErr="Username already exists"; $errors++; } This is correctly pointing out that the username already exists but prepared statement does not shows the error – Jun 30 '17 at 10:20
error is something else then "i found this user already". Error is like "the table is corrupted, column does not exsist " etc – Ivo P Jun 30 '17 at 10:29
@IvoP When the username is unique indexed and if I use the above code, I get this error: Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Duplicate entry 'benedict' for key 'username' It doesn't even enter the first if condition – Jul 03 '17 at 14:55
put a try-catch around the execute() line – Ivo P Jul 03 '17 at 16:34

score 0 · Answer 4 · answered Jun 30 '17 at 08:34

0

You could do something like this.

if(mysqli_num_rows($userslist) > 0){

    echo "username already exists"
}

check if row exists with mysql - similiar question

answered Jun 30 '17 at 08:34

AutoTester213

2,714
2
24
48

mysqli_num_rows() expects parameter 1 to be mysqli_result, object given – Jun 30 '17 at 08:38

score 0 · Answer 5 · answered Jun 30 '17 at 08:36

Have you tried:

$conn = new mysqli("localhost","user","password","db");
$sql = "SELECT * FROM users where username='".$_POST['username']."'";
$result = $conn->query($sql);
$count = $result->num_rows;
if($count > 0)
{
echo "Username ".$_POST['username']." already exists!";
}

Why is this code to check duplicate usernames not executing properly?

5 Answers5