Optimizing Database Update

[Ryukotsusei]

I have a website which contains three database tables. The website is for virtual pets that grow up by having people click on them and view them, and the databases keep track of what stage they are in. One database is for "ER" pets that will die in 4 days or less if they don't receive enough views to mature. The next one is for "eggs" (the first stage, but not yet "ER"), and the second one is for "hatchlings" (the final stage before becoming fully grown).

 

I also have a php script that is set up to run every 5 minutes through a cron job. However, the script either simply is not running, or is timing out before it can finish. I think it's more likely the second option.

 

The cron job is set up as follows:

 

*/5 * * * *  php -f /home/a******/public_html/cron.php (Full address edited)

 

Whenever the script runs, I want it to comb through each of the tables to keep them cleaned up. Considering the nature of the site, this needs to be done as often as possible. It's not something that can sit there for 24 hours before running again. My site detects the state of the pets through an external site's API which I have been given permission to access.

 

Anything that has 4 days or less left to live, whether it's an "egg" or "hatchling", I want to end up in the NICU table. Any eggs that have hatched should be moved to the Hatchlings table, and anything that has grown up, died, or is hidden should be removed entirely.

 

Unfortunately it's hard to test whether the cron script is actually running every 5 minutes like it's supposed to. I'm assuming it is, which leaves the problem of it timing out. So I guess my real question is what can I do to optimize my code to help prevent this from happening? I'm very new to PHP, so even though I can usually get the stuff working, it's not always very fast or efficient. Right now they only have a couple hundred entries, but at least two of the tables could potentially have thousands, so I really need the script to be as fast and efficient as possible.

 

<?php

$username="*******";
     $password="*******";
     $database="*********";
     $host="*********";
     mysql_connect("$host", "$username", "$password") or die(mysql_error());
     mysql_select_db("$database") or die(mysql_error());

     function update($type)
     {
       $query="SELECT * FROM $type";
       $result=mysql_query($query);
       $num=mysql_numrows($result);

       $sql = mysql_query("SELECT Code FROM $type LIMIT $num");
       while($object = mysql_fetch_object($sql))
       {
       $codevar = $object->Code;

       $data = unserialize(file_get_contents("http://dragcave.net/**********/$codevar"));

           $data = array_pop($data['dragons']);
           $code=$data['id'];
           $egg=$data['hatch'];
           $hidden=$data['start'];
           $views=$data['views'];
           $unique=$data['unique'];
           $clicks=$data['clicks'];
           $hours=$data['hoursleft'];

                 $query3 = "UPDATE NICU SET Hoursleft='$hours' WHERE Code='$code'";
                 $query4 = "UPDATE Eggs SET Hoursleft='$hours' WHERE Code='$code'";
                 $query5 = "UPDATE Hatchlings SET Hoursleft='$hours' WHERE Code='$code'";
                 mysql_query($query3);
                 mysql_query($query4);
                 mysql_query($query5);
                 
                 $querynull = "DELETE FROM NICU WHERE Code='\0\0\0\0'";
                 mysql_query($querynull);

                 if($hidden == "0") //if hidden
                 {
                   $query = "DELETE FROM NICU WHERE Code='$code'";
                   $query1 = "DELETE FROM Eggs WHERE Code='$code'";
                   $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
                   mysql_query($query);
                   mysql_query($query1);
                   mysql_query($query2);
                 }
	elseif($hours == -2) //if dead
                {
                  $query = "DELETE FROM NICU WHERE Code='$code'";
                   $query1 = "DELETE FROM Eggs WHERE Code='$code'";
                   $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
                   mysql_query($query);
                   mysql_query($query1);
                   mysql_query($query2);
                }
	elseif($hours == -1) //if adult or frozen
                {
                  $query = "DELETE FROM NICU WHERE Code='$code'";
                   $query1 = "DELETE FROM Eggs WHERE Code='$code'";
                   $query2 = "DELETE FROM Hatchlings WHERE Code='$code'";
                   mysql_query($query);
                   mysql_query($query1);
                   mysql_query($query2);
                }

                  if($egg == "0" && $hours > 96) //if egg
                  {
                  $query = "INSERT INTO Eggs VALUES ('','$code','$hours','$views','$unique','$clicks')";
                  mysql_query($query);
                  $status="$code is in the hatchery.";
                  }
	elseif($egg != "0" && $hours > 96) //if hatchling
                {
                  $query1 = "DELETE FROM Eggs WHERE Code='$code'";
                  mysql_query($query1);
                  $query = "INSERT INTO Hatchlings VALUES ('','$code','$hours','$views','$unique','$clicks')";
                  mysql_query($query);
                  $status="$code is in the nursery.";
                  }
                  
                  if($hours > 96) //if not ER
                  {
                    $query = "DELETE FROM NICU WHERE Code='$code'";
                    mysql_query($query);
                  }
	elseif($hours <= 96 && $hours != -2 && $hours != -1) //if ER
                {
                  $query = "INSERT INTO NICU VALUES ('','$code','$hours','$views','$unique','$clicks')";
                  mysql_query($query);
                  $status="$code is in the ER.";
                  }
  }
     }
     
     update(NICU);
     update(Eggs);
     update(Hatchlings);
  ?>

 

I'm not asking anyone to rewrite my code, but if I could just get some ideas on what I can do to move things along faster, that would be a great help. (I have starred out any potentially sensitive information).

[waynew]

Shouldn't it be:

 

update('NICU');
update('Eggs');
update('Hatchlings');

[Ryukotsusei]

I made the change and ran cron.php directly. I don't know yet if it fixed anything, but I'll do some testing to see if that's really all it was that was preventing the script from performing.