PDO query isn't working

PHP
1

Hi guys,

I’m moving to PDO from mysqli and I’ve tried my first query. It isn’t worked. What’s wrong with my code?

$query = $conn->prepare("SELECT id, email, name, password, level FROM user WHERE email = :email and password = :password LIMIT 1"); $query->execute(array(':email' => $email, ':password' => $password)); $num_rows = $query->fetchColumn(); if($num_rows == 1) { while($row = $query->fetch(PDO::FETCH_ASSOC)) { echo $row['name']; } } else { header("location: ../index.php"); }

Thank you,

2

When working with PDO you need to get used to working with exceptions (“try-catch” blocks)

try {
    $sql="
        SELECT
              id
            , email
            , name
            , password
            , level
        FROM
            user
        WHERE
            email = :email
        AND
            password = :password
    ";
    $stmt = $conn->prepare($sql);
            
      $stmt->execute(array(':email' => $email, ':password' => $password));
      $users = array();
      $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
      if (count($users <> 0 )) {
          foreach ( $users AS $user ) {
              // Display the users or do something here
          }
      } else {
          header("location: ../index.php");
      }
} catch (PDOException $e) {
    echo '
        <p>Error with the query!<br /><br />
        
        The error is:<br /><br />', $e->getMessage(), '\n
    ';
}

For a live site, instead of displaying the error, you’ll want to log it in an error log file

3 
$query = $conn->prepare("SELECT id, email, name, password, level FROM user WHERE email = :email and password = :password LIMIT 1");
$query->execute(array(':email' => $email, ':password' => $password));
$result = $sth->fetch(PDO::FETCH_ASSOC);
echo '<pre>';
print_r($result);

Personally I think you would be better fetching the next row as an array, for you only getting one result anyways.

4

@ketting00

num_rows is not nessesary at all

$row = $query->fetch(PDO::FETC_ASSOC); //$row is now either a dataset or false

http://de2.php.net/manual/en/pdostatement.fetch.php
so

$statement = $conn->prepare("your query");
$statement->execute($dataArray);
$row = $statement->fetch(PDO::FETCH_ASSOC); //also you dont need a LIMIT  1 at this place because fetch fetches only one row
if(!$row){ 
    header("location: ../index.php");
}
echo $row['name'];

@SpacePhoenix

iam not really sure, but i think you have to mention that you have to setup PDO error mode else PDO will not throw exceptions at all

$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
5

@ketting00 I suggest you take a look in the manual to see what PDO::fetchColumn does. It doesn’t do what you think it does :wink:

6

Thank guys for your help,

SpacePhoenix’s method work.

Thanks again for help saving my time.

7

@ketting00 but this does not make any sense at all, you have LIMIT 1 in your query and SpacePhoenix is using fetchAll method Pepster solution is closer to your problem

8

the difference is that @SpacePhoenix wants to count the number of results, so he has to use fetchAll() even if there is only a single result. but counting result rows ain’t necessary at all as @BlackScorp demonstrates.

@BlackScorp yes, Exceptions must be explicitly enabled.
@BlackScorp the LIMIT 1 is not necessary, since emailand password must be a unique (composite) key