expiry date alert

[sagisgirl]

Hi,
I am developing a system that need to monitor machines' licence, so i have a field 'movedate' and 'outdate'
every machines can only been kept for 2 years for most.
and if the machine didn't go out for almost 2 years, an alert will come out to remind the tenant.
I want the alert will come out about 2 weeks before the expire date.

But Im not sure how to do it..can someone guide me?

[diafol]

Assuming you access the page every day, you can access those about to expire (2 weeks) and expired with SQL

SELECT * FROM table WHERE DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14   [for about to expire]*

SELECT * FROM table WHERE CURDATE() > renewdate [for expired]

*That would show the records every day for the 14 days until expiry. I'd have an renew_status field or something to say what action had been taken (e.g. email, letter etc), so once a written letter had been sent, the field would read say '4' and then they wouldn't show up any longer in the list, e.g.

SELECT * FROM table WHERE renew_status < 4 AND DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14

I don't udnerstand why you have 2 dates for checking movedate/outdate - or is renewdate = outdate and movedate = startdate?

[sagisgirl]

I want to do it like this : there is a list of the machines' licence in a table:

machine  |  received date  |  expiry date  | consignee  |  notes
------------------------------------------------------
T15D001  |  2010-06-24     |  2013-06-24   | Haliburton | ALERT   
T15D089  |  2010-09-11     |  2013-09-11   | KSB        |  

the received date == moveDt
the lifetime for each machines is 2 years.
this page is for the admin to monitor.

[diafol]

So? does my idea work for you? Use 'expiry_date' instead of 'renewdate' in my code.

However...

the lifetime for each machines is 2 years.
2010-06-24 to 2013-06-24

Isn't that 3 years?

[sagisgirl]

sorry for the late reply.So, i just need write this code :

    SELECT * FROM table WHERE DATEDIFF(renewdate, CURDATE()) BETWEEN 0 AND 14 [for about to expire]*
    SELECT * FROM table WHERE CURDATE() > renewdate [for expired]

I want to create a popup or (same as my table example:the message will appear in the last column) which will appear every time they open the page, and this file will have a code to fetch the records from database and compare the date in the database with the current date and if it matched then alert a message.

for the date example, sorry, it supposed to be only 2 years.

[diafol]

<?php
$r = mysql_query($q);
$output = '';
if(mysql_num_rows($r))
{

    while($d = mysql_fetch_assoc($r))
    {
        if($d['diff'] < 0)
        {
            $notes = "EXPIRED";
        }elseif($d['diff'] <= 14){
            $notes = "ALERT";
        }else{
            $notes = '';
        }

        $output .= "<tr><td>{$d['machine']}</td><td>{$d['received_date']}</td><td>{$d['expiry_date']}</td><td>{$d['consignee']}</td><td>$notes</td></tr>";
    }

}
?>

<table>
    <thead>
        <tr>
            <th>Machine</th>
            <th>Received Date</th>
            <th>Expiry Date</th>
            <th>Consignee</th>
            <th>Notes</th>
        </tr>
    </thead>
    <tbody>
        <?php echo $output;?>
    </tbody>
</table>

Supply the SQL you require for the $q variable. You could even do the conditional inside the SQL so that you could ORDER BY the result of the conditional, but that's outside the scope of this at the moment.

[sagisgirl]

Hi...I need to change some database..
so...Is it possible if i didn't have 'expiry_date' column in my database?
I want PHP to calculate the expiry date, from the time the goods is received(received_date)?

[sarathnair]

how to create php mysql notification like facebook please help meee...?

[diafol]

Thanks for resurrecting this dead thread. Start your own and supply more info. What have you tried? Show any code you have written so far. If you.ve done nothing and just want somebody to do it for you then state how much you.re prepared to pay.

[gebretsadik]

can we use the two query as single or what

[Dani]

Sorry, I'm not understanding your question, gebretsadik.

What, specifically, are you looking to do? The SELECT queries that diafol provided just explain how you can look for dates within table columns that contain datestamps or timestamps.