PHP : PDO fetch() not showing result

Question

I'm now learning PDO I just followed the tutorial on Youtube. So here I have here a method from Db class and I added a new line PDO::FETCH_ASSOC because I want to try a while loop besides of foreach.

public function query($sql, $params = array()){
$this->_error = false;
$this->_count = 0;
if($this->_query = $this->_pdo->prepare($sql)){

  #check if theres a parameter and bindvalue to sql statement
  if(count($params)){
    $x = 1;
    foreach($params as $param){
      $this->_query->bindValue($x, $param);
      $x++;
    }
  }

  #execute with or without parameter
  if($this->_query->execute()){
    $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
    $this->_whileFetch = $this->_query->fetch(PDO::FETCH_ASSOC);
    $this->_count = $this->_query->rowCount();
  }else{
    $this->_error = true;
  }
  return $this;
}
} #end of GET function


public function whileFetch(){
   return $this->_whileFetch;
}

The foreach is working well, but I want to try a while loop but it seems not working, not showing any data.

$query = $db->query("SELECT * from master_data.store");
while($row = $query->whileFetch()){
     echo $row['name'];
}

I also try this, but i got error.

while($row = $query->fetch(PDO::FETCH_ASSOC)){
    echo $row['name'];
}

score 1 · Answer 1 · answered Feb 05 '17 at 11:45

This is an awful tutorial and your class has some critical drawbacks as a result. Your function should be looking like this

public function query($sql, $params = array()){
    $stmt = $this->_pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

So you'll be able to use whatever method supported by PDO:

$query = $db->query("SELECT * from master_data.store");
foreach($query as $row){
    echo $row['name'];
}

$query = $db->query("SELECT * from master_data.store");
while($row = $query->fetch()){
    echo $row['name'];
}

$list = $db->query("SELECT name from store")->fetchAll(PDO::FETCH_COLUMN);
foreach($list as $name){
    echo $name;
}

and remember that unlike yours, this function could be used with ANY query returns ANY type of result with better error reporting and many other benefits.

I wrote an article that explains all the drawbacks of your Db class, Your first database wrapper's childhood diseases which is definitely worth to read. You are welcome to ask any questions.

score 1 · Accepted Answer · answered Feb 05 '17 at 11:51

The problem is because of this method call ->fetchAll(PDO::FETCH_OBJ). Since you're fetching all the rows from the result set as an array and assigning it to the instance variable $results, this statement $this->_whileFetch = $this->_query->fetch(PDO::FETCH_ASSOC); won't work afterwards. And that's why you'll have $this->_whileFetch as false.

So the solution is,

Remove the instance variable $_whileFetch from your class, you won't be needing this.

Remove the following two lines from this if($this->_query->execute()){ ... } block,

$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
$this->_whileFetch = $this->_query->fetch(PDO::FETCH_ASSOC);

In the whileFetch() method, instead of $this->_whileFetch, directly fetch the next row from the result set and return it,
```
return $this->_query->fetch(PDO::FETCH_ASSOC);
```

So your query() and whileFetch() method would be like this:

public function query($sql, $params = array()){
    $this->_error = false;
    $this->_count = 0;
    if($this->_query = $this->_pdo->prepare($sql)){
        #check if theres a parameter and bindvalue to sql statement
        if(count($params)){
            $x = 1;
            foreach($params as $param){
                $this->_query->bindValue($x, $param);
                $x++;
            }
        }

        #execute with or without parameter
        if($this->_query->execute()){
            $this->_count = $this->_query->rowCount();
        }else{
            $this->_error = true;
        }
        return $this;
    }
}

public function whileFetch(){
    return $this->_query->fetch(PDO::FETCH_ASSOC);
}

Later, you can have your query and while() loop like this:

$query = $db->query("SELECT * from master_data.store");
while($row = $query->whileFetch()){
     echo $row['name'];
}

Thanks, I did but I can only call once each of the while loop or foreach and the next loop is not working anymore — Raffy T Lawrence, Feb 05 '17 at 12:14
@RaffyTLawrence Yes, That's because once you're done iterating the result set using `while` loop, the result set will be exhausted. You cannot adjust the result pointer to point to the beginning of the result set again. So use either `while` or `foreach` loop to loop through the result set. — Rajdeep Paul, Feb 05 '17 at 12:24
@RaffyTLawrence Having said this, in case you want to loop through the result set *again*, you need to set PDO cursor to `CURSOR_SCROLL`. By default it's set to `CURSOR_FWDONLY`. Take a look at this SO thread, and the *accepted* answer: [http://stackoverflow.com/q/15637291/5517143](http://stackoverflow.com/q/15637291/5517143) — Rajdeep Paul, Feb 05 '17 at 12:38

PHP : PDO fetch() not showing result

2 Answers2