Hello,
I am trying to send an email notification to a specific email address. I have a MySQL database in which I hold a product table. Product ID, ProductDescription and ProductQuantity.
When the Quantity reaches "0" I want to send an automatic email saying that ProductID is out of stock.
Do you have any idea of how I can write that in php?
Thank you
Do you already have a an email function?
No, I have no idea how to implement that. I can send email in PHP but when it comes to sending email when a table value is zero I have no idea.
I guess that MYSQL statement will be something like
"Select * from Product where ProductQuantity =0"
And then I want to email email@gmail.com with the name of the products that are 0 is quantity.
But I am not sure if this is the right way to implement that. I am mainly devloping in JAVA, it is quite different I guess.
I can send email in PHP
So the answer is Yes.
I presume there is a PHP function you call to remove an item from the database. After this function is run, you should simply run the function that returns the product quantity of the item and then send a PHP email if quantity is less than 1.
Pseudo code (PHP):
decrementProduct($productId);
$product = getProductDetails($productId);
//assumption that returned $product is an array
if($product['quantity']<1) {
sendNoStockEmail($product);
}
Normally, the change in product quantity will be triggered by an action. All you would need to do is do a check whenever someone changes the quantity, if it ever goes to zero fire off your sendmail function.
In that way you would NOT need to continuously poll that field in that table.
Isn't that what I said?
^^ Yes it is.
I apologise I just skimmed the threads today.
I've done that too. :-)
Thanks paulkd for the info. I get the idea but I am a bit confused about that function "getProductDetails($productId);"
So I guess that I will use Select * from Product_tbl"?
I get the idea of getting the product details but how can I do that?
I have started this, can you tell me if I am on the right way?
<?php
$username="root"; // Mysql username
$password="root"; // Mysql password
$db_name="Stock"; // Database name
$host = "somehost";
$inputProductQ = $_POST["ProductQuantity"]; //GET Quantity FROM $_POST SERVER GLOBAL
mysql_connect($host, $username, $password) or die("Failed to connect to MySQL");
mysql_select_db($db_name); //SELECT THE CORRECT DATABASE
if($inputProductQ != "") // IF not null
{
$query = "SELECT * FROM Products";
echo inputProductQ;
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$productQ = $row['ProductQuantity'];
if ($productQ == $inputProductQ)
$quantityP = 1;
}
if($quantityP == 0)
{
echo ""
}
else
{
SendEmail function goes here
}
}
else
{
}
?>
As iamthwee and myself mentioned - our idea was to send an email at the time that a particular product's stock level becomes zero.
Your code appears to want to check all products' stock levels on demand and send an email.
So forget code... let's think process. Do you simply want a function that can be run anytime that checks the stock level of all products and sends an email reporting which products are at zero?
Yes that's right.I want a function that that checks the stock level of all products and sends an email reporting which products are at zero. I do not want to decrease the stock level using PHP.
...and I presume you want a single email detailing the products, not one email per product?
yes
Should I do it a different way? (other than the pseudo code you suggested?)
I also thought of using MySQL triggers. What do you rekon?
Anyone?
I'd run a cron job one a day (possibly at a time when the server is at its quietest).
This just checks for all the zero-value records and emails the lot in one post.
If the email is sent without error, the table should be ammended to update a status field so that the same data isn't sent on consecutive days. The status field would be updated on restock.
status field values example:
0: ignore (equivalent to email sent)
1: active (default state - no email sent)
This field is only considered when stock amount = 0
Just off top of my head.
You should not care for products which have stock.
Database users who are used for such tasks should not have extra permissions.
Passing and parsing agruments for this case is not a requirement.
Unless your running your cron by calling something like * * * * * curl -L http://example.com/cron.php?quantity=1 - which is a very bad idea.
Use * * * * * php (or /usr/bin/php or else) /path/to/php/script/file.php
<?php
/**
* User permissions of database - SELECT
*/
// Notify user
$email="stock.manager@example.com";
// Database parameters
$host = 'localhost';
$port = '3306';
$db = 'database';
$user = 'username';
$pass = 'password';
// Database connection
$dsn = 'mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port;
$db = new PDO($dsn, $user, $pass);
// Query selecting products out of stock
$query = "SELECT id, name FROM products WHERE stock = 0";
// Execution
foreach ($db->query($query) as $row) {
// Safe name for 70 char/line limit
$name = (strlen($row['name']) > 40) ? (substr($row['name'], 0, 10) . '...') : $row['name'];
// Prepare message data
$subject = 'Out of stock - ' . $row['name'];
$body = 'Product "' . $name . '" is out of stock.' . "\r\n";
$body .= 'Manage from http://example.com/admin/store/product.php?id=' . $row['id'] . "\r\n";
// Send email (php needs to be configured with smtp)
// or even better use a different mailer (PEAR::Mail, PEAR::Mail_Queue)
// see http://php.net/manual/en/function.mail.php
mail($email, $subject, $body);
}
You said you already have an email function - you can simply feed $message from the code below into your email function.
<?php
$username = "root"; // Mysql username
$password = "root"; // Mysql password
$db_name = "Stock"; // Database name
$host = "somehost";
mysql_connect($host, $username, $password) or die("Failed to connect to MySQL");
mysql_select_db($db_name); //SELECT THE CORRECT DATABASE
$rs = mysql_query("select ProductID, ProductDescription from Products where ProductQuantity < 1");
if(mysql_num_rows($rs)>0) {
$message = '<p>The following products are out of stock</p><ul>';
while($row = mysql_fetch_assoc($rs)) {
$message .= '<li>'.$row['ProductID'].' '.$row['ProductDescription'].'</li>';
}
$message .= '</ul>';
echo $message;
//email your $message
} else {
//email a message that all products have stock
}
?>
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.