Update 2 query in sql using php

[dardime]

Hi Im about to update two query in mysql that subtracts quantity of current ID and add quantity to another ID but i got error on mysql.

 

Here is my code:

<?php require_once('localhost.php'); ?>
<?php
if(!isset($_SESSION)){
    session_start();
}
	include_once("../repair/includes/product.php");
	$product = new product();
	$productstock = $product->fetchProduct(stripslashes($_GET["id"]));
	
	if(!empty($_POST)){
	//Update 
	include_once("../repair/includes/conn_logs.php");
		if($link === false){
			die("ERROR: Could not connect. " . mysqli_connect_error());
			}
			$sql = "UPDATE stock_product SET quantity='".$productstock["quantity"]-$_POST["quantity"]."' WHERE id='".$_GET['id']."; 
					UPDATE stock_product SET quantity='".$productstock["quantity"]+$_POST["quantity"]."' WHERE id='".$_POST['id']."'";
																			   
			if(mysqli_query($link, $sql)){
			echo "";
			} else{
			echo "" . mysqli_error($link);
		}
	//Update
 }
?>

HTML/PHP form

<div class="alert-danger"><?php if(empty($error)){ echo ''; }else{ echo '<div class="msg" style="text-align:left;">'.$error.'</div>'; } ?></div>
            <form method="post" action="<?php $_SERVER['PHP_SELF']; ?>?id=<?php echo $productstock["id"]; ?>">
            <input type="hidden" name="id" value="<?php echo $id; ?>" />
                <table class="table">
                                    <tbody>
                                    	<tr>
                                            <td><strong>Product ID:</strong></td>
                                            <td><?php echo $productstock["id"]; ?></td>
                                            <td><strong>Brand:</strong></td>
                                            <td><?php echo $productstock["brand_id"]; ?></td>
                                        </tr>
                                        <tr>
                                            <td><strong>Name:</strong></td>
                                            <td><?php echo $productstock["name"]; ?></td>
                                            <td><strong>Category:</strong></td>
                                            <td><?php echo $productstock["category_id"]; ?></td>
                                       <tr>
                                   		  <td><strong>Branch:</strong></td>
                                          <td><?php echo $productstock["branch"]; ?></td>
                                          <td><strong>Quantity:</strong></td>
                                          <td><?php echo $productstock["quantity"]; ?></td>
                                         </tr>
                                       <tr>
                                       	<td><strong>Quantity:</strong></td>
                                         <td><input name="quantity" type="text" id="quantity" placeholder="Quantity" class="form-control"/></td>
                                         <td>Product ID:</td>
                                          <td><input name="id" type="text" id="id" placeholder="Product ID" class="form-control"/></td>
                                       </tr>
                                   </tbody>
                         </table>
                         <input class="form-control" type="hidden" name="id" id="id" value="<?php echo $_POST["id"]; ?>">
                         <input class="form-control" type="hidden" name="quantity" id="quantity" value="<?php echo $_POST["quantity"]; ?>">
                         <input name="submit" type="submit" class="btn btn-primary" value="Transfer Stock">
			            </form>

Thanks in advance for your help!

[gizmola]

Please bind your parameters! See http://php.net/manual/en/mysqli-stmt.bind-param.php

You need to do these queries one at a time. You can't just have a bunch of sql statements as one big string. That won't work.

 

If this needs to be a single transaction then you need to:

1. Have your tables be using a mysql engine that supports transactions. Usually people use InnoDB.
2. Utilize the begin transaction and commit.  

See:  http://php.net/manual/en/mysqli.begin-transaction.php

 

[mac_gyver]

in addition to not being able to execute multiple queries in one sql statement (unless you use a php statement/method call that supports it), your form has two fields named quantity and three fields named id. only the last field for any name will 'win'. you need to get your form code under control so that you will know which field is going to actually supply the values that your form processing code will use.

 

next, you shouldn't add/subtract values to maintain a quantity. your current method has no way of detecting/preventing multiple form submissions from incorrectly altering the data. you can end up with the wrong quantity values and you won't even know if this has occurred. the correct way of maintaining a quantity (or any other 'account' total) is to insert a new record for each transaction that adjusts the value, inset a positive value to add to the quantity and negative value to subtract from the quantity. you would then simply SUM() the quantity for any id value (use GROUP BY id) to get the current quantity.

[dardime]

Syntax error again. Please help!

$sql = "UPDATE stock_product SET quantity = quantity - 1 WHERE id=".$_GET['id'].";
	UPDATE stock_product SET quantity = quantity + 1 WHERE id='".$_POST['id']."'";

[NigelRel3]

