PDO query isn't working

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,

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

try {
            , email
            , name
            , password
            , level
            email = :email
            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

$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>';

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


num_rows is not nessesary at all

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


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


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


@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:

Thank guys for your help,

SpacePhoenix’s method work.

Thanks again for help saving my time.

@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

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