Check row exists with PDO

Question

I cannot figure this out, I've been reading numerous Stack overflow threads and I keep getting an issue,

right now if the user types anything, regardless if it is the same username or email, it would come up with an error. Please help!

I want the error message to appear when the user types an username or email that is already on the database.

$sql = "SELECT count(*) FROM users WHERE user_email = :email OR user_name = :username";
        $query1 = $DBH->prepare($sql);
        $query1->execute(array(':username'  => $username,':email' => $email));

        if ($query1->rowCount() > 0){
            // User Exists
            $error = "Username or E-Mail in Use";

        }   else{
            // User Does Not Exist
            //No errors - let’s create the account
            //Encrypt the password with a salt
            $encryptedPass = password_hash($_POST['password'], PASSWORD_DEFAULT);
            //Insert DB
            $query = "INSERT INTO users (user_email, user_password, user_forename, user_lastname, user_name, user_gender, user_country, user_number) VALUES (:email, :password, :firstname, :lastname, :username, :gender, :country, :mobile)";
            $result = $DBH->prepare($query);
            $result->bindParam(':username', $_POST['username']);
            $result->bindParam(':firstname', $_POST['firstname']);
            $result->bindParam(':lastname', $_POST['lastname']);
            $result->bindParam(':email', $_POST['email']);
            $result->bindParam(':gender', $_POST['gender']);
            $result->bindParam(':country', $_POST['country']);
            $result->bindParam(':mobile', $_POST['mobile']);
            $result->bindParam(':password', $encryptedPass);

            if($result->execute()){
                echo '<div class="alert alert-success" role="alert">Registration Successful!</div>';
                echo "<script> window.location.assign('index.php?p=login'); </script>";
            }
        }

EDIT

        $stmt = $DBH->prepare("SELECT COUNT(*) FROM users WHERE user_email = :email OR user_name = :username");
        $stmt->execute(array(':username'  => $username,':email' => $email));
        $count = $stmt->fetchColumn();

        var_dump($count);

        if ($count > 0) {
            $error1 = "Username or E-Mail in Use. Please try another.";
        }

        if(!$error){

                //No errors - let’s create the account
                //Encrypt the password with a salt
                $encryptedPass = password_hash($_POST['password'], PASSWORD_DEFAULT);
                //Insert DB
                $query = "INSERT INTO users (user_email, user_password, user_forename, user_lastname, user_name, user_gender, user_country, user_number) VALUES (:email, :password, :firstname, :lastname, :username, :gender, :country, :mobile)";
                $result = $DBH->prepare($query);
                $result->bindParam(':username', $_POST['username']);
                $result->bindParam(':firstname', $_POST['firstname']);
                $result->bindParam(':lastname', $_POST['lastname']);
                $result->bindParam(':email', $_POST['email']);
                $result->bindParam(':gender', $_POST['gender']);
                $result->bindParam(':country', $_POST['country']);
                $result->bindParam(':mobile', $_POST['mobile']);
                $result->bindParam(':password', $encryptedPass);

                if($result->execute()){
                    echo '<div class="alert alert-success" role="alert">Registration Successful!</div>';
                    // echo "<script> window.location.assign('index.php?p=login'); </script>";
                }
            }

var dump = string(1) "0"

You are counting the rows so it will return `1 row` with a `value of 0`. You are then asking how many rows, which is 1. What you can do is `SELECT 1 FROM ...` which will return a row if a record is found and will return an empty dataset if no matching records are found. — waterloomatt, Apr 11 '18 at 19:20
$sql = "SELECT 1 FROM users WHERE user_email = :email OR user_name = :username"; ? I tried that it did not work, sorry if I did it wrong I'm fairly new to PHP — Hasnaat Khan, Apr 11 '18 at 19:22
Why not use `SELECT id FROM ...` this will be faster than a count on all columns. Also adding `LIMIT 1` might help — SuperDJ, Apr 11 '18 at 19:24

