Having trouble with PDO queries (Notice: Undefined index)

Question

I'm trying to extract a data from database, I'm using PDO to do it.

I have the following PHP code without success:

public function CheckIdentity($email, $password) {
    $usersArray = [];

    $SQL = " SELECT * FROM usuario WHERE email='".$email."' AND password='".$password."' ";
    $resultBySQL = $this->dbConnected->query($SQL);

    while ($row = $resultBySQL->fetchAll(PDO::FETCH_ASSOC)) {
        //$row = $resultBySQL->fetchAll(PDO::FETCH_ASSOC);
        $user = new User();
        $user->setUsername($row['username']);
        $user->setEmail($row['email']);
        $user->setPassword($row['password']);
        $user->setRole($row['role']);
        $user->setPersonalInfo($row['personal_info']);
        print_r($user);

        array_push($usersArray, $user);
    }
}

When I print it, I'm getting the following (for each attribute):

Notice: Undefined index: email, etc, etc...

Also, when I only apply $row = $resultBySQL->fetchAll(PDO::FETCH_ASSOC);

I'm always getting this:

User Object ( [username:User:private] => [email:User:private] => [password:User:private] => [role:User:private] => [personalInfo:User:private] => )

By the way, I've tried to apply a code where I had good result:

foreach ($resultBySQL as $row) { /* Extract the info */ }

But now, with this example... It doesn't work.

On the other hand, I've checked the query in the database and the PDO connection, they're working without problem.

What is wrong? Do you have any idea?

Answer 1

When you do:

$row = $resultBySQL->fetchAll(PDO::FETCH_ASSOC);

you are fetching all the results in one time, and you get an array of array. Since yor're making this call in your while condition, you're creating the whole result's array in the first iteration, then the code exits in the second iteration, as there is nothing more to fetch, because all the results have already been fetched in the first iteration.

You should use:

//notice the 'fetch' in place of 'fetchAll'
while ( $row = $resultBySQL->fetch(PDO::FETCH_ASSOC); ) 
{
    $user = new User();
    $user->setUsername($row['username']);

    //...
}

to fetch only one row for every iteration, then you can access its content inside the while loop

Alternatively, you could fetch all the rows once, an then loop through the results:

$rows = $resultBySQL->fetchAll(PDO::FETCH_ASSOC);

foreach( $rows as $row )
{
    $user = new User();
    $user->setUsername($row['username']);

    //...
} 

Answer 2

Try using fetch instead of fetchAll in situations like this.