Jump to content

Update multiple row at once


Obodo
 Share

Recommended Posts

hello  all

i am building a shopping system, but i am stuck. i want to update items in the database but it is affecting only the last row.
thanks for the help

			
			<input type="hidden" class="form-control" name="prod_id"  value="'.$values["product_ids"].'" />
			<td><input type="text" class="form-control" readonly name="prod_name"  value="'.$values["product_name"].'" /></td>
			<td><input type="text" class="form-control" readonly name="prod_type"  value="'.$values["product_type"].'" /></td>
			<td><input type="text" class="form-control" readonly name="prod_size"  value="'.$values["product_size"].'" /></td>
			<td><input type="text" class="form-control" readonly name="prod_qty"  value="'.$values["product_quantity"].'" /></td>
			<td><input type="text" class="form-control" readonly name="prod_price"  value="'.$values["product_price"].'" /></td>
			<td><input type="text" class="form-control" readonly name="total_price"  value="'.number_format($values["product_quantity"] * $values["product_price"], 2).'" /></td>


if(isset($_POST['enter']))
{

require_once 'database_connection.php';

	$prod_name 		= $_POST['prod_name'];
	$prod_id  		= $_POST['prod_id'];
	$prod_type  	= $_POST['prod_type'];
	$prod_size  	= $_POST['prod_size'];
	$prod_qty 	 	= $_POST['prod_qty'];
	$prod_price  	= $_POST['prod_price'];
	$total_price  	= $_POST['total_price'];
	$trans_ref 		= mt_rand(10000, 99999);


$sql = "INSERT INTO test_sales (
trans_ref, 
prod_id, 
prod_name,
prod_type,
prod_size,
prod_qty,
prod_price,
total_price,
trans_date
) 
VALUES (
:trans_ref, 
:prod_id, 
:prod_name,
:prod_type,
:prod_size,
:prod_qty,
:prod_price,
:total_price, 
NOW()
)";
$stmt = $connect->prepare($sql);
$stmt->execute(array(
':trans_ref'	=> $trans_ref,
':prod_id' 		=> $prod_id,
':prod_name' 	=> $prod_name,
':prod_type' 	=> $prod_type,
':prod_size' 	=> $prod_size,
':prod_qty'		=> $prod_qty,
':prod_price' 	=> $prod_price,
':total_price' 	=> $total_price
));

$num_rows = $stmt->rowCount();

if($num_rows){

 
    $sql = "
	UPDATE  products
	SET 	qty 		= qty - '$prod_qty'
	WHERE 	prod_name 	= '$prod_name'
	AND 	prod_id 	= '$prod_id'
	";
	$stmt = $connect->prepare($sql);
	//$stmt->bindValue(':qty', $prod_qty, PDO::PARAM_STR);
	$stmt->bindValue(':prod_name', $prod_name, PDO::PARAM_STR);
	$stmt->bindValue(':prod_id', $prod_id, PDO::PARAM_STR);
	$stmt->execute();




echo  '<div class="alert bg-success text-center">ITEMS ADDED</div>';
unset($_SESSION["shopping_cart"]);


}else{
	
	echo '<div class="alert bg-danger text-center">A PROBLEM OCCURRED</div>';

}

}

 

Link to comment
Share on other sites

Next time, please post all of your code intact instead of extracting the pieces you think are relevant. That said, with some educated guesses to fill in the blanks,

If your HTML form is including lots of rows to update, you can't give every "product name" field the same "prod_name" field. It's like you wrote code

$prod_name = "First product";
$prod_name = "Second product";
$prod_name = "Third product";

Think about it: what's $prod_name going to be?

Use [ ]s in your form field names to create arrays, similar to the code

$prod_name[] = "First product";
$prod_name[] = "Second product";
$prod_name[] = "Third product";

In this case, I would suggest using the product_ids (is it just one ID, right?) as one array key, then "product_name" and such as secondary array keys. Means you can remove the hidden prod_id field too.

'<td><input type="text" class="form-control" readonly name="products['.$values["product_ids"].'][prod_name]"  value="'.$values["product_name"].'" /></td>'

But... the fields are readonly?

Link to comment
Share on other sites

