Script For Checking Stock Levels Via Cron

Discussion in 'PHP' started by NITRO23456, Sep 9, 2017.

0
  1. #1
    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?
     
    NITRO23456, Sep 9, 2017 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,875
    Likes Received:
    4,547
    Best Answers:
    123
    Trophy Points:
    665
    #2
    first up, your script should always make sense when you run it in the browser - your's won't be.

    I don't quite get where the joins could be in your table (Item Type and Stock Type are the same thing?) but I'd suspect that you could have a single query

    so you'd have

    select min_stock_level, count(stock.id) as counter
    from Stocktypes, Stock
    where Stocktypes.Stock Type = 'Item 1'
    and Stocktypes.Stock Type = Stock.Item Type
    group by Stocktypes.id
    having counter > min_stock_level

    then loop through the results for each stock item and output as plain text on the screen. edit until it's right.

    so long as we're going old school and using mysql_query we might as well have hard joins

    I only just discovered you can have spaces in column names - might work but talk about making life hard for yourself!
     
    sarahk, Sep 9, 2017 IP
    JEET likes this.
  3. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #3
    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", ""); }
    }
     
    JEET, Sep 9, 2017 IP