Reputation: 54
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
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
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