12 hours ago, Barand said:

When it's finished, send us a link to the site. I love buying from sites where the user can specify their own prices (0.01) for each item purchased 😀

All you should be sending from the form is product ID and qty. Price will be stored in your product table.

Hahahahahahaha. Users don't get to input their own price. this page is actually to send the order to the database and print. meanwhile its for online, I'm developing it for my wife to use in her shop. so everything is ran on localhost.

Link to comment
Share on other sites

14 hours ago, requinix said:

Next time, please post all of your code intact instead of extracting the pieces you think are relevant. That said, with some educated guesses to fill in the blanks,

If your HTML form is including lots of rows to update, you can't give every "product name" field the same "prod_name" field. It's like you wrote code

$prod_name = "First product";
$prod_name = "Second product";
$prod_name = "Third product";

Think about it: what's $prod_name going to be?

Use [ ]s in your form field names to create arrays, similar to the code

$prod_name[] = "First product";
$prod_name[] = "Second product";
$prod_name[] = "Third product";

In this case, I would suggest using the product_ids (is it just one ID, right?) as one array key, then "product_name" and such as secondary array keys. Means you can remove the hidden prod_id field too.

'<td><input type="text" class="form-control" readonly name="products['.$values["product_ids"].'][prod_name]"  value="'.$values["product_name"].'" /></td>'

But... the fields are readonly?

Sorry but i don't quite get you. the files are read only cos user is not expected to do any form of input on the page. the values are from another page which is the front end. this page is the checkout page.

Link to comment
Share on other sites

1 minute ago, Obodo said:

Sorry but i don't quite get you. the files are read only cos user is not expected to do any form of input on the page. the values are from another page which is the front end. this page is the checkout page.

Then what are you "updating"?

Also,

number_format($values["product_quantity"] * $values["product_price"], 2)

don't do that. I know you think you won't deal with figures above 1000 but number_format will add commas ("1,000") which PHP will not be able to reverse into a regular number. If you want to show a formatted number then go ahead and do that, but the value itself has to be unformatted.
Which brings us back to the "what are you updating" question.

Link to comment
Share on other sites

5 minutes ago, requinix said:

Then what are you "updating"?

Also,

number_format($values["product_quantity"] * $values["product_price"], 2)

don't do that. I know you think you won't deal with figures above 1000 but number_format will add commas ("1,000") which PHP will not be able to reverse into a regular number. If you want to show a formatted number then go ahead and do that, but the value itself has to be unformatted.
Which brings us back to the "what are you updating" question.

I really want to update the record. I mean to subtract the quantity of items bought to that in stock. that is if a customer buys 12 coke and 15 milk, they will be deducted from the total in stock. hope you get it.

thanks

Link to comment
Share on other sites

23 minutes ago, Obodo said:

subtract the quantity of items bought

stock/inventory should be handled using an accounting/transaction ledger type system, where a separate row is inserted for every +/- transaction that affects a value. this will provide you with an audit trail so that you can tell if a programming mistake, duplicate submission, or nefarious activity has altered a value. you would then query to SUM() the +/- amounts for each item id to get the current stock/inventory amounts.

when you submit the items making up an order/sale, you would insert a row into an order/sale table, with the unique/one-time  information about the order. you would then get the last insert id from that query and use it when inserting the rows containing the item id/quantity into an order_item table.

Edited by mac_gyver
Link to comment
Share on other sites

thanks all.

i got it up and running now. but Mr Barand, is there a way i could have stored the items into the database without the use of form? meanwhile like i tried explaining, the values are derived from session so the page i had problem is the checkout page. 

 

thanks again

Link to comment
Share on other sites

      +-----------------+                                  +-----------------+
      | checkout page   |                                  |      Server     |
      |-----------------|      AJAX request                |-----------------|
      |          Send   | ----------------------------->   |    Update DB    |
      |                 |      with product data           |        |        |
      |                 |                                  |        |        |
      |                 |                                  |        |        |
      |     Process     |  <----------------------------   | return response |
      |     response    |                                  |                 |
      |                 |                                  |                 |
      |                 |                                  |                 |
      |                 |                                  |                 |
      +-----------------+                                  +-----------------+

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.