Processing a large result set with php

Question

My scenario is like this ,i have a huge dataset fetched from mysql table

$data = $somearray; //say the number of records in this array is 200000

iam looping this data,processing some functionalities and writing this data to an excel file

 $my_file = 'somefile.csv';
 $handle = fopen($my_file, 'w') or die('Cannot open file:  ' . $my_file); file
     for($i=0;$i<count($data);$i++){
         //do something with the data
         self::someOtherFunctionalities($data[$i]); //just some function    
         fwrite($handle, $data[$i]['index']); //here iam writing this data to a file
      }
 fclose($handle);

My problem is that the loop gets memory exhaustion ...it shows "fatal error allowed memory size of.." is there anyway to process this loop without exhaustion

Due to the server limitation im unable to increase php memory limit like

ini_set("memory_limit","2048M");

Im not concerned about the time it takes..even if it takes hours..so i did set_time_limit(0)

Can't you read the MySQL data in batches or process it row by row rather then saving the whole set to a variable? — Prix
– Prix, Commented Sep 5, 2014 at 8:30
I have tried batch processing also...but it still hangs up...is there someway we can release the memory at the end of each loop — coolguy
– coolguy, Commented Sep 5, 2014 at 8:33
To release the memory just set the variable to null, the garbage collector should handle the memory. — Magic-Mouse
– Magic-Mouse, Commented Sep 5, 2014 at 8:35
Do you need to fetch all data into memory? Can't you fetch one row at a time? — Marek
– Marek, Commented Sep 5, 2014 at 8:35

MajidTaheri · Accepted Answer · 2014-09-05 08:44:06Z

5

your job is linear and you don't need load all data. use Unbuffered Query also use php://stdout(don't temp file) if send this file to httpClient.

<?php
$mysqli  = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
$my_file = 'somefile.csv'; // php://stdout
$handle = fopen($my_file, 'w') or die('Cannot open file:  ' . $my_file); file

if ($uresult) {
   while ($row = $uresult->fetch_assoc()) {
   // $row=$data[i]
      self::someOtherFunctionalities($row); //just some function    
     fwrite($handle, $row['index']); //here iam writing this data to a file
   }
}
$uresult->close();
?>

answered Sep 5, 2014 at 8:44

MajidTaheri

4,0237 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajant · Accepted Answer · 2014-09-05 08:36:00Z

2

Can you use "LIMIT" in your MySQL query?

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

http://dev.mysql.com/doc/refman/5.0/en/select.html

answered Sep 5, 2014 at 8:36

Ajant

1584 silver badges17 bronze badges

Comments

Magic-Mouse · Accepted Answer · 2014-09-05 08:39:26Z

1

If you don't worry about time, take 1000 rows at a time, and just append the rows to the end of the file, eg. make a temp file that you move and/or rename when the job is done.

First select count(*) from table
  then for($i = 0; i < number of row; i = i + 1000){
  result = SELECT * FROM table LIMIT i,1000; # Retrieve rows 6-15
  append to file = result
}
move and rename the file

this is verry meta code, but the process should work

edited Sep 5, 2014 at 8:39

answered Sep 5, 2014 at 8:31

Magic-Mouse

61310 silver badges21 bronze badges

2 Comments

Marek Over a year ago

The query needs order by, else it's not guaranteed it will select the next batch. It also assumes the data does not change.

Magic-Mouse Over a year ago

Feel free to use the edit button to improve my suggestion, still as i said, it is just a list form on how to do it, not a working example.

Collectives™ on Stack Overflow

Processing a large result set with php

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related