Jump to content
I-AM-OBODO

Insert into database at all at once

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)
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 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

Share this post


Link to post
Share on other sites

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]
                          ] );  
    }

 

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
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.


×
×
  • 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.