Need help with time

[topflight]

Hello so I am creating some what of a virtual airline script. And I would like a script that shows pilots who haven't done a flight within 30 days of their last flight and 20 days within their last flight.

 

I have a table called flights and everytime a pilot submit a flight report it insert the pilot name,login etc.. and the date of the flight.

 

I also have another table called members with just the pilot information in it passwords,etc... and last flight.

 

The last flight field is a date and so is date of flight. I need help create a script that shows all the pilots who haven't flown a flight 20 and 30 days within their last flight. And maybe 20 could be yellow and 30 or more can be red.

 

Please give me some examples or help on how I can start this. Thanks in advanced.

[JD*]

You can do it either in the mysql or in PHP.

 

If you want to do it in mysql, you can make your selects with the NOW() variable. Subtract date from NOW() and if it's greater than 30 or greater than 20 and less than 30.

 

If you do it in PHP, you can use the date() function to do something similar, just have a for loop for each row and do the check.

[topflight]

May I please have an example, as I am kind of confuse I want to see if they haven't done a flight 20 or 30 days or more from their last flight.

[Ken2k7]

If the type of the column is a valid date, then you can just query it with SQL.

 

Here's a link to some functions you can use. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[topflight]

That link you provided is doesn't work. and the date column are entered like mm.dd.yyyy. Is it possible if someone can write me a quick example?

[Ken2k7]

Sorry, there was a [dot] at the end. I edited. That's not any valid date format I'm familiar with. Is the column of type date?

[JD*]

Topflight, it would be best if you could convert your dates into the standard yyyy-mm-dd. If not, you could always get the db result, break it into an array, then use that to compare:

 

$result = mysql_result("SELECT * FROM table ORDER BY date") or die(mysql_error());
for($i=0; $i < mysql_num_rows($result); $i++)
{
    $date = explode('.', mysql_result($result, $i, "date"));
    $date = $date[2].'-'.$date[1].'-'.$date[0];
    if(date('Y-m-d') - $date > 30)
    {
        echo 'Last flight was greater than 30 days';
    } elseif(date('Y-m-d')-$date > 20) { 
        echo 'Last flight was greater than 20 days, but less than 30';
    } else {
        echo 'Last flight was less than 20 days ago';
    }
    echo '<br /><br />';
}

 

I didn't test the above code, so your mileage may vary, but that would be the general idea.