CODEWITHSUNDEEP
phpmysql
Martin Perry
Martin Perry

Reputation: 9527

PHP out of memory while fetching data from MySQL

I have this script:

class DBWrapper 
{
   private $conn;

   public function __construct()
   {
      $this->conn = mysqli_connect(/* LOGIN INFO */);
   }

   public function select($q)
   {
       return new DBQuery($this->conn, $q);
   }
}

class DBQuery 
{
   private $q;
   private $tmp;
   public function __construct($conn, $q)
   {
     $this->tmp = mysqli_query($conn, $q);

   }

   public function getRow()
   {
      return mysqli_fetch_array($this->tmp)
   }
}

class Users
{
   private $tmp;
   private $dbWrapper;

   public function __construct($db)
   {
     $this->dbWrapper = $db;
   }


   public function getRow()
   {
     return $this->tmp->getRow();
   }

   public function Activate()
   {
     $select = " SELECT * FROM  users";                         

     $this->tmp = $this->dbWrapper->select($select);
   }
}

$users = new Users($db);
$users->Activate(); 

while($t = $users->getRow())
{
    echo memory_get_usage().'<br>';
}

DBWrapper class uses mysqli_fetch_array in getRow(). memory_get_usage is increasing in every iteration of while, so it causes a problem if I iterate over tousands of users. Why is the single user in $t not released after single iteration of while?

Upvotes: 1

Views: 259

Answers (1)

Ofir Baruch
Ofir Baruch

Reputation: 10346

According to the PHP manual regarding Buffered and Unbuffered queries:

The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology "store result" is also used for buffered mode, as the whole result set is stored at once.

At that time mysql_* was supported so the suggested solution was to use: mysql_unbuffered_query(), this is irrelevant anymore.

So in case you're just retrieving data, the other solution would be to use: mysqli::use_result in combination with mysqli_result::free

Upvotes: 1

Related Questions