Insert into database at all at once

[I-AM-OBODO]

13 minutes ago, Barand said:

If that code is in the same page as your previous code then remember those $_POST items are now arrays.

Once again you can can throw away the form and update stocks with a single query...

UPDATE stocks s
       JOIN temp_shopping t 
            USING (product_name, item_type, item_size, category)
SET s.item_qty = s.item_qty - t.item_qty
WHERE t.trans_ref = ?

(This assumes you have checks into your application to ensure you have sufficient stock when making a sale.)

And you have another table that requires normalizing

                                    +------------------+
                                    |  stocks          |
                                    +------------------+
                          +--------<| product_id       |
                          |         | item_qty         |
                          |         +------------------+
                          |
+-----------------+       |
|  product        |       |
+-----------------+       |
| product_id      |-------+
| product_name    |       |
| category        |       |         +------------------+
| size            |       |         |  shopping        |
| type            |       |         +------------------+
| price           |       |         |  shopping_id     |
+-----------------+       |         |  trans_ref       |
                          +--------<|  product_id      |
                                    |  qty             |
                                    |  date            |
                                    +------------------+

 

Thanks. but am working on the form type version. Will look at the other version later. thanks

[I-AM-OBODO]

On 6/18/2019 at 7:33 AM, Barand said:

If that code is in the same page as your previous code then remember those $_POST items are now arrays.

Once again you can can throw away the form and update stocks with a single query...

UPDATE stocks s
       JOIN temp_shopping t 
            USING (product_name, item_type, item_size, category)
SET s.item_qty = s.item_qty - t.item_qty
WHERE t.trans_ref = ?

(This assumes you have checks into your application to ensure you have sufficient stock when making a sale.)

And you have another table that requires normalizing

                                    +------------------+
                                    |  stocks          |
                                    +------------------+
                          +--------<| product_id       |
                          |         | item_qty         |
                          |         +------------------+
                          |
+-----------------+       |
|  product        |       |
+-----------------+       |
| product_id      |-------+
| product_name    |       |
| category        |       |         +------------------+
| size            |       |         |  shopping        |
| type            |       |         +------------------+
| price           |       |         |  shopping_id     |
+-----------------+       |         |  trans_ref       |
                          +--------<|  product_id      |
                                    |  qty             |
                                    |  date            |
                                    +------------------+

 

Good day. I still havent been able to update the stocks. I have tried so many things and encountered various errors:

Cannot pass parameter 2 by reference
Fatal error: Cannot pass parameter 2 by reference

 

Please can you help me with the update stuff?

Thanks

[Barand]

Process the $_POST data in a very similar method to that used for the shopping table inserts.

I don't know if you are trying that as , once again, you have left us to guess what the code is that produced your error messages.

[I-AM-OBODO]

10 hours ago, Barand said:

Process the $_POST data in a very similar method to that used for the shopping table inserts.

I don't know if you are trying that as , once again, you have left us to guess what the code is that produced your error messages.

This is what i did and the error: Fatal error: Cannot pass parameter 2 by reference

	$update = $pdo->prepare("
UPDATE stocks
SET item_qty = :item_qty
WHERE category = :category
AND  product_name = :product_name
AND item_size = :item_size
AND item_type = :item_type
");
	foreach($_POST['category'] as $k => $category){
        $update->bindParam(':item_qty', $item_q - $_POST['item_qty'][$k]);
        $update->bindParam(':product_name', $_POST['product_name'][$k]);
        $update->bindParam(':item_type', $_POST['item_type'][$k]);
        $update->bindParam(':item_size', $_POST['item_size'][$k]);
        $update->execute();
    }
	

Edited June 22, 2019 by I-AM-OBODO
But am also thinking, to avoid so many use of AND in the WHERE condition, i could just say where item_id =. The problem with this is that i will need to parse the item_id to the next page which isnt part of the innitial setup

[Barand]

I think the problem is this line ...

$update->bindParam(':item_qty', $item_q - $_POST['item_qty'][$k]);

... where you are attempting to bind an expression and not a variable.

Try changing the query (and you also need to bind the category)

    $update = $pdo->prepare("
                        UPDATE stocks
                        SET item_qty = item_qty - :item_qty                 -- changed line
                        WHERE category = :category
                        AND  product_name = :product_name
                        AND item_size = :item_size
                        AND item_type = :item_type
                        ");
    foreach($_POST['category'] as $k => $category){
        $update->execute( [ 
                            ':item_qty'    => $_POST['item_qty'][$k],
                            ':category'    => $category,
                            ':product_name'=> $_POST['product_name'][$k],
                            ':item_type'   => $_POST['item_type'][$k],
                            ':item_size'   => $_POST['item_size'][$k]
                          ] );  
    }

 

[I-AM-OBODO]

On 6/22/2019 at 10:13 PM, Barand said:

I think the problem is this line ...

$update->bindParam(':item_qty', $item_q - $_POST['item_qty'][$k]);

... where you are attempting to bind an expression and not a variable.

Try changing the query (and you also need to bind the category)

    $update = $pdo->prepare("
                        UPDATE stocks
                        SET item_qty = item_qty - :item_qty                 -- changed line
                        WHERE category = :category
                        AND  product_name = :product_name
                        AND item_size = :item_size
                        AND item_type = :item_type
                        ");
    foreach($_POST['category'] as $k => $category){
        $update->execute( [ 
                            ':item_qty'    => $_POST['item_qty'][$k],
                            ':category'    => $category,
                            ':product_name'=> $_POST['product_name'][$k],
                            ':item_type'   => $_POST['item_type'][$k],
                            ':item_size'   => $_POST['item_size'][$k]
                          ] );  
    }

 

Thank you so very much!