Email Warning When Certain Amounts Reached.

[roldahayes]

Hi,

 

I am building a PHP stock control / Inventory system and am in need of some advise on the best way to go about it.

 

Currently, the stock levels are displayed using the below code.

 

I am after a solution to Email me when a certain item fall below a set level.

 

I.e. When a product reaches only 5 left on the shelf, the system will email me the details of this product for re ordering.

 

I hope this makes sense and any advise would be great.

 

<?php include_once('../header.php'); ?>


<?php
mysql_query("DELETE FROM $database->product_amount WHERE amount='0'");

if(isset($_GET['product_id'])){ $product_id = safety_filter($_GET['product_id']); }
?>


<table class="dataTable">
<thead>
<tr>
<th width="1"></th>
<th><?php lang('Product Code'); ?></th>
<th><?php lang('Shelf'); ?></th>
<th class="text-right"><?php lang('Amount'); ?></th>
</tr>
</thead>
<tbody>
<?php
$amount_total = 0;
if(isset($_GET['product_id'])){ $query_product_amount = mysql_query("SELECT * FROM $database->product_amount WHERE product_id='$product_id' ORDER BY product_id ASC"); }
else { $query_product_amount = mysql_query("SELECT * FROM $database->product_amount"); }
while($list_product_amount = mysql_fetch_assoc($query_product_amount))
{
$product_amount['id'] = $list_product_amount['id'];
$product_amount['product_id'] = $list_product_amount['product_id'];
$product_amount['shelf'] = $list_product_amount['shelf'];
$product_amount['amount'] = $list_product_amount['amount'];

echo '
<tr>
<td></td>
<td><a href="'.get_url('page').'/product/product.php?product_id='.$product_amount['product_id'].'">'.get_product($product_amount['product_id'], 'code').'</a></td>
<td>'.$product_amount['shelf'].'</td>
<td class="text-right">'.$product_amount['amount'].'</td>
</tr>
';

$amount_total = $amount_total + $product_amount['amount'];
}
?>
</tbody>
<?php if(isset($_GET['product_id'])) : ?>
<tfoot>
<tr>
<th></th>
<th></th>
<th></th>
<th class="text-right"><?php echo $amount_total; ?></th>
</tr>
</tfoot>
<?php endif; ?>
</table>
<?php include_once('../../footer.php'); ?>

[Beeeeney]

Try

 

<?php
send me dat email wen criteria is met, yo
?>

 

Let me know how you get on.

[roldahayes]

Clever.... Really clever......

 

Shame I can't type here what I really think of that.

 

------------------------------------------------------------------------------------------------------

 

I was thinking more along the lines of Table Triggers and Cron Jobs....

[MDCode]

Cron jobs would work good, just make a php script that checks and emails.

[roldahayes]

OK,

 

I would need the script to mail when the level reached 5 items.

 

So my first attempt would be:

 

#!/etc/php5/cgi
<?php
//connect to database
include_once('configuration.php');
include_once('include/connect.php');

$result = mysql_query("SELECT `id`,`product_id`,`amount` FROM `product_amount` WHERE `amount`<=`5.0000`");


if($result->num_rows>=1) {
  $email='mymail@gmail.com';
  $subject = "LOW STOCK WARNING!";
  $message='One or more products are running low:\n\n';
  while($row=$result->fetch_assoc()) {
     $message.="{$row['product_id']}\n";
  }
  if(mail($email, $subject, $message)) {
     //mail successfully sent
  } else {
     //mail unsuccessful
  }
}
?>

 

Is there anything glaringly obvious as to why this isn't working?

[Pikachu2000]

Get rid of the backticks.

[MDCode]

You're using backticks around your number

 

Edit: lol same time posting as pikachu

Edited November 15, 2012 by SocialCloud

[roldahayes]

So it should be like this? :

 

$result = mysql_query("SELECT `id`,`product_id`,`amount` FROM `product_amount` WHERE `amount`<=9.0000");

 

That still dosn't appear to work.

[Pikachu2000]

You aren't checking the success or failure of the query.

[roldahayes]

you mean by putting an Echo at the bottom of the query?

[AyKay47]

If you debug your code correctly, you will not need to ask us why something isn't working, error handling will tell you.

You are treating $result as if it were an object, when in fact its a mysql resource.

 

Edit: to simply answer the question above:

$result = mysql_query() or die(mysql_error())

Edited November 15, 2012 by AyKay47

[roldahayes]

so, something like this?

 

$result = mysql_query("SELECT id, product_id , amount FROM  product_amount WHERE amount<=5.0000"); {
   die('Invalid query: ' . mysql_error());
}

[Beeeeney]

so, something like this?

 

$result = mysql_query("SELECT id, product_id , amount FROM product_amount WHERE amount<=5.0000"); {
die('Invalid query: ' . mysql_error());
}

 

As far as I'm aware, that's one big syntax error.

 

Where your "die" command is concerned, you need to tell it to query the database and if it fails, then use "die" and display a MySQL error.

 

