I have a php script that is supposed to generate a list of plate numbers with some data about coverage for cardealers that have multiple cars covered under one policy.
The data for this list is fetched through mysqli_connect mysqli_query and mysqli_fetch_array and subsequently converted to HTML.
All goes well until we reach about 300 platenumbers, then as a result I get only part of the list and a Fatal error:
"Fatal error: Maximum execution time of 60 seconds exceeded in c:\Inetpub\Scripts\printallemutaties.php on line 65" (see below for a code listing)
Before this happens you see that the page is being built up; the scrollbar is getting smaller every few seconds and after a minute it getts really small of a sudden and processing has stopped.
I didn't write the code and it never gave us any problems, but some cardealers have been changing their behavior and have submitted hundreds of cars. These customers now cannot print an entire list of insured cars.
The question: what can I do to keep performance under an acceptable amount of time (without increasing maximum execution time in php.ini) independent of the number of platenumbers that is retrieved (well at least not linear dependent or worse).
This is the actual php-code: (Do not be scared by the dutch variable and function names, it shouldn't matter)
## START ##
<html>
<head>
<?php include("auth.inc");
$polisnummer = $_REQUEST["polisnummer"];
$kenteken = $_REQUEST["kenteken"];
$naam = $_REQUEST["naam"];
?>
<title></title>
<style type="text/css">
TD,P {font : 9pt arial, helvetica, sans-serif;}
P.afdrukdatum { font : 8pt arial, helvetica, sans-serif;}
TD.header {font : bold 9pt arial, helvetica, sans-serif;}
</style>
</head>
<body>
<?php
function print_kenteken_info($link,$kenteken,$polisnummer,$polisid,$naam)
{
// $querystring="select meldcode,soort_voertuig,merk,uitvoering,kilometers,bouwjaar,bouwmaand,catwaarde
// from kenteken where kentekennummer=\"" . $kenteken . "\"";
$querystring="select meldcode,soort_voertuig,merk,uitvoering,kilometers,bouwjaar,bouwmaand,catwaarde
from mutatie where kentekennummer=\"" . $kenteken . "\" and einddatum IS NULL";
$result= mysqli_query($link, $querystring);
$info=mysqli_fetch_array($result);
$huidigjaar=date(Y);
$huidigemaand=date(m);
$leeftijd = ( (($huidigjaar*12)+$huidigemaand) - (($info[bouwjaar]*12)+$info[bouwmaand]) );
print "<table border=0><tr>
<td align=left width=440>
<table border=0>
<tr>
<td>Kenteken:<td><b>$kenteken</b>
<td>Meldcode:<td><b>$info[0]</b><tr>
<td>Soort:<td><b>$info[1]</b>
<td>Merk/Type:<td><b>$info[2] $info[3]</b>
</table>
<td align=right>
<table border=0><tr>
<td>Bouwjaar/maand:<td><b>$info[bouwjaar]/$info[bouwmaand]</b><tr>
<td>Cataloguswaarde:<td><b>$info[catwaarde] €</b></table>
</table><hr>";
# $querystring="select date_format(startdatum,\"%d-%m-%Y\") as datum_van, date_format(startdatum, \"%H:%i\") as tijd_van,
# date_format(einddatum,\"%d-%m-%Y\") as datum_tot, date_format(einddatum, \"%H:%i\") as tijd_tot,
# soort_gebruik,bestuurder,eigen_risico,afgemeld,casco,casco_dekking,ongevallen,schade,rechtsbijstand,kilometers,default_dekking
# from mutatie where kentekennummer=\"$kenteken\" and polis_id=$polisid order by startdatum desc,hour(startdatum) desc ,minute(startdatum) desc, second(startdatum) desc, einddatum desc ,hour(einddatum) desc ,minute(einddatum) desc, second(einddatum) desc";
$querystring="select date_format(startdatum,\"%d-%m-%Y\") as datum_van, date_format(startdatum, \"%H:%i\") as tijd_van,
date_format(einddatum,\"%d-%m-%Y\") as datum_tot, date_format(einddatum, \"%H:%i\") as tijd_tot,
soort_gebruik,bestuurder,eigen_risico,afgemeld,casco,casco_dekking,ongevallen,schade,rechtsbijstand,kilometers,default_dekking
from mutatie where kentekennummer=\"$kenteken\" and polis_id=$polisid order by volgnummer desc";
#print $querystring;
$result2=mysqli_query($link, $querystring);
while ($mut=mysqli_fetch_array($result2))
{
if ($i==0)
{
print "<table border=0>";
if ($j==0)
{
print "<tr><td class=header width=110>Dekking vanaf<td class=header width=110>Dekking tot<td class=header width=90>Gebruik<td class=header width=120>Bestuurder
<td class=header width=120>Dekking<td class=header width=40>Eigen<br> risico<td class=header width=10>OI
<td class=header width=10>SI<td class=header width=10>Rb";
if (strcasecmp($info[kilometers],"yes") == 0)
print "<td class=header width=40>Kilometerstand";
print "<tr><tr>";
$j=1;
}
$i=1;
}
print "<tr><td>$mut[0] $mut[1]";
print "<td>$mut[2] $mut[3]";
print "<td>$mut[soort_gebruik]<td>$mut[bestuurder]<td>";
if ($mut[casco]=="on")
{
if ($leeftijd <= 36)
{
if ($mut[default_dekking]=="yes")
print "WA + Casco";
else
print "WA + Casco";
}
else
if (($leeftijd > 36) and ($leeftijd <= 84))
{
if ($mut[default_dekking]=="yes")
print "WA + Casco";
else
print "WA + Casco beperkt";
}
else
if (($leeftijd > 84) and ($leeftijd <= 240))
{
if ($mut[default_dekking]=="yes")
print "WA + Casco beperkt";
else
print "WA + Casco";
}
else
if (($leeftijd > 84) and ($leeftijd <= 240))
{
if ($mut[default_dekking]=="yes")
print "WA";
else
print "WA";
}
else
print "WA";
}
else
if (!(strcmp($mut[soort_gebruik],"Afgemeld")==0 ))
print "WA";
print "<td>";
if ($mut[eigen_risico]==0)
print "nvt<td>";
else
print "$mut[eigen_risico]<td>";
if ($mut[ongevallen]=="on")
print "J";
else
print "N";
print "<td>";
if ($mut[schade]=="on")
print "J";
else
print "N";
print "<td>";
if ($mut[rechtsbijstand]=="on")
print "J";
else
print "N";
if (strcasecmp($info[kilometers],"yes") == 0)
print "<td>$mut[kilometers]";
}
if ($i)
print "</table><br><br><br>";
}
$link=mysqli_connect($host,$user,$passwd,"automeld") or die ("Database error!");
##
## Haal polidid
##
$querystring="select id from polis where polisnummer=\"" . $polisnummer . "\"";
$result= mysqli_query($link, $querystring) or $stop=1;
if ($stop or !($id=mysqli_fetch_array($result)))
print "<center><b>Geen gegevens gevonden.....</b></center>";
else
{
print "<table width=100%><tr><td align=left><img src=../images/topframe_adres.gif width=130 height=44>
<td align=right><img src=../images/topframe_logo.gif width=80 height=41 border=0></table>";
print "<p class=afdrukdatum> Afdrukdatum: " .date ("d-m-Y / H:i ") . "<p align=center>Alle mutaties</p>";
print "<p style=font:arial>Polisnummer: $polisnummer $naam</p>";
$querystring="select distinct kentekennummer from mutatie where polis_id=$id[0] and verwijderen=\"no\" order by kentekennummer";
$result= mysqli_query($link, $querystring);
while ($kenteken=mysqli_fetch_array($result))
{
print_kenteken_info($link, $kenteken[0],$polisnummer,$id[0],$naam);
}
}
?>
</body>
</html>
## FINISH ##
<?php
set_time_limit(0);
...
?>
Regards,
Richard Quadling.