Foreach vs While loop in PDO Database retrieval

[marcbraulio]

Hello everyone,

 

Let's say I am connecting to a database using PDO (I am excluding prepared statements for the sake of simplicity):

try 
{
$conn = new PDO('mysql:host=localhost;dbname=cms', 'root', 'password');
$result = $conn->query('SELECT * FROM news');
} 
catch (PDOException $e) 
{
echo 'Error: '.$e->getMessage();
exit;
}

 

So far I been using the following to manipulate the data in object form:

while ($row = $result->fetchObject()) {
echo $row->title;
echo $row->text;
}

My question is, is there a better way?

 

What is the foreach code equivalent to that? I have had no luck keeping the data in object form using foreach statements.

 

Which method is faster, while or foreach?

 

Thank you, any suggestions are highly appreciated.

[kicken]

Not sure it really makes a difference speed wise.  However if you read it in a row at a time in a while loop you don't have to load the entire result set into memory, it can be streamed from the server.  To do a foreach you have to read it all into an array then loop that.  You could use fetchAll() to get the array, eg:

foreach ($result->fetchAll() as $row){
}

 

 

I prefer to stick with a simple while loop myself.

 

[bspace]

there's a benchmark page at http://www.phpbench.com/

 

which suggests that foreach is quicker

(scroll down to find)

although whether it's actually noticable in practice would probably come down to how complicated the code in the loop was

in this case i'd go for ease of reading the code over marginal speed gain

 

there's a number of other rather interesting comparisons here too

[marcbraulio]

Not sure it really makes a difference speed wise.  However if you read it in a row at a time in a while loop you don't have to load the entire result set into memory, it can be streamed from the server.  To do a foreach you have to read it all into an array then loop that.  You could use fetchAll() to get the array, eg:

foreach ($result->fetchAll() as $row){
}

 

 

I prefer to stick with a simple while loop myself.

 

 

Thank you for the insight, but see, using it like that would just turn the information into a array and the following would NOT work:

foreach ($result->fetchAll() as $row){
        echo $row->title;
echo $row->text;
}

 

there's a benchmark page at http://www.phpbench.com/

 

which suggests that foreach is quicker

(scroll down to find)

although whether it's actually noticable in practice would probably come down to how complicated the code in the loop was

in this case i'd go for ease of reading the code over marginal speed gain

 

there's a number of other rather interesting comparisons here too

 

Thank you for the source. Do you know of a way to use foreach and still keep the data in object form?

[kicken]

Do you know of a way to use foreach and still keep the data in object form?

 

Is there any particular reason you need it in object form vs array?  Or just preference?

 

In any event:

PDO::fetchAll()

PDOStatement::fetchAll ([ int $fetch_style [, mixed $fetch_argument [, array $ctor_args = array() ]]] )

fetch_style

Controls the contents of the returned array as documented in PDOStatement::fetch().

 

PDO::fetch()

fetch_style

Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants, defaulting to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH).

[*]PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

[*]PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

[*]PDO::FETCH_BOUND: returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound with the PDOStatement::bindColumn() method

[*]PDO::FETCH_CLASS: returns a new instance of the requested class, mapping the columns of the result set to named properties in the class. If fetch_style includes [*]PDO::FETCH_CLASSTYPE (e.g. PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE) then the name of the class is determined from a value of the first column.

[*]PDO::FETCH_INTO: updates an existing instance of the requested class, mapping the columns of the result set to named properties in the class

[*]PDO::FETCH_LAZY: combines PDO::FETCH_BOTH and PDO::FETCH_OBJ, creating the object variable names as they are accessed

[*]PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

[*]PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names returned in your result set

 

[marcbraulio]

Is there any particular reason you need it in object form vs array?  Or just preference?

 

For the most part I am sticking with the object form, just for consistency and convenience. If I can access it directly ($var->title), why bother using arrays.

 

Anyways, here's some really good insight for those who are interested:

http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

Just do a quick "CONTROL + F" and type in "Fetch Modes".