Returning objects

PHP
1

Hey,

I’m trying to figure out a way to return results from a DB with the help of PDO and stmt->fetch(PDO::FETCH_OBJ).

When I do this inside the method, it works fine:


while ($objRow = $objStatement->fetch(PDO::FETCH_OBJ))
  echo $objRow->id.'<br />';

What I want to do is return those objects and loop them later, possibly through the HTML side.

When I do this, it does an infinite loop:


public function methodTest() {
    $strQuery = 'SELECT customers.id
               FROM customers
               LIMIT 0, 20';
    $objStatement = $this->DB->prepare($strQuery);
    $objStatement->execute();

return $objStatement->fetch(PDO::FETCH_OBJ);
}

$objUser = new User();

while ($objRow = $objUser->methodTest())
  echo $objRow->id;

So I tried it this way:


public function methodTest() {
    $strQuery = 'SELECT customers.id
               FROM customers
               LIMIT 0, 20';
    $objStatement = $this->DB->prepare($strQuery);
    $objStatement->execute();

return $objStatement;
}

$objUser = new User();

while ($objRow = $objUser->methodTest()->fetch(PDO::FETCH_ASSOC))
  echo $objRow->id;

But it returned errors as well. The only way I was able to accomplish is to return an array, which is fine, but I was hoping to make it more object friendly:


$strQuery = 'SELECT customers.id
               FROM customers
               LIMIT 0, 20';
    $objStatement = $this->DB->prepare($strQuery);
    $objStatement->execute();
    
    if ($objStatement->rowCount() == 0)
      return false;
    else {
      $arrRows = array();
      
      while ($arrRow = $objStatement->fetch(PDO::FETCH_ASSOC))
        $arrRows[] = $arrRow;
      
      return $arrRows;
    }
  }


$objUser = new User();

//Output correctly:
foreach($objUser->methodTest() as $arrRow)
  echo $objRow['id'];

Thanks.

2

If you actually want an array, because maybe you want to manipulate it, or pass it to some other function that accepts an array as an argument, or maybe you want to save it for a bit because you need to loop it multiple times, or use parts of it etc… use fetchAll()
Most of the time, this is what you would probably use.

fetchAll() buffers all the rows into php memory so it can create an array. The drawback is memory consumption. Maybe you need to work with a very large result. It simply might require too much memory.

So, there’s methods to use an unbuffered query where you fetch only small parts of the result set at a time. You could use the fetch() + a loop, or you could even just loop right over the statement object itself(foreach provides special support for this)


$stmt->execute();
foreach ($stmt as $row) {
//...
}
3

Hmm, buffered is true by default, but I think the doc is confusing.

As I understand it:
buffered = false means php is reading from mysql resource as it needs to
buffered = true means php will read the entire result set and then carry on

So by default there would be no difference bar creating the array/object returned?

4

The fetch functions with database queries work on a specific query - every time the fetch() is called, it returns the data then increments the row from which to fetch from.

By calling the method in the loop, you are actually calling a NEW query every time.

I’d recommend a flavour of the second:

public function methodTest() {
    $strQuery = 'SELECT customers.id
               FROM customers
               LIMIT 0, 20';
    $objStatement = $this->DB->prepare($strQuery);
    $objStatement->execute();
 
return $objStatement;
}
 
$objUser = new User();
$objRecordSet = $objUser->methodTest();
while ($objRow = $objRecordSet->fetch(PDO::FETCH_OBJ))
  echo $objRow->id;

Notice also that you’re calling the fetch method with FETCH_ASSOC yet you are trying to treat it as an object - ASSOC returns an associative array. The above code should fix that.

5

Unless you have a need for the statement object (you mention looping in html?) then you should use fetchAll() which will give you an array of records.

6

Hi Jake,

With what you provided, doesn’t the issue still stand that a NEW query is being called every time?

I did catch the errors I made in the 2nd attempt, thanks for that.

7

According to the manual using fetchAll() is very resourceful. So I’m trying to avoid that. Currently there is a LIMIT on the data that can be returned, but that could change.

8

Nope :slight_smile:

The function is called before the loop. the FetchAll is run on the object returned, but that object remains the same.

9

I see exactly where I went wrong, thanks :slight_smile:

10

What the manual says is: don’t fetch your entire db and manipulate it with php, instead use mysql to manipulate.

It doesn’t say avoid fetchAll because it’s resource intensive. If you’re going to pull your whole db then you will use resources regardless.

11

Is there any different between using an statement object or fetchAll ? I mean, I still have to do a loop to display the data correctly anyway.

12

so from crmalibu example, there doesn’t seem to be such a drastic change from what Jake originally fixed for me. Unless I would want an array, which in this case I don’t.

Or am I missing something? 1000 ways to skin a cat, I always say!

13

fetchAll() would still use more memory than iterating row by row, regardless of whether the result is internally buffered.

You can see this by uncommenting to get different combinations


//$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
//$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$stmt = $dbh->prepare($sql);
$stmt->execute();
//$stmt->fetch();
//$stmt->fetchAll();
printf("%s\
", number_format(memory_get_peak_usage()));
14

Testing, it doesn’t use any more memory till you get more than 60 rows in the result set. So if you’re talking about a web page, it makes negligible difference.

This makes a big difference, but again, you wouldn’t select 10k rows for a web page.


$sql = "SELECT * FROM test"; // 10000 rows
$stmt = $db->prepare($sql);

// this is the default setting
//$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$t = microtime();
$stmt->execute();

var_dump(microtime()-$t); // 0.01

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$t = microtime();
$stmt->execute();

var_dump(microtime()-$t); // 0.001