Avatar of tromm
tromm
 asked on

How to speed up php-script

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] &#8364;</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: &nbsp;&nbsp;$polisnummer &nbsp;&nbsp;&nbsp;&nbsp; $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

Avatar of undefined
Last Comment
Richard Quadling

8/22/2022 - Mon
Richard Quadling

Hi iextensive,


<?php
set_time_limit(0);
...
?>

Regards,

Richard Quadling.
Richard Quadling

Hi tromm,

Sorry about calling you iextensive!

Each PHP installation has a config file which controls, among other things, how long a PHP script can run for.

The function set_time_limit() allows you to change this in your script.

Setting it to 0 means the script can run forever, so watch out for that. Make sure your script can finish!!!!


Regards,

Richard.
tromm

ASKER
Ok thanks, now I have at least a way to generate the full list. But how can I make it go faster? putting more code in sql? or change the way HTML is generated? or anything else that speeds up php-processing?

regards,

Martijn Tromm
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Richard Quadling

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.