Running a hook Cron Job

[utony]

Ok AG Fam, looking for some help.

I have a table, with a data field called expiration. When the row is 90 days from the expiration date, I want the system to send an email to let the party know the record is 90 days from exp.

I currenlty have a sendmail for all my hooks, but with this one I am really not sure where to put this send mail code.

I would write a cron job to run the script, but would I leave the code in the hooks file for this table? I would not be inserting or editing the file to trigger the hook so I am a bit confused how I would do this. Any help would be greatly apperiated.

Tony

[ppfoong]

You can create a new MySQL user with read-only access to the necessary tables only.

Then, write an independent script and access MySQL with that user. Do some basic security checking in your script, such as only allow to execute from CLI and not from web browser. You can put the script outside web accessible folder too. Use chmod to restrict who can read and execute it.

Lastly, manually run the script to test it. Make sure you only send the email to your own account instead of real emails to avoid annoyance.

Once tested OK, create a cron job to run the script daily.

To summarize, you can make use of AppGini MySQL data, and own custom script to perform the task.

[ppfoong]

By the way, in your code, you can restrict your script to only run once per day. If run the 2nd time within 24 hour, need to pass a parameter to the script. You can pass parameter using HTTP GET.

Then you can create a new MySQL table for logging and grant write access to it. Use it to log down emails sent out and record the time. With this, you can know the last time the script was executed.

[a.gneady]

In this scenario, since you don’t have an insert/edit action to trigger code in the usual table hooks (like tablename_after_insert() or tablename_after_update()), you’ll need a separate script that runs periodically and checks for records that are 90 days away from expiration. Here’s the typical approach:

1. Create a new PHP script inside the hooks folder, for example:hooks/expiry_notifier.php
2. Write the logic to query your table, find the rows that are due to expire in exactly 90 days
   (or within a certain range), and send the email. For instance:
   

   Code: Select all

   <?php
   // hooks/expiry_notifier.php
   
   include(__DIR__ . '/../db.php');
   
   // today's date
   $today = date('Y-m-d');
   // target date = today + 90 days
   $targetDate = date('Y-m-d', strtotime("+90 days"));
   
   // query to find records that expire on $targetDate
   $sql = "SELECT * FROM `tablename` WHERE `expiration` = '{$targetDate}'";
   $res = sql($sql, $eo);
   
   // loop over results, sending email
   while($row = db_fetch_assoc($res)) {
       // your email-sending logic
       sendmail([
         'to' => $row['email'],  // I'm assuming the recipient address is stored in the 'email' field .. adjust if needed
         'name' => $row['name'],  // Same assumption as above        
         'subject' => 'Record expiring in 90 days',        
         'message' => "The following record is expiring in 90 days:\n" . 
                      application_url('tablename_view.php') . '?SelectedID=' . urlencode($row['id']),
       ]);
   }
   ?>
   

3. Set up a cron job on your server to run this expiry_notifier.php script once a day.
   For example:

   Code: Select all

   0 2 * * * /usr/bin/php /var/www/html/myapp/hooks/expiry_notifier.php
   

4. Test the script by adjusting the dates in your table or in the script
   (e.g., using +1 day instead of +90 days) to ensure it sends email as intended.

This way, your logic remains inside the hooks folder but does not depend on any insert or update action in AppGini. You simply run your notification logic on a schedule via cron. This is a common pattern when you need timed notifications rather than event-based (insert/update) hooks.

[utony]

Ahmed, this is awesome! Thank you, I am working on this now. Just checking one thing... If my expiration is 1-APR and I want the alert to fire on 1-JAN, 90 from the expiration, would the +90, be -90 in the code provided?

Tony

[a.gneady]

You're welcome! The code above looks for records expiring in 90 days from today. "+90" in the above code means 90 days ahead of today.

[utony]

Thank you Sir!