Fetch' in Pdo Gets Only One Result

fetch' in PDO gets only one result

Fetch should be used to display the next row from the database result.

To get all rows, you should use fetchAll();

• PDOStatement::fetch — Fetches the next row from a result set
• PDOStatement::fetchAll() — Returns an array containing all of the result set rows

Change your example to:

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $f = $sql->query('select * from user');
    $f->setFetchMode(PDO::FETCH_ASSOC);
    print_r($f->fetchAll());
?>

or if you want use PDOStatement::fetch to

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $sth = $sql->query('select * from user');
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
      print_r($row);
    }
?>

Why does PDO fetch() return only the first row?

To select all rows using PDO, you need to use fetchAll() instead of fetch().

$e = $db->query("SELECT `username`, `membership` FROM `users` WHERE `expire` != ''")->fetchAll();

In PDO, fetch() returns "the next row from a result set".

fetchAll() returns "an array containing all of the result set rows".

PHP PDO returning single row

Just fetch. only gets one row. So no foreach loop needed :D

$row  = $STH -> fetch();

example (ty northkildonan):

$id = 4;
$stmt = $dbh->prepare("SELECT name FROM mytable WHERE id=? LIMIT 1"); 
$stmt->execute([$id]); 
$row = $stmt->fetch();

PHP pdo fetch only returning 1 result

fetch indeed returns only one row from the result set. If you want them all, you'd have to use fetchAll or iterate over the result set yourself:

public function selectByCatagory($catagory,$column,$table,$id){
    $query =  $this->_pdo->prepare('SELECT ' .$column. ' FROM ' .$table. ' WHERE   catagory=:catagory');
    $query->bindParam(':catagory', $catagory);
    $query->execute();

    $retVal = array();
    while ($result = $query->fetch()) {
        array_push($retVal, $result[$id]);
    }
    return $retVal;
}

PDO fetch returns only first row

Add a while loop,

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

Or you can use fetchAll

$rows = $sth->fetchAll();
print_r($rows);

PHP PDO always give only one result

PDOStatement::fetch() loads a single row only. Use PDOStatement::fetchAll() to load all rows (or use a while loop):

$out = $result->fetchAll(PDO::FETCH_ASSOC);

MySQL pdo select single column with limit but return one row

fetch retrieves one row, or it returns FALSE if there is no row to fetch. (We could do the processing in a loop, and fetch each "next row", until fetch returns FALSE.

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

---

There's only one column in the resultset. It seems like we are confused about fetch and fetchAll.

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

http://php.net/manual/en/pdostatement.fetchall.php

---

If we use fetchAll to retrieve all of the rows, we get an array back, each element in the array is a row from the resultset.

  $query->execute();
  if( $rs = $query->fetchAll(PDO::FETCH_ASSOC) ) {
     foreach($rs as $row) {
        echo $row['title'];
     }
  }

PDO query returns result, but i cant fetch it

The problem is that you don't save the results of your first $statement->fetch(), so you skip the first row of your results. Given the way you have written your query, it looks like you only have one row, so you have nothing to loop over after that first row. Thus, the while never runs.

Remove the if and just do the while ($record = $statement->fetch()) loop:

while ($record =  $statement->fetch()) {
    var_dump($record);
    echo 'inside while';
}

If you need to verify that you have any results before doing the loop, you can use the procedure described in the documentation or simply use a counter to see if the loop ran, like this:

$rows = 0;
while ($record =  $statement->fetch()) {
    var_dump($record);
    echo 'inside while';
    $rows++;
}
if ($rows) {
    echo "Processed $rows rows\n";
} else {
    echo "Oops! No data!\n";
}

---

Related Topics