I am trying to build a inventory page.. I want it to minus one everytime a product is selected..
right now I have it setup as item number > Amount taken
I have no idea how to make the minus one work in the php mysql.. in the drop down I have -1 but then it will update in the system as minus -1 it will not actually take one off.
You can create an array that tracks which items have been selected, so everytime an item is selected it adds +1 to the item's value in the array. Then, after the form is submitted (or in the form submit action) you call the function that stores the data into de db, so that it runs the database update for every element in the array.
id/qty in form or js/ajax/session array - depends how you're implementing this. You then pick up the values on checkout confirm. It should be an array of some description:
$_POST['checkout']['product_id']
<select name='product'>
<option value='1'>Car</option>
<option value='2'>Bike</option>
<option value='3'>Truck</option>
<option value='4'>Motor bike</option>
<option value='5'>Tractor</option>
</select>
<select name='qty'>
<option value='1'>1</option>
<option value='2'>2</option>
<option value='3'>3</option>
<option value='4'>4</option>
<option value='5'>5</option>
</select>
<?php
//the process page
if(ctype_digit($_POST['product']) && ctype_digit($_POST['qty'])){
$Q = "UPDATE `products` SET `instock` = `instock` - {$_POST['qty']} WHERE `prodid` = {$_POST['product']}";
}
?>
I'd also try and find a definite confirmation of removing items out of stock, someone could refresh the page multiple times and you could get items disappearing in the system.
And also move the stock through a status if you don't already, something like:
in stock -> Reserved -> Paid -> Shipped -> Delivered OK
So the same item isnt bought twice and if a payment cancels you can put the qty back in stock - basically so the whole thing is tracked and you know where things are
> <?php
> //the process page
> if(ctype_digit($_POST['product']) && ctype_digit($_POST['qty'])){
> $Q = "UPDATE `products` SET `instock` = `instock` - {$_POST['qty']} WHERE `prodid` = {$_POST['product']}";
> }
?>
Please excuse my tiredness . I don't understand the ctype_digit part??
my stock is dynamic... and parts could be static...
this is what i really need right ?
$Q = "UPDATE `products` SET `instock` = `instock` - {$_POST['qty']} WHERE `prodid` = {$_POST['product']}";
ctype_digit($value) returns true if the $value is a string and is made up of only digits(0-9)
So when you say if(ctype_digit($value)){ it will only run if the passed $value contains only digits - so for product id's its perfect - (if prodid and quantity is a string containing only digits run the script else theres a bad value)
its just input validation, the query by itself will work - just imagine if you don't escape values and someone enters "1,instock = 700000" into $_POST['qty']
Thank you but im not sure what im missing here -
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf
Replaced \/
("UPDATE fruitinventory SET stock=%s, type=%s WHERE itemnumber=%s",
GetSQLValueString($_POST['amount'], "text"),
GetSQLValueString($_POST['type'], "text"),
GetSQLValueString($_POST['type'], "text"));
replaced with \/
if(ctype_digit($_POST['itemnumber']) && ctype_digit($_POST['amount'])){
$Q = ("UPDATE fruitinventory SET instock = instock - {$_POST['amount']} WHERE itemnumber = {$_POST['itemnumber']}");
}
Heres what I have but im not sure where I am missing the subtraction at ?
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s WHERE itemnumber=%s",
GetSQLValueString( - $_POST['amount'], "int"),
GetSQLValueString($_POST['type'], "text"),
GetSQLValueString($_POST['type'], "text"));
this is what i have but it still subtracts the incorrect amount
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s, stock=stock - '%{$_POST['amount']}%' WHERE itemnumber=%s",
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s WHERE itemnumber=%s",
GetSQLValueString( - $_POST['amount'], "int"),
GetSQLValueString($_POST['type'], "text"),
GetSQLValueString($_POST['type'], "text"));
to:
$updateSQL = sprintf("UPDATE fruitinventory SET stock = stock - %s, type=%s WHERE itemnumber=%s",
GetSQLValueString($_POST['amount'], "int"),
GetSQLValueString($_POST['type'], "text"),
GetSQLValueString($_POST['id'], "text"));//this needs to be the unique id of the product
or mine:
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
if(ctype_digit($_POST['itemnumber']) && ctype_digit($_POST['amount'])){
if(ctype_alnum($_POST['type'])){//check type is only letters and numbers
$updateSQL = "UPDATE fruitinventory SET stock = stock - {$_POST['amount']}, `type` = '{$_POST['type']}' WHERE itemnumber = {$_POST['itemnumber']}";
}else{//if not just update stock
$updateSQL = "UPDATE fruitinventory SET stock = stock - {$_POST['amount']} WHERE itemnumber = {$_POST['itemnumber']}";
}
}else{
echo "itemnumber and amount are not integers<br/>\r\n";
}
need a little more practice on the basics!
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s, stock=stock - '%{$_POST['amount']}%' WHERE itemnumber=%s",
to:
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s, stock=stock - {$_POST['amount']} WHERE itemnumber=%s",
mysql is expecting a query like UPDATE table SET field = field - 3 WHERE rowid = 9
or in your case UPDATE fruitinventory SET stock = stock - 2 WHERE itemnumber = 4
You got to get php to output the correct syntax for mysql to parse, whatever method you use mysql doesnt care.
When it recieves UPDATE fruitinventory SET stock = stock - '%2%' WHERE itemnumber = 4 its gonna either attempt to translate '%2%' from a string to a number or instead just error. strings are surrounded in quotes, numbers have no quotes, which is same in pretty much any programming language. php and mysql are pretty good at converting strings like '57' into the integar 57 so you can often get away with it.
Thank you but im getting zero still for some reason
thats my drop down \/
<select name="amount" id="amount">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
</select>
When I'm using this code for some reason it subracts everything... im not sure what im missing.. I will select 1 and it subtract 50 or 51 it doesnt make what the number is because it always come to 0...
$updateSQL = sprintf("UPDATE fruitinventory SET stock=%s, type=%s, stock=stock - {$_POST['amount']} WHERE itemnumber=%s",
Maybe i got into it too much but heres a working example, i used it to try out ajax with jquery:
stocktest.php
<?php
$DB = mysqli_connect('localhost','stocktest','testing','bim');
$stockQ = "SELECT * FROM `stocktest`";
$stockR = mysqli_query($DB,$stockQ);
echo mysqli_error($DB);
$stockdata = array();
while($row = mysqli_fetch_assoc($stockR)){
$stockdata[$row['stockid']] = $row;
}
?>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js" type="text/javascript"></script>
<script type='text/javascript'>
var stockData = new Array();
<?php
foreach($stockdata as $k=>$v){
echo "stockData[{$k}] = new Array();\r\n";
foreach($v as $ke=>$va){
echo "stockData[{$k}]['{$ke}'] = '{$va}';\r\n";
}
}
?>
function addToCart(){
var prod = document.getElementById('prod').value;
var qty = document.getElementById('amount').value;
$.post("ajax_updstock.php", { prod: prod, qty: qty },
function(data) {
if(data == 'Y'){
window.location.reload();
}else{
alert(data);
}
}
);
}
function updPrice(){
var prod = document.getElementById('prod').value;
var qty = document.getElementById('amount').value;
var costDiv = document.getElementById('cost');
costDiv.innerHTML = '£'+Math.round((stockData[prod]['price']*qty)*100)/100;
}
</script>
</head>
<body>
Product:
<select name='prod' id='prod' onchange="updPrice();">
<?php
foreach($stockdata as $k=>$v){
echo "<option value='{$k}'>{$v['prodname']} - Available: {$v['stock']}</option>\r\n";
}
?>
</select>
<br/>
Quantity:
<select name="amount" id="amount" onchange="updPrice();">
<?php
$amts1 = range(1,10);
$amts2 = range(15,50,5);
$amounts = array_merge($amts1,$amts2);
foreach($amounts as $v){
echo "<option value='{$v}'>{$v}</option>\r\n";
}
?>
</select>
<div id='cost'></div>
<a href='javascript:' onclick="addToCart();">Add to cart</a>
<script type='text/javascript'>
updPrice();
</script>
</body>
</html>
ajax_updstock.php
<?php
$prodid = $_POST['prod'];
$qty = $_POST['qty'];
if(ctype_digit($prodid) && ctype_digit($qty)){
$DB = mysqli_connect('localhost','stocktest','testing','bim');
$Q = "select * FROM stocktest WHERE `stockid` = {$prodid}";
$R = mysqli_query($DB,$Q);
$stockData = mysqli_fetch_assoc($R);
if(ISSET($stockData['stockid'])){
if($qty > $stockData['stock']){$qty = $stockData['stock'];}
$updQ = "UPDATE stocktest SET stock = stock - {$qty} WHERE stockid = {$prodid}";
if(mysqli_query($DB,$updQ)){
echo 'Y';
}else{
echo 'Update failed: '.mysqli_error($DB);
}
}else{
echo 'Product not found: '.$prodid;
}
}else{
echo 'Prodid and/or qty not valid: '.var_dump($prodid).' - '.var_dump($qty);
}
?>
mysql code to create database, table & user
/*
SQLyog Community v9.63
MySQL - 5.5.17-log : Database - bim
*********************************************************************
*/
CREATE DATABASE /*!32312 IF NOT EXISTS*/`bim` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `bim`;
/*Table structure for table `stocktest` */
DROP TABLE IF EXISTS `stocktest`;
CREATE TABLE `stocktest` (
`stockid` int(5) NOT NULL AUTO_INCREMENT,
`prodname` varchar(30) DEFAULT NULL,
`stock` int(4) NOT NULL DEFAULT '0',
`price` decimal(5,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`stockid`),
UNIQUE KEY `prodname` (`prodname`),
KEY `stock` (`stock`),
KEY `price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
insert into `stocktest`(`stockid`,`prodname`,`stock`,`price`) values (1,'tv',12,'349.99'),(2,'xbox',8,'154.99'),(3,'Mouse mat',366,'3.99'),(4,'Mouse',230,'5.99');
CREATE USER 'stocktest'@'localhost' IDENTIFIED BY 'testing';
FLUSH PRIVILEGES;
GRANT ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `bim`.`stocktest` TO 'stocktest'@'localhost' WITH GRANT OPTION;
I figured out what I was doing wrong.. Its always something right in front of my face
I had stock=%s then stock=stock it was pulling from the stock=%s
:)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.