php - Fatal error: Call to a member function fetchALL() on a non-object - using PDO on a Microsoft Access Database

Question

Good Afternoon!

I have made a connection class to a Microsoft Access Database (which works). However my problem lies where I'm trying to use this class to execute a simple SQL statement and I receive the error message: Fatal error: Call to a member function fetchALL() on a non-object.

I'm fairly new to PDO and have read a lot of articles online but to no avail. I think I understand my problem but not fully, please could someone shed some light on the situation and possibly provide an answer to why i'm getting the error message?

connectionClass.php

class connection{

      public $con;
      private $dbName;

      function __construct(){
          $this->dbName = $_SERVER["DOCUMENT_ROOT"] . "databaseyakety1new.mdb";
       }

      function connect(){
          $this->con = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$this->dbName; Uid=Admin; Pwd=;");
          $this->con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
          return $this->con;
         }   
      }

      if (!ini_get('display_errors')) {
      ini_set('display_errors', '1');
      }

testIndex.php

try{
   include_once 'classesconnectionClass.php';


   $con = new connection();
   $pdoConnection = $con->connect();


   $sql = $pdoConnection->prepare("SELECT * FROM celebs");
   $result = $pdoConnection->exec($sql);
     while ($row = $result->fetchALL(PDO::FETCH_ASSOC)) {
       echo $row['firstname'];
       echo $row['surname'];
      }
   } catch (Exception $e){
       echo 'ERROR:'.$e->getMessage();
       file_put_contents('connection.errors.txt', $e->getMessage().PHP_EOL,FILE_APPEND);
       }

       if (!ini_get('display_errors')) {
       ini_set('display_errors', '1');
      }

The error message is related to this line:

while ($row = $result->fetchALL(PDO::FETCH_ASSOC)) {

Any help is greatly appreciated, thanks!

Answer 1

$result is just a boolean that indicates whether the query was successful or not. The fetchAll method is on PDOStatement, so it should be:

while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {

You're also executing the statement wrong, it should be:

$result = $sql->execute();

The method you used is for executing a SQL string without first preparing it. You could instead do:

$result = $pdoConnection->exec("SELECT * FROM celebs");
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {