mysql update qty on complete order array

Question

i'm trying to update quantities left in stock on all items in the order, when the order is completed in a point of sale system

my code so far is

$order=$_GET["order"];

include('php/config.php');


$invoice=$order;
$voucher=$_GET['voucher'];
$card=$_GET['card'];
$cash=$_GET["cash"];
$date = date('Y-m-d');

$sql="SELECT * FROM `orders` WHERE  `invoice` = '".$invoice."'";

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {
$parts[] = array(
"part" => $row['part'], 
"qty" => $row['qty'], 
 );

$sqlstock = "UPDATE `stock` SET available='available - $parts[qty]' WHERE 

part = '".$parts['part']."'";
}

chandresh_cool · Answer 1 · 2013-05-08T16:37:38.900

0

Change your while loop to this

while ($row = mysql_fetch_array($result)) {
    $parts[] = array(
    "part" => $row['part'], 
    "qty" => $row['qty'], 
     );

    $sqlstock = mysql_query("UPDATE `stock` SET available=available - $parts[qty] WHERE part = 
    '".$part['part']."'");
}

edited May 08 '13 at 16:37

answered May 08 '13 at 15:24

chandresh_cool

11,753
3
30
45

i Dont know what i'm doing wrong but still can't get this to work, it doesnt through up any errors but wont update quantities – Joe Bambridge May 08 '13 at 15:57
The code is setting up a piece of SQL to do the update, but it also needs to actually execute that SQL. – Kickstart May 08 '13 at 16:36

Kickstart · Accepted Answer · 2013-05-08T16:42:02.977

0

Probably best to do this as a single piece of SQL:-

UPDATE stock a INNER JOIN order b ON a.part = b.part 
SET a.available = a.available - b.qty
WHERE  b.invoice` = '$order'

Watch out that you don't just rerun this multiple times without some way of checking that an order hasn't already been used to update the stock

Doing it this way, if you had 1000 items on the order then it is a single query. Doing a select and then looping around the results would require 10001 queries.

edited May 08 '13 at 16:42

answered May 08 '13 at 15:26

Kickstart

21,403
2
21
33

` $sqlstock = mysql_query("UPDATE stock b, orders p SET b.available = b.available- p.qty WHERE b.part = p.part AND p.invoice = '".$invoice."'"); ` – Joe Bambridge May 09 '13 at 18:35
Pretty much the same. You have just used the older way of doing a join, and used $invoice variable (in your script you have just set $invoice to equal $order). Note you should check that $invoice / $order is safe (ie, if a string run it through mysql_real_escape_string and if an integers use intval). – Kickstart May 13 '13 at 08:21

score 0 · Answer 3 · answered May 08 '13 at 15:31

Your main problems are with available='available - $parts[qty]' and .$parts.

The available='available - $parts[qty]' will be interpreted as a string so change it to

available=available - {$parts['qty']}

and change .$parts. to .$parts['part']

Also if you are writing this as new code consider converting your mysql_ functions to mysqli_ or use PDO as mysl is now deprecated and no longer supported in PHP.

http://www.php.net/manual/en/mysqli.construct.php

http://uk1.php.net/manual/en/pdo.construct.php

mysql update qty on complete order array

3 Answers3

Linked