Regular email reminder

PHP
1

I’ll just ask initial question here first:

I need a email reminder which would check items due maintenance once a week (i.e. every monday) and then create a list and send it over to me.

I was thinking about cron jobs but that’s absolutely empty fields for me and I’ve been advised not to mess about.

So I see a potential use of something similar to THIS

What email should contain:
Items that are due maintenance within 7 days
Items that are overdue

The data I store in DB is:

  • Last maintenance date
  • Maintenance interval

And for such reminder as I understand I will need

Thoughts?

2

By whom? And does that someone have a better idea, because a cron job is the primary tool for that?

2 Likes
3

By people from other forum which I clearly don’t use anymore (and that’s what I’ve been told when described myself not even beginer with CRON jobs). I have never used cron thing and never even had a chance to look at it. As far as I know it’s very related to Linux system programming (or so) and I have no idea about these…

4

Its not really programming. You can think of cron as a program that runs on linux systems and this program will run scheduled tasks that are defined to be run. Basically you run crontab -e command and it will open the cron job listing for your user. Then in there you can add scripts to be run withing time interval.

Example cron file, which will run script called /var/www/html/cron/email.php every day at 7:00 am. Lines starting with # are comments:

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

# Send emails everyday at 7:00 am
0 7 * * * cd /var/www/html/cron/; /usr/bin/php email.php >>/dev/null

Cron manual: http://man7.org/linux/man-pages/man8/cron.8.html
More info: https://en.wikipedia.org/wiki/Cron

If you are not familiar with linux systems and haven’t used cron jobs at all before, this might feel a bit overwhelming at the start. But in the end cron is pretty simple thing.

You probably need to learn to use vi editor on linux to be able to edit the cron file. I don’t know if you can use some other editors too.

3 Likes
5

So from what I see here cron job runs scrip which you describe within one line, fairly simple, then my question would be how would the php email.php file look like? If I’m right to write cron job is very easy part of what I am after. Compiling email.php file is the challenge.

6
  1. You get the items data from db.
  2. You check which items have maintenance within 7 days by comparing dates in php.
  3. You check which items are overdue by comparing dates in php (which you already did in other thread).
  4. You build your email message (subject, recipient(s) and body, maybe headers) based on the items data.
  5. Send email, probably best idea to use some existing library or service to do this.

Based on this start building your script. And ask more when stuck.

1 Like
7

Although that would be very easy to accomplish in SQL as well.

2 Likes
8

And which one should I pick? It might be worth saying that the email reminder may need much more details in the future, so good to keep in mind there must be room for further developments.

This is really looking very complicated and I can probably manage do up to step 4 where I have tiny bit of experience compiling code for email message.

9

True that.

10

Well, you can pass parameters to your script to configure it (e.g. email.php --limit='+7 days')

11

This is looking something I have never seen before. I know there is more than one way to do such job but I prefer most simple and basic way.

I think this would be doing all within file which would be called up by cron job. Am I wrong?

12

if you’re working on the command line, you likely have encountered some commands like ls -a (list all directory contents) or cd .. (move up one directory). That’s exactly the same only that it doesn’t use PHP. The PHP CLI module allows you to use these mechanism as well.

Basically it’s one way to configure a CLI script. And one way that works well with cron.

13

No, that’s correct - cron calls your script to run whenever you configure it to.

Other than that, it’s just PHP code the same as if you ran it in your browser, basically. So you can do all the development, testing and debugging from the browser, then once it is all working, you can make any changes that might be needed to make it work from cron, if there are any.

There’s a good place to start then. Do the code to make it select the appropriate rows and compile the text messages. Once you have that working, you can add the mail support relatively easily.

14

It sounds so easy but I’m stuck already.

Got this line:

$query_rsHardwareAsset = sprintf("SELECT * FROM assets_hardware WHERE asset_hardware_id = %s", GetSQLValueString($colname_rsHardwareAsset, "int"));

I think I would need to use WHERE to pick up certain entries with i.e. date of <7 days comparing with today’s date. But I do not have such column in DB which stores “next maintenance date” - from previous posts you can see it’s all done within certain page (which is called i.e. “Item Details” )

I am now thinking of recording a “nextmaintdate” when completing maintenance steps so the system does not need to calculate each item’s time separately when looking at “Items Detail” page. Would simply comparing one DB entry date with todays date be more efficient way?

15

The things beside ‘basically’: $_SERVER & $_ENV have different values and $argc & $argv only exist in CLI context.

1 Like
16

It would probably make any bit of code that works with the expiry date easier to read, as you’d be just comparing a date against another date, rather than having to do the calculations you needed in your other thread. It is possible to do it within SQL though:

SELECT maint_date + INTERVAL maint_period DAY AS maint_expiry

I think. You’d have to try it but it should be easy to test.

17

If I can ask someone to point mistake here…

Don’t really know how to insert data into two different locations within DB, here’s what I have:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO maintenance_history (
						by_staff,
						date_completed,
						maint_comm,
						maint_id) 
						
						VALUES (%s, %s, %s, %s)",

					   GetSQLValueString($_POST['by_staff'], "text"),
                       GetSQLValueString(date('Y-m-d'), "text"),
					   GetSQLValueString($_POST['maint_comm'], "text"),
					   GetSQLValueString($maintID, "text")
					   ),
	
		
	$insertSQL = sprintf("INSERT INTO assets_hardware (
						nextmaint) 
						
						VALUES (%s)",

					   GetSQLValueString($_POST['nextmaint'], "text"),
					   );

How to get it right, please?

18

You make a separate query for each table. Preferably while in a transaction.

19

Example?

20 
$pdo->exec($sql1);
$pdo->exec($sql2);