Updating records

raouleduke

I have this query to figure out the miles per day for each VIN of a car, can anyone see a problem in this, it is not updating all the fields in the database only about half of them, there are 33000 records in the database. Is it just taking too long or what. It give the right values but does not update all the records in the database.

mysql_connect("","",""); 
mysql_select_db(""); 
$query = "SELECT MIN(CLOSEDATE), MAX(CLOSEDATE), VIN, CUSTOMERNAME, MIN(MILEAGE), MAX(MILEAGE)  FROM LUTHERSELLANDRECOMMEND GROUP BY VIN";
$result = mysql_query($query);
echo "<table>";
while ($row = mysql_fetch_array($result)) {
	$date1 = $row["MIN(CLOSEDATE)"]; 
	$date2 = $row["MAX(CLOSEDATE)"];

$time1 = strtotime($date1); 
$time2 = strtotime($date2); 
$time = @round($time2-$time1,0); 

$datedifference = $time/60/60/24;

$mileage1 = $row["MIN(MILEAGE)"]; 
$mileage2 = $row["MAX(MILEAGE)"];

$mileagedifference = @round($mileage2-$mileage1,0); 

$milesperday = @round($mileagedifference/$datedifference,0);

$vin = $row["VIN"];

print"
<tr>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["MIN(CLOSEDATE)"]}</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["MAX(CLOSEDATE)"]}</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>$vin</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["CUSTOMERNAME"]}</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>$datedifference</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["MIN(MILEAGE)"]}</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["MAX(MILEAGE)"]}</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>$mileagedifference</font></td>
<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>$milesperday</font></td>
</tr>";

if($milesperday > '0'){
$query1 = "UPDATE LUTHERSELLANDRECOMMEND SET miles_per_day ='$milesperday'  WHERE VIN='$vin'";
}else{
$query1 = "UPDATE LUTHERSELLANDRECOMMEND SET miles_per_day ='33'  WHERE VIN='$vin'";
}
print $query1."<br />"; 
$result1 = mysql_query($query1) or die(mysql_error());
}
echo "</table>";
?>

Thanks in advance.

Gary_King

Code is generally really messy, try cleaning it up first and it should be easier to solve things 🙂

Also, I would suggest replacing

<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>{$row["MIN(CLOSEDATE)"]}</font></td>

with

<td><font color='#000066' size='2' face='Verdana, Arial, Helvetica, sans-serif'>" . $row["MIN(CLOSEDATE)"] . "</font></td>

and the same with the ones below it.

Shrike

It's possible that the script is exceeding the maximum execution time set down in php.ini.

ini_set("MAX_EXECUTION_TIME", "time in seconds");

Weedpacket

I have some worries about the database structure, but I don't see anything off-hand that will stop processing (except, as Shrike mentions, the possibility that it's taking too long and timing out - that might also be a db design issue). As suggested, the code could be tidied up a bit, and in fact the HTML can do with a makeover as well (which has the added bonus of making the PHP easier to read).

<?php
    mysql_connect("","",""); 
    mysql_select_db(""); 
    $query = "select
      	min(CLOSEDATE) as DATE1,
         max(CLOSEDATE) as DATE2,
         VIN, CUSTOMERNAME,
         min(MILEAGE) as MILEAGE1,
         max(MILEAGE) as MILEAGE2
       from LUTHERSELLANDRECOMMEND
       group by VIN";
    $result = mysql_query($query);
    echo '<table style="font-family:verdana,arial,helvetica,sans-serif;font-size:medium;color:#000066">';
    while ($row = mysql_fetch_array($result)) {
    	 $date1 = $row["DATE1"]; 
        $date2 = $row["DATE2"];

    $time1 = strtotime($date1); 
    $time2 = strtotime($date2); 
    $time = @round($time2-$time1,0); 

    $datedifference = $time/60/60/24;

    $mileage1 = $row["MILEAGE1"]; 
    $mileage2 = $row["MILEAGE2"];

    $mileagedifference = @round($mileage2-$mileage1,0); 

    $milesperday = @round($mileagedifference/$datedifference,0);

    $vin = $row["VIN"];

    ?>
    <tr>
    <td><?php echo $date1?></td>
    <td><?php echo $date2?></td>
    <td><?php echo $vin?></td>
    <td><?php echo {$row["CUSTOMERNAME"]}?></td>
    <td><?php echo $datedifference?></td>
    <td><?php echo $mileage1?></td>
    <td><?php echo $mileage2?></td>
    <td><?php echo $mileagedifference?></td>
    <td><?php echo $milesperday?></td>
    <?php

    if($milesperday<=0)
    	$milesperday = 33;

    $query1 = "UPDATE LUTHERSELLANDRECOMMEND SET miles_per_day=$milesperday  WHERE VIN='$vin'";
    print '<td>'.$query1."</td>"; 
    $result1 = mysql_query($query1) or die(mysql_error());
    echo '</tr>';
    }
echo "</table>";
?>

raouleduke

It looks like it is a timeout issue, I can put a limit on it and it works just fine, even without the limit it works fine, but does not complete. My DB Structure looks like this.

CREATE TABLE `LUTHERSELLANDRECOMMEND` (
  `ADDRESS` varchar(255) default NULL,
  `CLOSEDATE` date default NULL,
  `CUSTOMERNAME` varchar(255) default NULL,
  `CUSTOMERNAME2` varchar(255) default NULL,
  `CUSTNO` varchar(255) default NULL,
  `MAKE` varchar(255) default NULL,
  `CITY` varchar(255) default NULL,
  `MILEAGE` int(11) default NULL,
  `MODEL` varchar(255) default NULL,
  `RO` int(11) default NULL,
  `VIN` varchar(255) default NULL,
  `STATE` varchar(255) default NULL,
  `STOCK` varchar(255) default NULL,
  `WARREXPDATE` date default NULL,
  `ZIP` varchar(255) default NULL,
  `SANO` varchar(255) default NULL,
  `OPCODE` varchar(255) default NULL,
  `OPDESC` varchar(255) default NULL,
  `HOMEPHONE` varchar(255) default NULL,
  `PAY` varchar(255) default NULL,
  `WORKPHONE` varchar(255) default NULL,
  `TECH` varchar(255) default NULL,
  `YEAR` int(11) default NULL,
  `ACTIVE` tinyint(1) NOT NULL default '0',
  `miles_per_day` int(6) NOT NULL default '0',
  `projected_mileage` int(8) NOT NULL default '0',
  `next_service_day` date NOT NULL default '0000-00-00',
  KEY `VIN` (`VIN`)
) TYPE=MyISAM;

What do you think is the fix for this, I added this to the top of the script, but the mysql connect timeout is set to 60, does this override the mysql connect timeout .

ini_set("MAX_EXECUTION_TIME", "10800");

Thanks in advance for this, I have never had this problem, but I am trying to make alot of calculations.

Weedpacket

The fact that you're going to need to update multiple records with duplicate information (all records for a given VIN need to be assigned the same speed) suggests to me that your database schema is in need of normalisation (possible opportunity of data corruption: different speeds listed for the same VIN). I suspect too that if it were better-designed, the entire calculation/update could probably be done with one or two queries instead of 1+(number of records returned by first query).