Large write to file and browser timing out 1

[mThomas]

I am trying to create a text file by reading data from a MS SQL database. Some files will have 600,000 or so lines. I want to initiate this through a browser. The browser is timing out, and the files are not complete. Here is the basic code I'm using.

$link = mssql_connect($server, '$user', '$pass');
mssql_select_db($db, $link);

$sql = "

select distinct number
from numlist num
join areacodes ar on ar.areacode = LEFT(num.number, 3) 
where state = 'alabama'

";

$res = mssql_query($sql,$link);

set_time_limit(0);
ini_set('max_execution_time', 0);

$myFile = "alabama.txt";
$fh = fopen($myFile, 'w') or die("can't open file");

while ($row = mssql_fetch_array($res)) {

$stringData = "@" . $row['number'] . "@@F211" . $row['number'] . "@@F299@\n";
fwrite($fh, $stringData);

}

fclose($fh);

echo "The list has been created.";

Any ideas on how to keep the browser from timing out and or getting the files to complete with all the data. Once the browser times out, the script stops writing to the file.

The upload_max_filesize is 50M. The file sizes are not all that large considering there is not a lot to each record, so I don't think the max filesize is an issue.

Am I barking up the wrong tree using a browser?

Any ideas would be appreciated.

tia...mike

 

[jpadie]

sounds browser specific.
try outputting something to the browser in the loop to keep the connection alive.

 

[mThomas]

I've tried. How do you get something to be returned to the browser during the loop? I've tried flushing the buffer, but I'm not sure I understand how to do that correctly.

How would you suggest outputting something to the browser?

tia...mike

 

[jpadie]

echo '<br/>processing record '. $c;
$c++;

 

[mThomas]

I wish that worked, however if I echo the "processing record" with the incremented variable inside the loop, the "processing record" is not printed to the browser screen until the loop has completed processing (when testing with a small data set).

With the "processing record" and incrementing variable in the loop, the browser still times out.

I can print the results to the browser just fine and then view source to get the data the way I want it, but if I try to print the data to a file, the browser times out.

How can I return something to the browser while the loop is processing so I can print the data to a file?

tia...mike

 

[jpadie]

How can I return something to the browser while the loop is processing

by placing the output code inside the loop.

if it is not getting displayed then either:

1. the code prior to the output code is causing the time out.
2. something in your set up is doing some buffering. i have noticed that several web servers silently buffer and i am not enough of an apache expert to determine how to stop it (but i have had an attempt below). this can also be caused by the web-server gzipping data (turn off mod_gzip).

one other thing you might consider:

store the entirety of the output string in memory and then write it as a single operation. this will tell you where the failure point is.

try this code for size

/**
 * class to handle script timing
 * 
 * usage
$timer = new timer();
$timer->start();
...code...
$timer->report();
 */
class timer{
	/**
	 * method to start the timer
	 * @return 
	 */
	public function start(){
		$this->start = microtime(true);
	}
 
	/**
	 * method to pause the timer
	 * @return 
	 */
	private function stop(){
		$this->stop = microtime(true);
	}
 
	/**
	 * method to stop the timer and report on the amount of time taken
	 * @return 
	 * @param object $format[optional]
	 */
	public function report($type = 'Script'){
		$this->stop();
		$this->diff();
		if ($type != 'Bare'){
			printf ("<hr/>$type took %s seconds", number_format($this->diff, 8)); 
		} else {
			return number_format($this->diff, 8);
		}
	}
 
	/**
	 * calculate the amount of time taken.
	 */
	private function diff(){
		$this->diff = $this->stop - $this->start;
	}
}


echo <<<CSS
<style type="text/css">
div {border:0px; padding:0px; margin:0px;}
.row{overflow-y:hidden;clear:left; width: 600px; font-family:courier;}
.row .item {float:left; width: 450px;}
.row .timer {float:left; margin-left:8px; width:140px;}
</style>
CSS;

//kill the php output buffer just in case
ob_end_flush();
//send a whole load of information to apache to try to crash the buffer
sendBufferCrasher();
$timer = new timer();
_log ('connecting to the database server');
$link = mssql_connect($server, '$user', '$pass');
_log ('connected to the database server');
_log ('selecting database');
mssql_select_db($db, $link);
_log('selected database');
$sql = "	select distinct number
			from numlist num
			join areacodes ar on ar.areacode = LEFT(num.number, 3) 
			where state = 'alabama'";
_log ('performing query');
$res = mssql_query($sql,$link);
_log ('start recordset iteration');
while ($row = mssql_fetch_array($res)) {
	_log ('retrieving record '. $row['number'] . '<br/><hr/>');
	$data[] = "@" . $row['number'] . "@@F211" . $row['number'] . "@@F299@";
}
_log ('finished iterating recordset');
_log('Outputting data to the file system<hr/>');
file_put_contents('alabama.txt', vsprintf("%s\n", $data));
_log ('finished outputting data to the file system');
_log ("The list has been created.");

function _log($message){
	global $timer;
	echo <<<STR
	
<div class="row">
	<div class="item">$message</div>
	<div class="timer">{$timer->report('Bare')}</div>
</div>
STR;
}

