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".
The script works wonderfully on my Powerbook...but...I'm having some performance issues when using this script on Windows 2000 Server IIS 5.0.
Do you see anything wrong with this script?
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.
<?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");
?>