<?php
$result = mysql_query("SELECT id, product_id , amount FROM product_amount WHERE amount<=5.0000") || die("Invalid query:" . mysql_error());
?>

Edited November 16, 2012 by Beeeeney

[roldahayes]

So your saying it should display an error based on what is wrong with the query?

[Beeeeney]

So your saying it should display an error based on what is wrong with the query?

 

That code I just posted will tell you what's wrong if it fails.

[roldahayes]

*edit* error reports are now on!

 

Notice: Trying to get property of non-object in....

That line is:

 

if($result->num_rows>=1) {

Edited November 16, 2012 by roldahayes

[AyKay47]

You are treating $result as if it were an object, when in fact its a mysql resource.

[roldahayes]

Is this any better?

 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

//connect to database
include_once('configuration.php');
include_once('include/connect.php');


$query = "SELECT id, product_id , amount FROM product_amount WHERE amount<=5.0000";

$result = mysql_query($query) or die(mysql_error());



if($result=num_rows>='2'
$email='mymail@gmail.com';
$subject = "LOW STOCK WARNING!";
$message='One or more products are running low:\n\n';
while($row=$result->fetch_assoc()) {
$message="{$row["product_id"]}\n";
}
if(mail($email, $subject, $message)) {
//mail successfully sent
} else {
//mail unsuccessful
}
}
?>

 

 

I'm now getting an unexpected T_variable error near the bottom...

Edited November 16, 2012 by roldahayes

[Beeeeney]

Is this any better?

 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

//connect to database
include_once('configuration.php');
include_once('include/connect.php');


$query = "SELECT id, product_id , amount FROM product_amount WHERE amount<=5.0000";

$result = mysql_query($query) or die(mysql_error());



if($result=num_rows>='2'
$email='mymail@gmail.com';
$subject = "LOW STOCK WARNING!";
$message='One or more products are running low:\n\n';
while($row=$result->fetch_assoc()) {
$message="{$row["product_id"]}\n";
}
if(mail($email, $subject, $message)) {
//mail successfully sent
} else {
//mail unsuccessful
}
}
?>

 

 

I'm now getting an unexpected T_variable error near the bottom...

 

Near the bottom?

Is this any better?

 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

//connect to database
include_once('configuration.php');
include_once('include/connect.php');


$query = "SELECT id, product_id , amount FROM product_amount WHERE amount<=5.0000";

$result = mysql_query($query) or die(mysql_error());



if($result=num_rows>='2'
$email='mymail@gmail.com';
$subject = "LOW STOCK WARNING!";
$message='One or more products are running low:\n\n';
while($row=$result->fetch_assoc()) {
$message="{$row["product_id"]}\n";
}
if(mail($email, $subject, $message)) {
//mail successfully sent
} else {
//mail unsuccessful
}
}
?>

 

 

I'm now getting an unexpected T_variable error near the bottom...

 

Near the bottom? Also you haven't closed a set of parenthesis.

[roldahayes]

Ive cleared up the error by closing the parenthesis,

 

Now i have the error:

 

Notice: Use of undefined constant num_rows - assumed 'num_rows'  on line 17


Fatal error: Call to a member function fetch_assoc() on a non-object     on line 21

 

So I'm guessing that the query is failing in the first place??

[Beeeeney]

Ive cleared up the error by closing the parenthesis,

 

Now i have the error:

 

Notice: Use of undefined constant num_rows - assumed 'num_rows' on line 17


Fatal error: Call to a member function fetch_assoc() on a non-object on line 21

 

So I'm guessing that the query is failing in the first place??

 

Those errors are detailing the EXACT problem. In plain English.

[AyKay47]

num_rows is assumed a constant, you are after mysql_num_rows()

The second error is being triggered because, again, you are using $result as if it is an object, it's not:

 

if(mysql_num_rows($result) >= 2)
{
 $email = 'mymail@gmail.com';
 $subject = "LOW STOCK WARNING!";
 $message = 'One or more products are running low:\r\n';
 while($row = mysql_fetch_assoc($result))
 {
   $message .= $row["product_id"] . "\r\n";
 }
 if(mail($email, $subject, $message))
 {
   //mail successfully sent
 }
 else
 {
   //mail unsuccessful
 }
}

[roldahayes]

Aha! This is all making sense now.

 

There is one thing that I can see is now not going to work with how this is written....

 

The info that is going to be emailed is actually a field in another table... instead of product_id it needs to be product_code (not in this table)

 

can the query get info from the other table? The id field is the same in both.

 

Maybe with LEFT JOIN ?

Edited November 16, 2012 by roldahayes

[AyKay47]

MYSQL Join

[roldahayes]

Thanks for the link...! I'm nearly there now.

 

It now emails correctly but it displays all of the part numbers - no matter what their amount is?

$query = "SELECT product.id,  product.code, product_amount.id, product_amount.amount
FROM product, product_amount
WHERE amount <=5.0000";