score 0 · Answer 1 · answered Apr 11 '18 at 19:37

0

A few ways to go about this. As @chris85 mentioned you need to actually fetch the result instead of using rowCount().

Edit: Removed the first example.

<?php
$stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE user_email = :email OR user_name = :username");
$stmt->execute(array(':username'  => $username,':email' => $email));
$count = $stmt->fetchColumn();

if ($count > 0) {
    $error = "Username or E-Mail in Use";
} else {
    ...

answered Apr 11 '18 at 19:37

waterloomatt

3,662
1
19
25

Fatal error: Call to a member function prepare() on null I receive this error on the prepare, I don't know what I did wrong – Hasnaat Khan Apr 11 '18 at 19:45
@HasnaatKhan `$DBH` is your connection, not `$pdo`. Did you change that? – chris85 Apr 11 '18 at 19:47
@waterloomatt, ah thank you, but I still receive the same issue from the beginning, it just submits even though there is a duplicate :( – Hasnaat Khan Apr 11 '18 at 19:52
@HasnaatKhan Please update the question with your new code. Also do a `var_dump($count);` and add the output of that. – chris85 Apr 11 '18 at 19:54
Hey, I updated it, thanks for your help – Hasnaat Khan Apr 11 '18 at 19:58
@HasnaatKhan Use the `@` to direct comments. Notifications aren't sent otherwise. From that it sounds like there are no rows being returned. Are you sure your query returns rows, and/or that your PDO and when you manual execute are using the same DB and server? – chris85 Apr 11 '18 at 20:34
@chris85 They are using the same server, and I'm not entirely sure it is returning rows I am always getting string(1) "0" – Hasnaat Khan Apr 11 '18 at 20:37
@HasnaatKhan Run the query on the database directly. – chris85 Apr 11 '18 at 20:40
@chris85 ok I'm at the run queries in the database I just don't know the right SQL to use from the PHP, – Hasnaat Khan Apr 11 '18 at 20:42
@HasnaatKhan It's what you have in the PHP except swap the placeholders `:....` with the quoted string. For example `SELECT count(*) FROM users WHERE user_email = 'some@domain.com' OR user_name = 'chris85'` – chris85 Apr 11 '18 at 20:57
@chris85, thanks for your help again by the way, the sql says count(*) 4 if I type in a duplicate – Hasnaat Khan Apr 11 '18 at 21:06
@HasnaatKhan Okay, then that means neither the `username`, nor `email` are in your database. Perhaps your insert is failing?... You updated the comment to `4`. Is it `0` rows or `4`? You need to be consistent with your testing.. – chris85 Apr 11 '18 at 21:07
@chris85 Yeah, sorry I meant to type in 4. I uh used chris85 and it came up with 0, my bad. – Hasnaat Khan Apr 11 '18 at 21:08
@chris85, sorry I meant to type 4 – Hasnaat Khan Apr 11 '18 at 21:09
@HasnaatKhan Okay, put the query you ran directly into your PHP `prepare` and run it, comment out the binding. This is just to test the behavior is the same. Don't use it this way. – chris85 Apr 11 '18 at 21:15
@chris85 um, `$stmt = $DBH->prepare(SELECT count(*) FROM users WHERE user_email = 'some@domain.com' OR user_name = 'x');` like this? – Hasnaat Khan Apr 11 '18 at 21:17
@HasnaatKhan and then `$stmt->execute(/* array(':username' => $username,':email' => $email) */);` – chris85 Apr 11 '18 at 21:18
@chris85 ok, so it's a constant 4 now in the var dump count – Hasnaat Khan Apr 11 '18 at 21:20
@HasnaatKhan Okay, so you know the PDO connection is correct. Now check the binding by statically defining the same values first. Then use the variables you had. I'll assume you have it from here. – chris85 Apr 11 '18 at 21:23

Check row exists with PDO

1 Answers1