function sendBufferCrasher(){
	for ($i = 0; $i < 2048; $i++){
		echo '&nbsp;';
	}
}

 

[mThomas]

The solution I used was in three steps.

Step 1) I split the lists into manageable chunks of data by splitting the data into seperate lists. Once this script has run the script posts to another script, step 2.

<?PHP

// Read in the form variables - magic quotes is on
$state = $_POST['state'];
$numsplit = $_POST['numsplit'];
$fname = $_POST['fname'];

// check to see if there are form variables
if(!$state || !$numsplit) {

	echo "<center>Please use your back button and make sure you have choosen a state and entered the number of splits.</center>";
	
} else {

// define the server, user and pass to connect to the db
$server = '';
$user = '';
$pass = '';

// create a db server connection
$link = mssql_connect($server, $user, $pass);
// selct the db
mssql_select_db('', $link);

// create the templist and populate it with data
$sql1 = "

create table templist (
number BIGINT NULL
)

insert into templist (number)
select distinct number -- this makes sure the exported list is deduped
from list 
join areacodes ar on ar.areacode = LEFT(.number, 3) 
where state = '$state'

";

// run the above query
mssql_query($sql1,$link);

// delete any removes from the templist
$sqlrem = "delete from templist
where number in (select number from removes)";

// run the above query
mssql_query($sqlrem,$link);

// count the total numbers in the templist
$sqlnum = "select count(number) as num from templist";

//run the above query
$resnum = mssql_query($sqlnum,$link);;

// check to see if the list count is smaller than the numsplits
// if it is smaller then set the numsplit to the actual list count value
while ($rownum = mssql_fetch_array($resnum)) { 

if ($rownum['num'] < $numsplit) { $numsplit = $rownum['num']; }
 
}

// perform the list splitting
$sql = "

-- declare some variables
declare @tlist int,
		@listc int,
		@lcount int,
		@tablename varchar(200),
		@state varchar(100)

	-- set some variables
	set @lcount = 0
	set @state = '$state'
	
 -- set the number of splits
 SELECT @listc = dbo.RoundUp((count(*)/$numsplit))
 from templist
 
 -- set the value for the rowcount
 SELECT @tlist = count(*)/@listc
   FROM templist

-- set the rowcount to be used in the initial iteration
set rowcount @tlist

-- start the list splitting loop
while @lcount < @listc

-- begin the looping
begin
		-- set the loop counter
		set @lcount = @lcount + 1

		-- set the tablename to be used in the execute statements
		select @tablename = 'list_' + @state + '_' + convert(char(12), @lcount, 14)

		-- create the individual tables
		execute ('create table ' + @tablename + '
		( 
		  number BIGINT    NULL
		)')

		-- this is really not necessary in this context 
		set nocount off

		-- perform the insert
		execute('insert into ' + @tablename + ' (number) 
		select number
		from templist')

		-- this is not necessary in this context
		set nocount on
		
		-- delete data from the templist
		-- this will only delete up to the current iteration of the rowcount
		delete from templist		

--end the looping
end
		-- get rid of the templist
		drop table templist

";

// run the above query
$res = mssql_query($sql,$link);

// count the number of splits created
$sql2 = "
		Select Count(*) As TableCount
		From   Information_Schema.Tables
		where Table_Name like '%$state%'
		";
// run the above query
$res = mssql_query($sql2,$link);		

// declare and set the total of lists to be used in the next script
while ($row = mssql_fetch_array($res)) {

$gocount = $row['TableCount'];

}

mssql_free_result($res); // clean up some memory
mssql_free_result($resnum); // clean up some memory

mssql_close($link); // close the db connection

// print some information to the screen
echo "<center>The $state List Data Segments have been created.<br /><br />The list will now be generated.</center>";

			// pass some data to a form and auto submit the form 
			// this starts the next part of the process
			print "<body onload='submitForm()'>
					<form name='form' method='post' action='listbystate.php'> 
					<input type='hidden' name='count' value='1'>
					<input type='hidden' name='state' value='$state'>
					<input type='hidden' name='gocount' value='$gocount'>
					<input type='hidden' name='fname' value='$fname'>  
					</form> 
					<SCRIPT LANGUAGE='JavaScript'>
						function submitForm(){
						document.form.submit(); 
						}
					</SCRIPT>";

}// end if !numsplit

?>

Step 2) In step 2 a script appends data to a text file based on the lists created in the step 1. As each list is read and appended, the appending script posts some variable to a third script, step 3.

<?PHP

// read in the form variables
$count = $_POST['count'];
$state = $_POST['state'];
$gocount = $_POST['gocount'];
$fname = $_POST['fname'];

// check to see if the count variable exists
// i probably should check the other ones to but I'm lazy ;)
if (!$count) {$count = 1;}

// return some info to the screen
echo "<center>Appended Data Segment: $state_" . $count . "</center>";

// define the server, user and pass to connect to the db
$server = '';
$user = '';
$pass = '';

// create a db server connection
$link = mssql_connect($server, $user, $pass);

//select the db
mssql_select_db('', $link);

// declare and set a variable to be used in the next query
$gostate = "list_" . $state . "_" . $count;

//grab the numbers
$sql = "

select distinct number
from $gostate

";

//run the above query
$res = mssql_query($sql,$link);

// set a specific execution time
// this could also be set to 0
set_time_limit(0);
ini_set('max_execution_time', 0);

//path to the file
$myFile = "./states/$state.txt";

//open file
$fh = fopen($myFile, 'a') or die("can't open file");

//loop through the file
while ($row = mssql_fetch_array($res)) {
// set the data to append to the file
$stringData = "@@F201$fname@@F211" . $row['number'] . "@@F299@\n";
fwrite($fh, $stringData); // write to the file

}

fclose($fh); // close the open file
mssql_free_result($res); // release the memory used by the query
mssql_close($link); // close the db connection

$count++; /* increment a counter to set a sleep variable and print
             to the screen some information based on the modulus
			 value returned on every iteration which is divisble by
			 5 [no remainder]. the sleep is used to allow for server
			 lag when creating appending to the opened file.*/

	if($count % 5 == 0 ) { 
	
	echo "<br /><br /><center>I slept for 4 seconds...</center><br /><br />"; 
	sleep(4); 
	
	} // close the if loop checking the modulus value
	
	
	/* next the script checks to see if the number of times the script has 
	   run is less than the number of lists to create plus 1. this tells
	   the script to stop auto submitting to the next script once the correct
	   number of lists has been created */
	if($count < $gocount+1) {

			print "<body onload='submitForm()'>
					<form name='form' method='post' action='listbystate2.php'> 
					<input type='hidden' name='count' value='$count'>
					<input type='hidden' name='state' value='$state'> 
					<input type='hidden' name='gocount' value='$gocount'>
					<input type='hidden' name='fname' value='$fname'>
					</form> 
						<SCRIPT LANGUAGE='JavaScript'>
						function submitForm(){
						document.form.submit(); 
						}
					</SCRIPT>";
		
 } else { 
 
/* now that the script has created the correct number of lists the script
   cleans up the db by droping the data segments/splits (the tables created
   during the previous script list splitting processes) */
 
 $counter = 1; /* this counter is used as part of the table naming convention. The tables or
                  data segments are named 1 through however many there are. So the script 
				  just runs a loop starting with 1 and appends the current state name and 
				  the counter value */

 while ($counter < $gocount+1) {

 $godropstate = "list_" . $state . "_" . $counter; /* this creates the table names of the
													  individual tables */
$sql2 = "drop table $godropstate"; //this drops the tables from the database

mssql_query($sql2,$link); // run the above query

$counter++; // increment the loop counter value

} // close while loop

echo "<center>
<form action=\"controlpanel.php\" method=\"get\">
<input name=\"submit\" type=\"submit\" value=\"Back to Control Panel\"> 
</form>
</center>";

// print some info to the screen
echo "<br /><br /><center>The Data Segments were cleaned.<br /><br />The $state List has been generated.</center>";
 
 } // close the current interation which is the number of times the script has run

?>

Step 3) This step takes the variable from step 2 and posts them back to the script in step 2.

