Calculate difference in date

[krs1]

Hey there, I am trying to calculate the difference between a date i have in my MySql database (stored as a date field) against todays date... (basically I am trying to figure out if a licence is past its expiry date.)

Currently I have :

$today = date("Y-m-d");
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
$query="SELECT * FROM driver";
$result=mysql_query($query);
$areanum=mysql_numrows($result);
$i=0;

while ($i < $areanum) {
$driverlicence=mysql_result($result,$i,"licence_expire");

If ($driverslicence > $today) {
  Echo '<span id="Expire">Expired!</span>';
}Else {
 Echo $driverlicence;
 }
}
.....

This doesn't seem to work... I believe its because php is trying to compare the math differently... But since my data is stored as a date in a table i dont know how i can seperate day month and year....

[devinemke]

$result = mysql_query('SELECT UNIX_TIMESTAMP(licence_expire) AS licence_expire FROM driver');
$time = time();
while ($row = mysql_fetch_assoc($result))
{
	if ($row['licence_expire'] <= $time) {echo 'expired';}
	else {echo 'not yet expired';}
	echo '<br>';
}

[krs1]

Can i run another query inside a looped while statement?

[devinemke]

yes, but it should be avoided if possible. what would the subquery look like?

[krs1]

Well that would be the sub query....

This is what i have now:

<span>
 <table cellspacing="0" cellpadding="3" border="0" width="780">
    <tr>
        <td>Driver Name</td>
        <td>Licence Expiry</td>
        <td>First Aid Expiry</td>
        <td>H<sub<2></sub>S Expiry</td>
        <td>W.H.M.I.S. Expiry</td>
        <td>P.D.I.C. Expiry</td>
        <td>Edit</td>
        <td>Delete</td>
    </tr>
</span>
<?php
$today = date("Y-m-d");
$tst = true;
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
$query="SELECT * FROM driver ORDER BY name";
$result=mysql_query($query);
$areanum=mysql_numrows($result);
$i=0;
while ($i < $areanum) {
$driverid=mysql_result($result,$i,"id");
$drivername=mysql_result($result,$i,"name");
$driverlicence=mysql_result($result,$i,"licence_expire");
$driverfirstaid=mysql_result($result,$i,"firstaid_expire");
$driverh2s=mysql_result($result,$i,"h2s_expire");
$driverwhmis=mysql_result($result,$i,"whmis_expire");
$driverpdic=mysql_result($result,$i,"pdic_expire");


 Echo ' <tr bgcolor="#'.$tblbgcolr[$tst = !$tst].'">';
 Echo '<td>';
 Echo $drivername;
 Echo '</td>';
 Echo '<td>';
    If ($driverslicence > $today) {
      Echo '<span id="Expire">Expired!</span>';
      Echo $today;
    }Else {
     Echo $driverlicence;
     }
 Echo '</td>';
 Echo '<td>';
 Echo $driverfirstaid;
 Echo '</td>';
 Echo '<td>';
 Echo $driverh2s;
 Echo '</td>';
 Echo '<td>';
 Echo $driverwhmis;
 Echo '</td>';
 Echo '<td>';
 Echo $driverpdic;
 Echo '</td>';
 Echo '<td>';
 Echo '<A href="Admin/EditDriver.php?id=';
 Echo $driverid;
 Echo '"><img src="Graphics/Modify.Gif" border="0"></a>';
 Echo '</td>';
 Echo ' <td>';
 Echo '<A href="Admin/DeleteDriver.php?id=';
 Echo $driverid;
 Echo '"><img src="Graphics/Delete.Gif" border="0"></a>';
 Echo ' </td></tr>';
$i++;
 }
?>
<form method="POST" action="admin/AddDriver.php">
    <tr>
        <td><input type="text" name="NewDriverName" size="20"></td>
        <td><input type="text" name="NewDriverLicence" size="10"></td>
        <td><input type="text" name="NewDriverFirstAid" size="10"></td>
        <td><input type="text" name="NewDriverH2S" size="10"></td>
        <td><input type="text" name="NewDriverWHMIS" size="10"></td>
        <td><input type="text" name="NewDriverPDIC" size="10"></td>
        <td>-</td>
        <td>-</td>
    </tr>
</table>
<input type="submit" value="Add" />
</form>

[krs1]

I'm still lost here... anyone able to help?

[xblue]

krs1,

did you think about doing it in your query, e.g. something like - note all this below is a draft, not tested -
from version >4.1.1. (difference in days)

SELECT whatever, 
DATEDIFF(NOW(), licence_expire) AS driverlicence

or (this would actually do the comparison for you already)

SELECT whatever, 
IF(licence_expire<NOW(), 'Expired!', licence_expire) AS driverlicence

[krs1]

Actually no I never thought of doing it that way, as i never knew i could... But when i tried to implement your code i failed to get any results from the server.

[xblue]

Hi krs1,

remote debugging of database problems is never easy :S

Could you post the query you put together?