Script For Checking Stock Levels Via Cron

Hi all

I have the following script

<?php
$recipient = "email@example.com";

$threshold = mysql_query("SELECT `Min Stock Level` FROM Stocktypes WHERE `Department`='Department 1' AND `Stock Type`='Item 1';");

$result = mysql_query("SELECT * FROM Stock WHERE `Department`='Department 1' AND `Item Type`='Item 1';");

if ($result) {

  $row = mysql_fetch_assoc($result);

  $inventory = mysql_num_rows($result);

  if ($inventory <= $threshold) {

  $msg = "Inventory for product has fallen beneath threshold. $inventory remaining.";

  mail($recipient, "Inventory check below threshold", $msg);

  }

}

else {

  $msg = "An error occurred while checking inventory: " . mysql_error();

  mail($recipient, "Inventory check error", $msg);

}

?>

Code (markup):

In table Stocktypes I have a field called `Min Stock Level` which defines my minimum levels for the each item in field `Stock Type` to be held by my shop.

I would like to count the number of rows in another table called Stock for the `Item Type` (which is the same data as in `Stock Type`). If the number of rows for `Item Type` is less than `Min Stock Level` I would like it to send an email alert.

How do I do this dynamically for all Item Types with one script? Any suggestions on script?

 

You can use a query like this:

$result= mysql_query("
select s.stock_name, s.stock_type, s.min_stock_level as minLevel, c.item_type as counter
from stocktypes as s
left join stock as c on s.stock_type = c.item_type
group by s.stock_type
having counter <= minLevel
");

while($row=$result->fetch_array(MYSQLI_ASSOC)){
$stock_name= $row["stock_name"];
mail( $to, "$Stock_name at threshhold", "" );
}

Make sure that item_type and stock_type fields are both following indexes otherwise this will really slow things down for your server...
I used made up names for your field names. Replace them accordingly please.
If this query looks complicated, then you can do a loop like this:

$result= mysql_query("select stock_name, min_stock_level, stock_type from stockstypes");
while($row=$result->fetch_array(MYSQLI_ASSOC)){
$stock_type=$row["stock_type"];
$min= $row["min_stock_level"];

$c= mysql_query(" select count(1) as c from stock where item_type='$stock_type' ");
$c= $c["c"];
if($c<=$min){ mail($to, "$stock_type at limit", ""); }
}