Babby
Reputation: 54
fetching millions of records from the database using php
I am facing problem with fetching millions of records from the mysql database using php,I want to load this records into excel file.When I am executing query,if the result set is having few records (in hundreds)then it's saved into the excel file,but when I am fetching million records it's display an error called
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
and My code is:
<?php
/*
author: shobhan
Date: 20-10-2014
Description: This file creates excel sheet from the database based on the selection and available for download
*/
ini_set('max_execution_time', 6000);
ini_set('memory_limit','1000M');
set_time_limit(0);
//echo ini_get('memory_limit');
include("db/config.php"); //include global configuration file
global $db_host,$db_name,$db_user,$db_password; //include global variables
$con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
$flag=1;
$download_filename="";
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=suburban_data.csv");
// Disable caching
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies
//echo $query;
//echo "<br/><br/><br/><br/><br/><br/><br/><br/>";
$result_array=array();
$output = fopen("php://output", "w");
$headings=array("Patient Name","Mobile","Visit Date","Centre","Profiles","Gender","Age");
fputcsv($output, $headings);
function preparewhere($ptstring){
global $db_host,$db_name,$db_user,$db_password; //include global variables
global $download_filename;
global $flag;
$year=$_POST['year'];
$centre=$_POST['centre_ids'];
$gender=$_POST['gender'];
$profile=$_POST['profile_ids'];
$testresult=$_POST['testresult'];
$age=$_POST['age'];
$treatmenttype=$_POST['treatmenttype'];
$where="";
$year_condition="";
$centre_condition="";
$gender_condition="";
$test_condition="";
$testresult_condition="";
$age_condition="";
if($year=="0"){
$year_condition="Visit.VstDate >= '2013-01-01'";
}
else if($year=="2013"){
$year_condition="Visit.VstDate >= '2013-01-01' and Visit.VstDate < '2014-01-01'";
$download_filename.="2013";
}
else{
$year_condition="Visit.VstDate >= '2014-01-01'";
$download_filename.="2014";
}
//centre condition
if($centre!=0){
$centre_condition=" and centres.SysNo in(".$centre.")";
$download_filename.="_".$centre;
}
//gender condition
if($gender!="0"){
$gender_condition=" and patient.PatSex= '".$gender."'";
$download_filename.="_".$gender;
}
if($flag==2){ //if input contains only profiles
$test_condition=" and VisitProfile.ProfCode in(".$ptstring.")";
}
else{
$test_condition=" and Test.TestCode in(".$ptstring.")";
}
//test result
if($flag==1){
if($testresult!="0"){
if($testresult=="HL"){
$testresult_condition=" and (result.AbnormalFlag='H' or result.AbnormalFlag ='L')";
$download_filename.="_abnormal";
}else{
$testresult_condition=" and result.AbnormalFlag='N'";
$download_filename.="_normal";
}
}
}
//age
if($age!="0"){
if($age=="0-30")
$age_condition=" and Visit.Age>=0 and Visit.Age<=30";
else if($age=="31-40")
$age_condition=" and Visit.Age>=31 and Visit.Age<=40";
else if($age=="41-50")
$age_condition=" and Visit.Age>=41 and Visit.Age<=50";
else if($age=="51-60")
$age_condition=" and Visit.Age>=51 and Visit.Age<=60";
else
$age_condition=" and Visit.Age>60";
}
$where=$year_condition.$centre_condition.$gender_condition.$test_condition.$testresult_condition.$age_condition;
return $where;
}
//echo "where condition ".preparewhere();
//echo "<br/>";
function preparequery(){
global $flag;
$selectquery="";
if($flag==1){
$selectquery="select Distinct(patient.PatName), Visit.VstMobile, Visit.VstDate, centres.SysField, Test.TestName, patient.Patsex, Visit.Age from Visit
inner join centres on Visit.RegistrationCentreCode=centres.SysNo
inner join patient on Visit.VstPatCode=patient.Patcode
inner join result on result.TrJobCode=Visit.VstCode
inner join Test on Test.TestCode=result.TrTestCode
inner join Param on Param.ParamCode=result.TrParamCode
";
}
else{
$selectquery="select Distinct(patient.PatName), Visit.VstMobile, Visit.VstDate, centres.SysField, profiles.ProfName, patient.Patsex, Visit.Age from Visit
inner join centres on Visit.RegistrationCentreCode=centres.SysNo
inner join patient on Visit.VstPatCode=patient.Patcode
inner join VisitProfile on VisitProfile.VstCode=Visit.VstCode
inner join profiles on profiles.ProfCode=VisitProfile.ProfCode
";
}
return $selectquery;
}
function strpos_offset($needle, $haystack, $occurrence) {
// explode the haystack
$arr = explode($needle, $haystack);
// check the needle is not out of bounds
switch( $occurrence ) {
case $occurrence == 0:
return false;
case $occurrence > max(array_keys($arr)):
return false;
default:
return strlen(implode($needle, array_slice($arr, 0, $occurrence)));
}
}
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=suburban_data.csv");
// Disable caching
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies
if(!mysqli_connect_errno())
{
$count=0;
$pcount=substr_count($_POST['profile_ids'], 'p'); //stores number of profiles from the string
$ptcount=substr_count($_POST['profile_ids'], ','); //stores number of tests or profiles selected
if($pcount==0)
$flag=1;
else if($pcount==$ptcount+1)
$flag=2;
else
$flag=3;
if($flag==1 || $flag ==2){ //if the selection contains either profiles or tests
//echo "profiles".$ptstring;
if($flag==1)
{
$tstring=$_POST['profile_ids'];
//echo "test".$tstring;
$mobile="";
$query_count_mobilenumbers=preparequery()." where ".preparewhere($tstring);
$con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
$start = 0;
$limit = 1000;
do {
$mobile_result = mysqli_query(
$con,
$query_count_mobilenumbers." LIMIT {$start}, {$limit}",
MYSQLI_USE_RESULT // this always helps for this kind of processing (makes a smooth streaming)
);
$nbRows = 0; // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`
while($row = mysqli_fetch_array($mobile_result)) {
fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6]));
$nbRows ++;
}
// next batch
$start += $limit;
} while ($nbRows);
}
else{
$ptstring=str_replace("p","",$_POST['profile_ids']);
//echo "profiles are".$_POST['profile_ids'];
$query_count_mobilenumbers=preparequery()." where ".preparewhere($ptstring);
//echo "query is".$query_count_mobilenumbers;
$con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
$start = 0;
$limit = 1000;
do {
$mobile_result = mysqli_query(
$con,
$query_count_mobilenumbers." LIMIT {$start}, {$limit}",
MYSQLI_USE_RESULT // this always helps for this kind of processing (makes a smooth streaming)
);
$nbRows = 0; // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`
while($row = mysqli_fetch_array($mobile_result)) {
fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6]));
$nbRows ++;
}
// next batch
$start += $limit;
} while ($nbRows);
}
}
else{ //if the selection contains both profiles and tests
//echo "inside both";
$no_of_profiles=substr_count($_POST['profile_ids'], 'p');
$nth_comma_index=strpos_offset(',',$_POST['profile_ids'], $no_of_profiles); //gets the nth comma's index
$pstring=str_replace("p","",substr($_POST['profile_ids'],0,$nth_comma_index));
$tstring=substr($_POST['profile_ids'],$nth_comma_index+1);
$mobile="";
$flag=1;
//query as tests
//echo "inside flag";
$query1=preparequery()." where ".preparewhere($tstring);
$con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
//echo "quer".$query1;
$mobile_result1 = mysqli_query($con,$query1);
$start = 0;
$limit = 1000;
do {
$mobile_result1 = mysqli_query(
$con,
$query1." LIMIT {$start}, {$limit}",
MYSQLI_USE_RESULT // this always helps for this kind of processing (makes a smooth streaming)
);
$nbRows = 0; // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`
while($row = mysqli_fetch_array($mobile_result1)) {
fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6]));
$nbRows ++;
}
// next batch
$start += $limit;
} while ($nbRows);
$flag=2;
//echo "inside flag2";
$query2=preparequery()." where ".preparewhere($pstring);
$start = 0;
$limit = 1000;
do {
$mobile_result2 = mysqli_query(
$con,
$query2." LIMIT {$start}, {$limit}",
MYSQLI_USE_RESULT // this always helps for this kind of processing (makes a smooth streaming)
);
$nbRows = 0; // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`
while($row = mysqli_fetch_array($mobile_result2)) {
fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6]));
$nbRows ++;
}
$start += $limit;
} while ($nbRows);
}
}
else{
echo "failed to connect to database";
}
fclose($output);
?>
Upvotes: 0
Views: 4342
Answers (2)
Bharat Rawat
Reputation: 199
You can try ini_set('memory_limit', '-1'); in your PHP script before the fetch/select query, if you are using shared hosting else set post_max_size in php.ini. ini_set('memory_limit', '-1'); working fine for me.
For the safe side, you can also set the memory size instead of -1.
Upvotes: 0
axiac
Reputation: 72425
I bet the error you can find in the server's error log says something about the PHP script running out of memory. This happens because by default the query to the database completes when all the rows are received from the database server. And because you asked for millions of rows the result set is huge and takes a lot of memory on the PHP process.
I think you should use MYSQLI_USE_RESULT as the third parameter of your call to mysqli_query():
$mobile_result = mysqli_query($con, $query_count_mobilenumbers, MYSQLI_USE_RESULT);
This makes mysqli_query() return faster (probably as soon as the first row from the result is received from the server) and the returned result set stores the received rows only until they are retrieved by the program using mysqli_fetch_*() functions.
I never used mysqli, the old mysql extension had a separate function for this purpose (called mysql_unbuffered_query()) but the mysql extension is dead so do not use it.
If this approach still does not work then you can try to get the data from the server in batches using LIMIT.
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=suburban_data.csv");
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies
// The next line is very important: it prevents PHP abruptly stop the script
// before it finishes what it's doing
set_time_limit(0);
$output = fopen("php://output", "w");
$headings=array("PatientName","Email","VisitDate","Centre","Profiles","Parameter","Age");
fputcsv($output, $headings);
$start = 0;
$limit = 1000;
do {
$mobile_result = mysqli_query(
$con,
$query_count_mobilenumbers." LIMIT {$start}, {$limit}",
MYSQLI_USE_RESULT // this always helps for this kind of processing (makes a smooth streaming)
);
$nbRows = 0; // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`
while($row = mysqli_fetch_array($mobile_result)) {
fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6]));
$nbRows ++;
}
// next batch
$start += $limit;
} while ($nbRows); // Stop when the query didn't return any row
fclose($output);
Upvotes: 2
Related Questions
- Generating large Excel files from MySQL data with PHP from corporate applications
- Using php/MySQL to run queries on 3+million rows
- Excel to MySQL using PHP
- Convert large Excel Sheet to Mysql?
- how to convert 500,000 rows of data with 39 columns from MySQL into Excel
- How to increase speed of PHP script that using MySQL?
- Optimizing EXCEL + MySQL Processing
- Fetching few records from mysql database having million entries
- Insert large amount of Excel data into a MySQL database
- Export large amount of data (20000 rows) from Mysql to excel (with Php)