Send email alert when mySQL field equals a value

Question

I have an inventory mySQL database. I need an email sent to a few people alerting them when the "qty" equals or is less than the number in the "min" field.

Does anyone know how to create a script to have email sent IF the "qty" equals or is less than "min"? Can this be done on the server-side?

Erik

DATABASE NAME: _hero 

TABLE NAME:  CARTONS_CURRENT
+--------------+--------------+--------+--------+-------------------+---------+
| Column       |  Type        |  Null  |  Key   |  Default          |  Extra  |
+--------------+--------------+--------+--------+-------------------+---------+
| orig_time    | timestamp    |  No    |        | CURRENT_TIMESTAMP |         |
| type         | text         |  No    |        |                   |         |
| part_no      | varchar(20)  |  No    |  Prim  |                   |         |
| description  | varchar(75)  |  No    |        |                   |         |
| count        | varchar(2)   |  No    |        |                   |         |
| size         | varchar(30)  |  No    |        |                   |         |
| min          | int(7)       |  No    |        |                   |         |
| max          | int(7)       |  No    |        |                   |         |
| qty          | int(8)       |  No    |        |                   |         |
+--------------+--------------+--------+--------+-------------------+---------+

Solution

I hope your MySQL/PHP is on a Linux host... if so, this is my suggestion:

First, create a php script that will check the database, and if condition is met send an email. Just remember that this script has to be executable by the server in my method, as we will later create a cronjob to run the script

Change value of /usr/bin/php to the path of your php (use whereis php or which php to find this)

#!/usr/bin/php
<?php
//connect to database
$dbconnect=new mysqli('localhost','username','password','_hero');
$result=$dbconnect->query("SELECT `qty`,`min`,`part_no` FROM `CARTONS_CURRENT` WHERE `qty`<=`min`");
//if there are any records matching this query we send an email listing each one using 'part_no' as the identifier in this case
if($result->num_rows>=1) {
   $email='[email protected]';
   $subject = "PRODUCTS OUT OF STOCK";
   $message='One or more products are out of stock:\n\n';
   while($row=$result->fetch_assoc()) {
      $message.="{$row['part_no']}\n";
   }
   if(mail($email, $subject, $message)) {
      //mail successfully sent
   } else {
      //mail unsuccessful
   }
}
?>

save/exit, and make the file executable (chmod 700 or 777)

create a cron entry to execute this script every 5 minutes:

crontab -e

*/5   *   *   *   * /path/to/phpscript

If your MySQL is not on a Linux host and/or you do not have executable PHP set up, you can still use the above PHP script to do the job, but you either have to manually point a browser to the script location, or figure out another way to execute that script.

Also, please check my code religiously as I wrote it on the fly without any syntax editor..

Comments

[Erik]

The cron is foreign to me. Not sure where the cron goes.

[Ben Ashton]

if you type crontab -e as root, it should open your cron table in an editor. Append to the end of that file what I quoted above, replacing only the /path/to/phpscript to the actual path of your script. Save/exit the file and crond will now execute that script every 5 minutes. If you do not have root access or do not have cron, I can also show you how to turn it into a daemon if you have shell access.

[Erik]

Is there way i can email you direct if I have a question. I'm starting on this in about 2 hours.

[Ben Ashton]

if you must! ;), I don't want to advertise my email on this site for spam reasons, my email address can be found here: www.toscanagrill.ca/mymail.txt

[MSD]

I am using XAMPP, should i use this: #!../xamppfiles/etc/php.ini is this correct?