php PDO fetchAll() - while not working, foreach works

Question

I would like to know if i'm doing fine OR fetchAll() doesn't work with WHILE.

here is an exemple

$db=new PDO("mysql:host=" .$dbhost. "; dbname=" . $dbname, $dbuser, $dbpass);

$page=$db->prepare("SELECT * FROM page");
$page->execute();

foreach ($page->fetchAll(PDO::FETCH_ASSOC) as $row) {

//echo a row
//is working
}

however, i if try looping with a while

while ($row=$page->fetchAll(PDO::FETCH_ASSOC)){

//echo a row
//Show empty
}

i tryed to use only fetch(), it was working, my question: why fetchAll() doesn't work with "WHILE" ?

you probably should be using fetch instead of fetchAll in the while example. in the for you should be fetching all before the loop condition. fetchAll returns all of your matching rows as an array or row arrays. — Orangepill, Jul 18 '13 at 16:40
Yeah, fetch() is working as i said but i have a big DB, i think the best way is fetchAll() — Mafitsi, Jul 18 '13 at 16:45

score 21 · Answer 1 · edited Apr 08 '16 at 18:41

21

Fetch all returns all of the records remaining in the result set. With this in mind your foreach is able to iterate over the result set as expected.

For the equivalent while implementation should use $page->fetch(PDO::FETCH_ASSOC);

while ($row = $page->fetch(PDO::FETCH_ASSOC)){
   // do something awesome with row
}

if you want to use a while and fetch all you can do

$rows = $page->fetchAll(PDO::FETCH_ASSOC);

// use array_shift to free up the memory associated with the record as we deal with it
while($row = array_shift($rows)){
   // do something awesome with row
}

A word of warning though: fetch all will do exactly that, if the result size is large it will stress the resources on your machine. I would only do this if I know that the result set will be small, or I'm forcing that by applying a limit to the query.

edited Apr 08 '16 at 18:41

Mark

2,961
1
16
24

answered Jul 18 '13 at 16:46

Orangepill

24,500
3
42
63

For the people who are already used to coding while loop or their codes are already using while loop and they want to upgrade their MySQL Class into PDO, I would suggest this solution because you don't need to change your style of coding. – AkiEru Mar 13 '15 at 07:18
Yes `array_shift()` is fine to use `while()` and `fetchAll()` together. But really why? What's wrong with `foreach()` and `fetchAll()`? What's the benefit of `while()` than `foreach()` in this case? – stack Jan 10 '16 at 19:14
@stack the only real benefit is that with the array_shift you can potentially free resources back to the server sooner – Orangepill Jan 11 '16 at 03:53
I always like to know this kind of small and clever points. Really thanks. +1 *(btw I'm who was worried about one `if-statement` in the PHP room `;-)`, )* – stack Jan 11 '16 at 14:56

score 8 · Answer 2 · answered Jul 18 '13 at 16:48

From the PHP Manual:

The meaning of a while statement is simple. It tells PHP to execute the nested statement(s) repeatedly, as long as the while expression evaluates to TRUE.

Since the method you're using returns an array, while ($row=$page->fetchAll(PDO::FETCH_ASSOC)) is going to set $row to an array of the entire result set. What you're expecting is for the fetchAll method to extend the Iterator class, which it does not.

A foreach is the right way to go here.

score 6 · Answer 3 · answered Jul 18 '13 at 16:52

6

no need to loop through the recordset, because fetchAll - well - fetches all the records in one command. Nice, isn't it?

$rows = $page->fetchAll(PDO::FETCH_ASSOC);

// $rows is an array containing all records...
foreach ($rows as $row)
    echo $row->fieldname;

answered Jul 18 '13 at 16:52

michi

6,565
4
33
56

Thanks @michi one problem though, its working on my local server but when i upload it to hosting it doesn't work :( – yaqoob Dec 16 '17 at 04:23
wrote this more than 4 years ago ;-) Different settings on your local machine vs your server, use google to find out how to resolve this methodically – michi Dec 16 '17 at 22:57
Found the issue, I capitalized table name in SQL renaming in small letter works :p – yaqoob Dec 19 '17 at 08:51
Awesome! Simple and effective solutions :) – Jodyshop Feb 26 '20 at 23:39

score 2 · Answer 4 · answered Jul 18 '13 at 16:58

I tried to reproduce your case. Look here:

script.php

<?php
$host = 'localhost';
$user = "user";
$password = '';
$db_name = 'test';
$port = 3306;

try
{
    $connection = new PDO("mysql:host=$host;port=$port;dbname=$db_name", $user, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
    echo 'Connection failed: ' . $e->getMessage();
}

$page=$connection->prepare("SELECT * FROM Document");
$page->execute();

while ($row = $page->fetchAll(PDO::FETCH_ASSOC)) {
    var_dump($row);
}

Database test

DROP TABLE IF EXISTS `Document`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Document` (
  `DataID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Description` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`DataID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Document`
--

LOCK TABLES `Document` WRITE;
/*!40000 ALTER TABLE `Document` DISABLE KEYS */;
INSERT INTO `Document` VALUES (1,'!!!'),(2,'This is document 2'),(3,'This is document 3'),(4,'This is document 4'),(5,'Hello');
/*!40000 ALTER TABLE `Document` ENABLE KEYS */;
UNLOCK TABLES;

output

$php script.php
array(5) {
  [0]=>
  array(2) {
    ["DataID"]=>
    string(1) "1"
    ["Description"]=>
    string(3) "!!!"
  }
  [1]=>
  array(2) {
    ["DataID"]=>
    string(1) "2"
    ["Description"]=>
    string(18) "This is document 2"
  }
  [2]=>
  array(2) {
    ["DataID"]=>
    string(1) "3"
    ["Description"]=>
    string(18) "This is document 3"
  }
  [3]=>
  array(2) {
    ["DataID"]=>
    string(1) "4"
    ["Description"]=>
    string(18) "This is document 4"
  }
  [4]=>
  array(2) {
    ["DataID"]=>
    string(1) "5"
    ["Description"]=>
    string(5) "Hello"
  }
}

The output means, that while statement was executed once and prints all the rows, that the query should return, which is absolutely correct, because fetchAll returns an array of arrays with all the rows. PHP interprets it as true and while runs once.

While foreach will iterate over the array of arrays and you will have the corresponding row every time.

score 0 · Answer 5 · answered Jul 18 '13 at 16:46

0

With fetchAll In while loop you have fetched all records in the first iteration and there is nothing to fetch the next time. In foreach also you have fetched all records in the first line, but foreach uses the result for iteration.

answered Jul 18 '13 at 16:46

bansi

55,591
6
41
52

so the only solution in this case is using foreach() ? – Mafitsi Jul 18 '13 at 16:49
@Maftisi I posted a solution that uses while on a result set acquired via fetchAll. – Orangepill Jul 18 '13 at 16:51
@Mafitsi you can use `fetchAll` with `foreach` or result of `fetchAll` with `while` loop as in Orangepill's solution. While needs to evaluate the condition on every iteration. – bansi Jul 18 '13 at 17:30

php PDO fetchAll() - while not working, foreach works

5 Answers5

script.php

Database test

output

Linked