Creating specially formated text files

Hi all!

I created a small "tool" in PHP to extract data from MYSQLin text files with fixed-field widths.
This tool will be used to produce mailing labels for magazines and therefore the data must be formatted to fixed-width fields. Basically, I need to be able to set a fixed length for each field and keep the empty spaces in between the fields with "pad charcaters".

I managed to successfully produce the text file with the padding characters (using sprintf)!

...but...I'm having some performance issues when using this script on Windows 2000 Server IIS 5.0.

This is the script I wrote:
The user uses an Html form where he inserts the name of the magazine ($nome_rivista), Issue number ($num_rivista ), issue year ($anno_rivista), Magazine code ($cod_rivista), and total number of records ($max_results) he wants to extract.

[CODE]
<?php
// not needed on Mac enviornment
//set_time_limit(60);
//error_reporting(E_ALL ^ E_NOTICE);
/********************************************
PHP - text extraction code
for magazine labels.
Page: output_weekit_mod.php
Developer: Lollo
Created: 07/01/2005
Modified: 20/01/2005
*********************************************/
// Database Connection
include 'inc/pbf_db.php';

// Post from form: set max number of records to extract
$max_results = $_POST['max_results'];

/********************************************
Post from form: magazine code and description
of the records being extracted
*********************************************/
$nome_rivista = $_POST['nome_rivista'];
$num_rivista = $_POST['num_rivista'];
$anno_rivista = $_POST['anno_rivista'];
$cod_rivista = $_POST['cod_rivista'];


// create description of magazine
$des_contatto = ("Invio ".$nome_rivista." "."numero ".$num_rivista." "."anno"." ".$anno_rivista);


/********************************************
Post to contatti the above data to keep
track of the magazine labels being created.
*********************************************/
$sql_contatti = mysql_query("INSERT INTO contatti (des_contatto, data_contatto, cod_rivista)
            VALUES('$des_contatto', now(), '$cod_rivista')") or die (mysql_error());

// Grab the id for later use
$cod_contatto = mysql_insert_id();

/********************************************
Perform MySQL query to extract records that
will be used to produce the magazine labels
*********************************************/
$sql_extract = mysql_query("SELECT persone.cod_n, persone.cognome, persone.nome, persone.nomeaz1, persone.via1, persone.civico, persone.cap, persone.localita, persone.provincia, persone.sesso, persone.tit_accad, persone.itaester, persone.via_aggiunta FROM persone, diritti_riviste, ultimo_contatto
WHERE diritti_riviste.cod_rivista = '644'
and persone.cod_n = diritti_riviste.cod_n
and diritti_riviste.cod_n = ultimo_contatto.cod_n
and diritti_riviste.cod_rivista = ultimo_contatto.cod_rivista
and diritti_riviste.cod_priorita BETWEEN 1 AND 3
and sospeso = 'N'
and cancellato = 'N'
and privacy = 'N'
and do_not_mail = 'N'
and estero = '0'
and LENGTH(CONCAT(cognome,nome)) < 30
ORDER BY diritti_riviste.cod_priorita, ultimo_contatto.data_uc DESC
LIMIT 0, $max_results");

// loop to extract data
while($row = mysql_fetch_array($sql_extract)) {

//set variable for easier editing
//$cod_rivista = $row['cod_rivista'];
$cod_n = $row['cod_n'];
$cognome = $row['cognome'];
$nome = $row['nome'];
$nomeaz1 = $row['nomeaz1'];
$via1 = $row['via1'];
$civico = $row['civico'];
$cap = $row['cap'];
$localita = $row['localita'];
$provincia = $row['provincia'];
$sesso = $row['sesso'];
$tit_accad = $row['tit_accad'];
$itaester = $row['itaester'];
$via_aggiunta = $row['via_aggiunta'];


/********************************************
I perform a MySQL query to insert in
contatti_pers the following records
$cod_contatto, $cod_rivista, $cod_n
This is used as a flag to determin which records
received a certain magazine.
/********************************************/

$sql_contatti_pers = mysql_query("INSERT INTO contatti_pers (cod_contatto, cod_rivista, cod_n)
            VALUES('$cod_contatto', $cod_rivista, '$cod_n')") or die (mysql_error());

//edit variables for correct formating
$nominativo = $cognome.",".$nome;
$via = trim($via1).",".trim($civico);
$via_ok = substr($via,0,31);
$via_aggiunta1 = substr($via_aggiunta,0,31);
$nomeaz1 = substr($nomeaz1,0,31);
$localita_ok = substr($localita,0,22);

/********************************************
Set the automatic download section
/********************************************/
// Output buffer not needed on Mac enviornment
//ob_start();
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=file_mod3_weekit.txt");
header("Pragma: no-cache");
header("Expires: 0");
// test print to screen (str_pad or printf)
print($cod_rivista."0".sprintf("%08d", $cod_n).sprintf("%-31s", $nominativo).sprintf("%-31s", $nomeaz1).sprintf("%-31s", $via_ok).sprintf("%-5s", $cap).sprintf("%-22s", $localita_ok).sprintf("%-2s", $provincia)." "." ".sprintf("%01d", $sesso).sprintf("%03d", $tit_accad).sprintf("%-2s", $itaester).sprintf("%04d", $anno_rivista).sprintf("%02d", $num_rivista).sprintf("%-52s", "  500").sprintf("%-31s", $via_aggiunta1)."C"."\r\n");

}

if(!$sql_extract){
      echo 'Errore - 001';
} else {

exit();

}

/********************************************
Here I need to redirect the user (operator)
extracting the labels to a thank you page
(e.g. file download complete !)
...but I can't manage to have it work...although
this is the least of my problems.
/********************************************/
//header("Location: index.php") or scrpt("index.php");

?>

[/CODE]

I keep on getting the following message when I extract more than 2,000 records on a Win system:
[quote]
Maximum execution time of 30 seconds exceeded in
Fatal error:  Maximum execution time of 30 seconds exceeded in...
[/quote]
I get this error printed at the end of my text file...and obviously the scipt has only managed to print the first 1,800 or so records.

I tried to fix this by inserting set_time_limit(60); at the top the script.   I manage to extract at least 10,000 records without getting the above error...but it isn't enough...and on top of it the script is very slow in executing (5 minutes vs 15 seconds on my Powerbook).

My problem is that I have to extract at least 25,000 records at the time. When I try to extract 20,000 records I get the following error:

[quote]
Server: Microsoft-IIS/5.0 Date: Fri, 14 Jan 2005 11:29:56 GMT Connection:
close Content-Length: 186 Content-Type: text/html  <head><title>CGI Application
Timeout</title></head>
<body><h1>CGI Timeout</h1>The specified CGI application exceeded the allowed
time for processing.  The server has deleted the process.

[/quote]

Why do I get this CGI error???  ...and why can't I extract at least 25,000 records.  What is wrong with this script?  Why is it so slow on Windows 2000 Server? Would I have the same problems on a Linux system?

Obviously, the script runs PERFECTLY on my Powerbook G4 (1GHZ - RAM: 768 MB) running MAC OS 10.3, Mysql 4.0.22-standard, PHP 4.3.9 and Apache/1.3.33 (Darwin).  It can extract 25,000 0r even 50,000 records in less than 20 seconds.

The client's server is an IBM Netfinity 3500 (PII ?Mhz RAM: 384 MB) running on Windows 2000 server, MySQL 4.1.9 , PHP 4.3.10 and IIS 5.0.

Please, help me optimize this script for Windows or tell how I can make it faster.  

Thanks,
-L.