Check if username exists (PDO)

Question

I've searched for something similar to my question, they all used mysqli while I am using PDO. (I am not sure if it differs between them)

if(!empty($_POST['username']) && !empty($_POST['email']) && !empty($_POST['password'])):
    if (mysql_num_rows($check_username) != 0):
        echo "Username already exists";
    else:
        $stmt = $conn->prepare($registration_insert);
        $stmt->bindParam(':username', $_POST['username']);
        $stmt->bindParam(':email', $_POST['email']);
        $stmt->bindParam(':password', password_hash($_POST['password'], PASSWORD_BCRYPT));

        if( $stmt->execute() ):
            $message = 'Successfully created new user';
            else:
            $message = 'Sorry there must have been an issue creating your account';
        endif;
    endif;
endif;

$check_username has the value of

SELECT * FROM users WHERE username='".$username."'"

This code outputs:

Warning: mysql_num_rows() expects parameter 1 to be resource, string given in path:\to\my\wesbite\signup.php on line 15

line 15 =

if (mysql_num_rows($check_username) > != 0):

I saw it in another question so I used it and then asked about its alternative in PDO. Thats why I asked this question — Marwan K. Elzeer, Jul 29 '18 at 15:38
You can't mix them actually, and you don't really want to use any function that starts with `mysql_` they are all removed from PHP. Actually from the error message you are probably using an old version, if you are building a new application try using PHP 7.2 — Spoody, Jul 29 '18 at 15:47
Yes, I have latest version of XAMPP that uses InnoDB, but I just figured out that I was using PHP5, I will switch to PHP7 now. Thanks for your suggestion :D — Marwan K. Elzeer, Jul 29 '18 at 15:53
@Mehdi, I upgraded to PHP7.2 by following these instructions: https://medium.com/oceanize-geeks/how-to-upgrade-lower-version-to-latest-php-version-in-xampp-on-windows-f7be9a70bbb0 except step 3 because it was already done when I copied the PHP folder. I get this error: https://prnt.sc/kcfnd0 — Marwan K. Elzeer, Jul 29 '18 at 16:13
I honestly don't know, I'm not using XAMPP. But I think it's easy if you just uninstall it and install the one with PHP 7, just don't forget to backup your files and export your database. — Spoody, Jul 30 '18 at 06:35

score 0 · Answer 1 · answered Jul 29 '18 at 15:17

0

mysql_num_rows is for the mysql_ driver. The equivalent function in PDO is http://php.net/manual/en/pdostatement.rowcount.php but that won't work for selects.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Do a select count(*) and use the returned value to know if there are/aren't results. Also parameterize that query. Here's an adaption based off the manual's example #2:

$stmt = $conn->prepare('SELECT count(*) FROM users WHERE username= ?');
$stmt->execute(array($username));
if($stmt->fetchColumn() > 0) {
     echo 'username already exists';
}

answered Jul 29 '18 at 15:17

user3783243

5,368
5
22
41

It says: `Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in path:\to\my\website\signup.php on line 16` line 16: `$stmt->execute(array($username));` – Marwan K. Elzeer Jul 29 '18 at 15:34
Did you use the query I wrote, and the `prepare` function? – user3783243 Jul 29 '18 at 16:00
yes but I changed `username = ?` to `username = :username` Is that okay? – Marwan K. Elzeer Jul 29 '18 at 16:11
You'd need to bind the value first, as shown in my answer @MarwanK.Elzeer – Isaac Jul 29 '18 at 17:26
@MarwanK.Elzeer Not if you use the `execute` as written. You'll need to add the key to the array, that will map the binding correctly. If you use the anonymous binding (as it was written) it should work correctly. – user3783243 Jul 29 '18 at 20:48

Check if username exists (PDO)

1 Answers1