As already mentioned - using multiple statements in one string will not work.

You could rewrite your query though to be something like 

UPDATE stock s1, stock s2
   set s1.qty = s1.qty-1, s2.qty = s2.qty +1
   where s1.id= 1 and s2.id= 1

you'll have to change the names as appropriate - and BIND the variables for the ID's.

[NigelRel3]

next, you shouldn't add/subtract values to maintain a quantity. your current method has no way of detecting/preventing multiple form submissions from incorrectly altering the data. you can end up with the wrong quantity values and you won't even know if this has occurred. the correct way of maintaining a quantity (or any other 'account' total) is to insert a new record for each transaction that adjusts the value, inset a positive value to add to the quantity and negative value to subtract from the quantity. you would then simply SUM() the quantity for any id value (use GROUP BY id) to get the current quantity.

Interesting approach - something which I've not seen before, I can see where some systems this is vital - but others is it a bit of overkill?  Out of interest - do you have any idea at what point performance becomes an issue?  Just that with SQL usually being the slowest part of most systems, I can imagine that over time or with high volumes of transactions, this may become an issue.

[dardime]

CHEERS BRO! THANKS TO ALL OF YOU!

Edited March 11, 2017 by dardime

[mac_gyver]

Interesting approach - something which I've not seen before, I can see where some systems this is vital - but others is it a bit of overkill?  Out of interest - do you have any idea at what point performance becomes an issue?  Just that with SQL usually being the slowest part of most systems, I can imagine that over time or with high volumes of transactions, this may become an issue.

 

if you have any type of account (bank, credit, loan, itunes, cell phone data plan, in game purchases, ...) that keeps track of an amount (quantity or money) or have ever done any in person or on-line shopping, you have seen this being used. it's not overkill, it's a necessity that's used everywhere in real situations. it gives you a record/audit trail of each transaction, so that you know all the who, what, when, where, and why information about each transaction. this lets you detect and correct errors, produce reports,... this method also has the advantage of correctly working with multiple concurrent users, since the changes made by each user will be recorded separately (at least the OP removed a race condition, by eliminating a separate SELECT query to get the 'current' starting amount.)

 

without doing this, by just maintaining the amount in a field, there's no record of any duplicate data submissions, incorrectly entered values (transposed digits, hitting the wrong key), programming errors, fraud/cheating, ... the first sign of a problem is when someone tries to use an amount, that the system says exists, but it doesn't.

 

if the OP is really doing an inventory system, that's more than just a classroom assignment or part of a beginner's attempt at making a game, the current method will eventually get out of sync with the actual products and you won't know which of the possible reasons why because there's no record being kept of what values have affected the total.

[NigelRel3]

if you have any type of account (bank, credit, loan, itunes, cell phone data plan, in game purchases, ...) that keeps track of an amount (quantity or money) or have ever done any in person or on-line shopping, you have seen this being used. it's not overkill, it's a necessity that's used everywhere in real situations. it gives you a record/audit trail of each transaction, so that you know all the who, what, when, where, and why information about each transaction. this lets you detect and correct errors, produce reports,... this method also has the advantage of correctly working with multiple concurrent users, since the changes made by each user will be recorded separately (at least the OP removed a race condition, by eliminating a separate SELECT query to get the 'current' starting amount.)

 

without doing this, by just maintaining the amount in a field, there's no record of any duplicate data submissions, incorrectly entered values (transposed digits, hitting the wrong key), programming errors, fraud/cheating, ... the first sign of a problem is when someone tries to use an amount, that the system says exists, but it doesn't.

 

if the OP is really doing an inventory system, that's more than just a classroom assignment or part of a beginner's attempt at making a game, the current method will eventually get out of sync with the actual products and you won't know which of the possible reasons why because there's no record being kept of what values have affected the total.

I can understand the financial reasons for doing this (and I did say that in some systems it may be overkill), but I've worked with systems where you have tens of thousands of products, some with histories going back for several years.  (In this system) There is an inherent assumption that there will be mistakes in values and as stock goes missing/gets damaged it gets recorded and the overall stock figures are manually checked on a regular basis during stock takes when the correct figures are recorded.

(Just wondering of the overhead it would cause if for various reports to use the audit trail as opposed to the main stock records.  Actually wonder if the full audit trail is still available - hmmm.)

I guess that any design has to weigh the accuracy and 'currentness' of any data against the cost of maintaining it and the chance that the figure isn't correct anyway.  It's probably easier when dealing with transactions that only have a logical existence rather than physical objects which have a lot of external factors in play.

Thanks for that anyway - makes me think about the design considerations for future systems.