<?PHP

$count = $_POST['count'];
$state = $_POST['state'];
$gocount = $_POST['gocount'];
$fname = $_POST['fname'];

echo "<center>Appending Segment: $count</center>";

			print "<body onload='submitForm()'>
					<form name='form' method='post' action='listbystate.php'> 
					<input type='hidden' name='count' value='$count'> 
					<input type='hidden' name='state' value='$state'> 
					<input type='hidden' name='gocount' value='$gocount'>
					<input type='hidden' name='fname' value='$fname'> 
					</form> 
					<SCRIPT LANGUAGE='JavaScript'>
						function submitForm(){
						document.form.submit(); 
						}
					</SCRIPT>";


?>

So basically step 1 splits the data into chunks, step 2 appends a chunk of data to the text file then passes the current variables to step 3. Step 3 then passes the variable back to step 2. In each step information is returned to the browser.

I've commented the scripts explaining what I'm doing. I'm sure there is a better way, probably a cooler way, but this process works and will work on the large files being appended.

I hope this makes sense

Thanks jpadie for your assistance

mike

 

[ingresman]

I know you have a fix but I thought I'd add my thoughts anyway !
In the bad old days when CGI wall the rage, you could achieve this with Non Parsed Headers (NPH) not easy to do (or even possible?) with PHP, but have a look at

http://www.weberdev.com/get_example-508.html

which might give you a start
alternatily you might look at using AJAX in the browser to orchestrate the backend proceses work flow.

 

[jpadie]

ajax would be my preference if you had to resort to this, but to be honest we never found out where the timeout was occurring so solution providing was a bit shot in the dark!

 

[mThomas]

I couldn't get any version of a refresh to work. I tried ingresman's link, but the script waits until completion to print to the browser. I also tried multiple variations of messing with the buffer.

I was running out of time to keep fussing with it, so my solution works and was quick.

ajax would have been my next step (which should probably should have been where I started), but the project is done

ingresman, thanks for your input. I know I can always find some help here.

tia... mike