Each product we store in a database,
and we have an online inventory program.
Code: Select all
CREATE TABLE products (
name varchar(255) NOT NULL default '',
stock int(11) NOT NULL default '0',
PRIMARY KEY (name)
)
INSERT INTO products VALUES ('milkshake', 100);
INSERT INTO products VALUES ('coca-cola', 100);
INSERT INTO products VALUES ('fanta', 100);products. And pages to correct these transfers.
This is the page the employees use to register a sale (outgoing product).
Code: Select all
<?php
// connect with the database
mysql_connect('localhost','anonymous','');
mysql_select_db('test');
if (isset($_POST['update'])) {
// perform an update
$name = mysql_escape_string($_POST['name']);
$change = mysql_escape_string($_POST['change']);
$query = "UPDATE products SET stock = stock - $change WHERE name='$name'";
mysql_query($query);
// retrieve the products
$products = array();
$query = "SELECT * FROM products";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
array_push($products,$row);
}
// generate nice HTML output with the results we found
echo '<table>';
echo '<tr>';
echo '<th>NAME</th>';
echo '<th>STOCK</th>';
echo '</tr>';
foreach($products as $product) {
echo '<tr>';
echo '<td>'.$product['name'].'</td>';
echo '<td>'.$product['stock'].'</td>';
echo '</tr>';
}
echo '</table>';
} else {
// retrieve the products
$products = array();
$query = "SELECT * FROM products";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
array_push($products,$row);
}
// generate nice HTML output with the results we found
echo '<table>';
echo '<tr>';
echo '<th>NAME</th>';
echo '<th>STOCK</th>';
echo '<td>SOLD ITEMS</th>';
echo '</tr>';
foreach($products as $product) {
echo '<tr>';
echo '<td>'.$product['name'].'</td>';
echo '<td>'.$product['stock'].'</td>';
echo '<td>';
echo '<form method="post">';
echo '<input type="hidden" name="name" value="'.$product['name'].'" />';
echo '<input type="text" name="change" />';
echo '<input type="submit" name="update" value="update" />';
echo '</form>';
echo '</td>';
echo '</tr>';
}
echo '</table>';
}
?>Now Joe, our employee, sells 5 products to a customer and registers this update.
Suddenly, he realizes that he only sold 4 products,
and thus wants to undo the last update he submitted.
He decides to hit the back button in his browser,
clicks away that annoying dialog,
and he recieves a page that says there have left 10 (2 times 5) products.
To avoid this problem, i have a quite simple solution.
1-) Add a column to our products that contains the timestamp when the product was last updated.
Code: Select all
ALTER TABLE products ADD lastupdate TIMESTAMP NOT NULL;Code: Select all
echo '<input type="hidden" name="lastupdate" value="'.$productї'lastupdate'].'" />';Code: Select all
// find out when this product was updated the last time
$query = 'SELECT * FROM products WHERE product_id='.$product_id;
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
// compare the lastupdate in our form with the lastupdate in the database
if ($_POST['lastupdate'] < $row['lastupdate']) {
echo "You should use the correction page instead!!!!";
} else {
// perform the update - don't forget to set lastupdate to NOW()
}So we end up with the following code
Code: Select all
<?php
// connect with the database
mysql_connect('localhost','anonymous','');
mysql_select_db('test');
if (isset($_POST['update'])) {
$name = mysql_escape_string($_POST['name']);
$change = mysql_escape_string($_POST['change']);
// find out when this product was updated the last time
$query = "SELECT * FROM products WHERE name='$name'";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
// compare the lastupdate in our form with the lastupdate in the database
if ($_POST['lastupdate'] < $row['lastupdate']) {
echo "You should use the <a href="correction.php">correction page</a> instead!!!!";
} else {
// perform an update
$query = "UPDATE products SET stock = stock - $change, lastupdate=NOW() WHERE name='$name'";
mysql_query($query);
// retrieve the products
$products = array();
$query = "SELECT * FROM products";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
array_push($products,$row);
}
// generate nice HTML output with the results we found
echo '<table>';
echo '<tr>';
echo '<th>NAME</th>';
echo '<th>STOCK</th>';
echo '</tr>';
foreach($products as $product) {
echo '<tr>';
echo '<td>'.$product['name'].'</td>';
echo '<td>'.$product['stock'].'</td>';
echo '</tr>';
}
echo '</table>';
}
} else {
// retrieve the products
$products = array();
$query = "SELECT * FROM products";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
array_push($products,$row);
}
// generate nice HTML output with the results we found
echo '<table>';
echo '<tr>';
echo '<th>NAME</th>';
echo '<th>STOCK</th>';
echo '<td>SOLD ITEMS</th>';
echo '</tr>';
foreach($products as $product) {
echo '<tr>';
echo '<td>'.$product['name'].'</td>';
echo '<td>'.$product['stock'].'</td>';
echo '<td>';
echo '<form method="post">';
echo '<input type="hidden" name="lastupdate" value="'.$product['lastupdate'].'" />';
echo '<input type="hidden" name="name" value="'.$product['name'].'" />';
echo '<input type="text" name="change" />';
echo '<input type="submit" name="update" value="update" />';
echo '</form>';
echo '</td>';
echo '</tr>';
}
echo '</table>';
}
?>