PHP query always returns that a user exists but running the same query directly to the database produces no results?

Question

The behavior I expect is that a query is first ran on the database to determine if a username or email already exist on there. If they do, skip if((count((array)$result) == 0)) because the result will either be 1 or 2 if a username or email is already present or 0 if neither are.

However, the if statement is being skipped even if both values are unique and else { echo ('Username or Email already exists!');} is being executed.

Similarly phrased questions I have checked but did not find a solution here and here

if(isset($_POST['submit']))
{
 $username = !empty($_POST['username']) ? trim($_POST['username'])  : null;
 $email   = !empty($_POST['email'])     ? trim($_POST['email'])     : null;

//other variables remove for brevity, but they are all formed the exact same

$query = $pdo->prepare("SELECT * FROM users WHERE username= ? OR email= ? LIMIT 2");
$query->bindParam(1, $username, PDO::PARAM_STR);
$query->bindParam(2, $email, PDO::PARAM_STR);
$query->execute();
$result = $query->fetch(PDO::FETCH_ASSOC);

if ((count((array)$result) == 0))
{
    
        $query = $pdo->prepare ("INSERT INTO users (firstname, lastname, username, password, email, address, city, province, postalcode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        
        $query->bindValue(1, $fname,    PDO::PARAM_STR);
        $query->bindValue(2, $lname,    PDO::PARAM_STR);
        $query->bindValue(3, $username, PDO::PARAM_STR);
        $query->bindValue(4, $password, PDO::PARAM_STR);
        $query->bindValue(5, $email,    PDO::PARAM_STR);
        $query->bindValue(6, $address,  PDO::PARAM_STR);
        $query->bindValue(7, $city,     PDO::PARAM_STR);
        $query->bindvalue(8, $province, PDO::PARAM_STR);
        $query->bindValue(9, $postalcode, PDO::PARAM_STR);
        $query->execute();
        
                echo 'Registration Successful!';
}
    
            
    else { echo ('Username or Email already exists!');}
}
//closing the database after execution
    $query = null;
    $pdo = null;

For example SELECT * FROM users WHERE (username='kanas' OR email='postmaster@outlook.com'); returns zero rows as expected in PHPMyAdmin. But when ran through my html whats echoed back is "Username or Email already exists!"

The create table:

CREATE TABLE `users` (
  `userID` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `lastlogin` date NOT NULL,
  `permission` int(3) NOT NULL,
  `email` varchar(320) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `province` varchar(50) NOT NULL,
  `postalcode` varchar(6) NOT NULL,
  `userrequests` text NOT NULL
);

show us the create table, besides why do you don't the real erre message — nbk, Apr 04 '21 at 22:29
The SELECT. Is redundant, and even counter-productive. What if user B inserts while user A is 'checking'? — Strawberry, Apr 04 '21 at 22:38
I have updated the original question with the create table, I'm not sure what you mean? Are you asking why I'm not outputting the real error message? Because I don't know what possible error message would be given, syntax wise it doesn't give any errors and I have an error catch at the start `catch(PDOEXception $e) { $strerr = $e->getMessage(); $format = 'Failed to connect to Database: %s'; echo sprintf($format, $strerr);}` — unusualsuspect, Apr 04 '21 at 22:38
@Strawberry I don't know how to account for such a case, but this will never be a production page, only 1 person at a time will be using the form it's simply a class project. How else would I check if a user already exists? Then contingent on the results, create a user? — unusualsuspect, Apr 04 '21 at 22:40

Dharman · Accepted Answer · 2021-04-04T23:17:49.847

1

If no rows were found then fetch() will return false. When you do count((array)false) the result will always be 1.

To fix the problem simply check if fetch() returned anything.

if ($result) {
    // ...
}

However, be warned that what you currently have will not prevent duplicate records. To prevent duplicate records you would have to add UNIQUE constraint in the database. See How to prevent duplicate usernames when people register?

edited Apr 04 '21 at 23:17

answered Apr 04 '21 at 23:05

Dharman

30,962
25
85
135

Thank you, I have added the UNIQUE Constraint and will work an implementing the suggested error handling too. – unusualsuspect Apr 04 '21 at 23:38

score 0 · Answer 2 · edited Apr 04 '21 at 23:33

0

You can try this

$query->execute(); 
$data = $query->fetchAll(); 
if (!$data) 
{
    // again, no rowCount() is needed! echo 'No data found'; 
}

edited Apr 04 '21 at 23:33

Dharman

30,962
25
85
135

answered Apr 04 '21 at 22:36

ahmed abdelaty

79
2

Thank you, but I don't understand the answer, I'm doing `$query->fetch(PDO::FETCH_ASSOC);` followed by `if ((count((array)$result) == 0))` the contents are still counted either way? The provide rowCount did not fix the problem I am having unfortunately, and reading the documentation, it only returns values affected by the "last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object" which could be anything in this case as the usernames and emails could have be inserted at anytime and the last PDO statement would have been select. – unusualsuspect Apr 04 '21 at 22:55
$query->execute(); $data = $query->fetchAll(); if (!$data) { // again, no rowCount() is needed! echo 'No data found'; } // Try this please – ahmed abdelaty Apr 04 '21 at 23:03
Yes, I tried both the solution in your comment and the one by Dharman, both solutions now work. – unusualsuspect Apr 04 '21 at 23:28

PHP query always returns that a user exists but running the same query directly to the database produces no results?

2 Answers2