Update multiple row at once

[Obodo]

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>';

}

}

 

[requinix]

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?

[Barand]

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.

[Obodo]

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.

[Obodo]

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.

[requinix]

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.

[Obodo]

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

[mac_gyver]

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 September 11, 2021 by mac_gyver

[Obodo]

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

[Barand]

44 minutes ago, Obodo said:

is there a way i could have stored the items into the database without the use of form?

A javascript/AJAX process is a common alternative

[Obodo]

43 minutes ago, Barand said:

A javascript/AJAX process is a common alternative

ok sir. how can that be done with the above mentioned?

[Barand]

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