Check if username already exists (PHP, PDO)

Question

I have a little problem, that's the 4th tutorial about username checking but they're all in depreciated MySQL and I would like to do it with PDO

here is my code:

$stmt = $bdd->prepare("SELECT * from users WHERE username=?");
$stmt->execute(array($username));
if ( $stmt->rowCount() > 0 ) {
$message='Username already taken';
} else { $message='User added'; }

As I understood, the "rowCount" is the equivalent of mysql_num_rows so I don't think there's a problem with that, the problem must be with the SQL Query, it shows me "Username already taken" even if the username is not really taken.

Where am I wrong please ?

Thanks

`rowCount()` only returns a valid count for INSERT, UPDATE, or DELETE queries. Instead, retrieve the result set with `$stmt->fetchAll()` and check the length of the resulting array. — , Jul 15 '15 at 10:29
check my answer, you are using wrong tool (function) to accomplish that! — Stefano Mtangoo, Jul 15 '15 at 10:43

Rox · Answer 1 · 2015-07-15T10:35:03.980

0

$stmt = $bdd->prepare("SELECT * from users WHERE username=?");
$stmt->execute(array($username));
$result = $stmt->fetchAll();
if (!empty($result) ) {
$message='Username already taken';
} else { $message='User added'; }

There are examples for a SELECT query here

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

so you should use

 PDOStatement::fetchAll

which return an array of all results matching your query

edited Jul 15 '15 at 10:35

answered Jul 15 '15 at 10:22

Rox

811
6
20

You should explain why this works when the OPs code did not. – Jul 15 '15 at 10:30

Stefano Mtangoo · Answer 2 · 2022-02-14T20:13:23.190

From PHP Manual (which you should read before using a function or ask at SO):

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Since your statement is SELECT, then its natural that you have unpredictable results

The same manual down the road have a best advice for your problem

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

Completely untested example that should give you idea of what the manual means

$stmt = $bdd->prepare("SELECT count(*) from users WHERE username=?");
$stmt->execute(array($username));
$norows = $result->fetchColumn(); 
if ( $norows  > 0 ) {
$message='Username already taken';
} else { $message='User added'; }

score -1 · Answer 3 · answered Jul 15 '15 at 10:39

$username = $_POST['username'];

$query = $pdo->prepare('SELECT * FROM users WHERE username = :username');
$query->bindParam(':username', $username);
$query->execute();

if($query->rowCount() == 0){
   // do your insert
   return "Registered";
} else { 
  return "Username Taken";
}

Otherwise, check your MySQL table and see if the username is already there.

Check if username already exists (PHP, PDO)

3 Answers3