Insert into database at all at once

I-AM-OBODO · June 15, 2019

Hi guys,
I have a table that i want all the entries to be inserted all at once.
Mine is just inserting only one of the item, how can i get it to work? I also attached a sample table.

Thanks

	if(isset($_POST['send'])){
    
    $category = $_POST['category'];
    $item_type = $_POST['item_type'];
    $item_size = $_POST['item_size'];
    $product_name = $_POST['product_name'];
    $item_qty = $_POST['item_qty'];
    $price = $_POST['price'];
    $total_price = $_POST['total_price'];
    $item_price = $_POST['item_price'];
    
$sql = "
INSERT INTO shopping(
trans_ref,
category,
product_name,
item_type,
item_size,
item_qty,
item_price,
price,
date
)
	VALUES(
:trans_ref,
:category,
:product_name,
:item_type,
:item_size,
:item_qty,
:item_price,
:price,
NOW()
)
";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
':trans_ref' => $_SESSION['trans_ref'],
':category' => $category,
':product_name' => $product_name,
':item_type' => $item_type,
':item_size' => $item_size,
':item_qty' => $item_qty,
':item_price' => $item_price,
':price' => $price
));
	
if ( $stmt->rowCount()){    
    echo '<div class="alert bg-success text-center">SHOPPING COMPLETED</div>';
}else{
    echo '<div class="alert bg-danger text-center">SHOPPING NOT COMPLETED. A PROBLE OCCURED</div>';
}
	
}

Barand · June 15, 2019

What does the data being posted to the form look like. IE What does this output...

echo '<pre>', print_r($_POST, 1), '</pre>';

?

benanamen · June 15, 2019

Get rid of the variables for nothing. You already have the POST values, just use them.

I-AM-OBODO · June 15, 2019

1 hour ago, benanamen said:

Get rid of the variables for nothing. You already have the POST values, just use them.

What do you mean by get rid of the variables for nothing?

I-AM-OBODO · June 15, 2019

13 hours ago, Barand said:
What does the data being posted to the form look like. IE What does this output...
echo '<pre>', print_r($_POST, 1), '</pre>';
?

The data on the form is derived from(a while loop) in a temporal table in the database. From there the data and the summation(total) is to be stored the the actual table. But the problem am having is that only the last row is being inserted into the actual table.

Thanks

Edited June 15, 2019 by I-AM-OBODO

Barand · June 15, 2019

Why are you using an intermediate form to move data from one table to another?

Is this a guessing game where we have to guess how the form sending the above POST data is getting its data from your "temporal" table? - Sorry, I don't play games.

benanamen · June 15, 2019

21 minutes ago, I-AM-OBODO said:

What do you mean by get rid of the variables for nothing?

All of this...

    $category = $_POST['category'];
    $item_type = $_POST['item_type'];
    $item_size = $_POST['item_size'];
    $product_name = $_POST['product_name'];
    $item_qty = $_POST['item_qty'];
    $price = $_POST['price'];
    $total_price = $_POST['total_price'];
    $item_price = $_POST['item_price'];

maxxd · June 15, 2019

2 hours ago, I-AM-OBODO said:

The data on the form is derived from(a while loop) in a temporal table in the database. From there the data and the summation(total) is to be stored the the actual table. But the problem am having is that only the last row is being inserted into the actual table.

Thanks

Nothing in your code shows a loop. If there's a loop inserting data but the only data being inserted via that loop is the last row of data, then there's probably a problem with the loop; which we haven't seen.

maxxd · June 15, 2019

Also, part of the joy of prepared statements is that you only need to prepare them once. Do the PDO::prepare() outside the loop, then bind the variables and run the query in the loop. Which, again, we can't see.

I-AM-OBODO · June 16, 2019

13 hours ago, Barand said:

Why are you using an intermediate form to move data from one table to another?

Is this a guessing game where we have to guess how the form sending the above POST data is getting its data from your "temporal" table? - Sorry, I don't play games.

Sorry. And thanks for your time. Its no guessing game. I omitted the where the data is coming from. I used a form cos i feel it will work for the purpose, cos i tried to use SELECT, INSERT but since the first table and destination table does not have the same number of rows, it didnt work or I couldnt get it to work and so i decided to use form. The previous code is the POST. Below is the rest of the code

$stmt = $pdo->prepare("SELECT * FROM temp_shopping");
$stmt->execute();
$num_rows = $stmt->rowCount();
	if($num_rows < 1){
    
echo '<div class="alert bg-danger text-center">NO RECORD FOUND</div>';
}else{
        
	$total_price = 0;
$stmt = $pdo->query("
SELECT *
FROM
temp_shopping
WHERE trans_ref = '$_SESSION[trans_ref]'
ORDER BY product_name ASC
"
);
	echo "<form action='' method='post'>";
echo "<table width='100%'>";
echo "<tr>
<th>Category</th>
<th>Product Name</th>
<th>Product Type</th>
<th>Size</th>
<th>Qty</th>
<th>Unit Price</th>
<th>Total</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Print out the contents of each row into a table
echo "<tr><td>";
echo "<input type='text' class='form-control' name='category' value='".$row['category']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='product_name' value='".$row['product_name']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='item_type' size='7' value='".$row['item_type']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='item_size' size='5' value='".$row['item_size']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='item_qty' size='5' value='".$row['item_qty']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='item_price' size='7' value='".$row['item_price']."' readonly /> ";
echo "</td><td>";
echo "<input type='text' class='form-control' name='price' size='7' value='".$row['item_price'] * $row['item_qty']."' readonly /> ";
$total_price += $row['item_price'] * $row['item_qty'];
echo "</td></tr>";
	}
echo "<tr>";
echo "<td colspan='6' align='right'>";
echo "<strong>TOTAL PRICE :</strong>";
echo "</td>";
echo "<td align='left'>";
echo "<strong><input type='text' class='form-control' name='total_price' size='7' value='"."₦".number_format($total_price,2)."' readonly /> ";
echo "</td>";
echo "</tr>";
echo "</table>";        
}

Edited June 16, 2019 by I-AM-OBODO

Barand · June 16, 2019

Your problem is the input names. You have multiple items with the same name so, when posted, the last value with that name has overwritten all the previous ones. Only the final row is posted.

You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" ) . To process ...

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $pdo->prepare("INSERT INTO shopping(
                            trans_ref,
                            category,
                            product_name,
                            item_type,
                            item_size,
                            item_qty,
                            item_price,
                            price,
                            date
                            )
                            VALUES(
                            :trans_ref,
                            :category,
                            :product_name,
                            :item_type,
                            :item_size,
                            :item_qty,
                            :item_price,
                            :price,
                            CURDATE()
                            )
                            ");
    foreach ($_POST['category'] as $k => $category) {
        $record = [ 'trans_ref' => $_SESSION['trans_ref'],
                    'category'  => $category,
                    'product_name' => $_POST['product_name'][$k],
                    'item_type' => $_POST['item_type'][$k],
                    'item_size' => $_POST['item_size'][$k],
                    'item_qty' => $_POST['item_qty'][$k],
                    'item_price' => $_POST['item_price'][$k],
                    'price' => $_POST['item_price'][$k] * $_POST['item_qty'][$k],
                  ];
        $stmt->execute($record);
    }
}

That should fix your code. Now to fix your method.

Firstly, don't store derived values. You can calculate the total price when required (SELECT qty * price as totalprice, ...)

Secondly, a single INSERT..SELECT SQL query will replace all the above code (two if you count the check that data exists to be transferred). Here's how I would do it (no form required)

$res = $pdo->prepare("SELECT SUM(item_qty * item_price) 
                      FROM temp_shopping
                      WHERE trans_ref = ?
                      ");
$res->execute([$_SESSION['trans_ref']]);
$total_price = $res->fetchColumn();

if ($total_price == 0) {
    echo "NO RECORDS TO TRANSFER<br>";
}
else {
    $res = $pdo->prepare("INSERT INTO shopping(
                            trans_ref,
                            category,
                            product_name,
                            item_type,
                            item_size,
                            item_qty,
                            item_price,
                            date
                            )
                            SELECT
                                trans_ref,
                                category,
                                product_name,
                                item_type,
                                item_size,
                                item_qty,
                                item_price,
                                CURDATE()
                            FROM temp_shopping
                            WHERE trans_ref = ?    
                            ");
    $res->execute([$_SESSION['trans_ref']]);
    echo "Total value transferred : " . number_format($total_price,2) . '<br>;';
    
}

Edited June 16, 2019 by Barand
code correction ($db to $pdo)

Barand · June 16, 2019

Just a comment on your tables.

Data items like product_name, category, price are properties of the product and should, therefore, only be in the product table. The shopping table should hold just the product_id. (Read up on "data normalization")

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

I-AM-OBODO · June 16, 2019

7 hours ago, Barand said:

Your problem is the input names. You have multiple items with the same name so, when posted, the last value with that name has overwritten all the previous ones. Only the final row is posted.

You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" ) . To process ...


if ($_SERVER['REQUEST_METHOD']=='POST') {
    $stmt = $pdo->prepare("INSERT INTO shopping(
                            trans_ref,
                            category,
                            product_name,
                            item_type,
                            item_size,
                            item_qty,
                            item_price,
                            price,
                            date
                            )
                            VALUES(
                            :trans_ref,
                            :category,
                            :product_name,
                            :item_type,
                            :item_size,
                            :item_qty,
                            :item_price,
                            :price,
                            CURDATE()
                            )
                            ");
    foreach ($_POST['category'] as $k => $category) {
        $record = [ 'trans_ref' => $_SESSION['trans_ref'],
                    'category'  => $category,
                    'product_name' => $_POST['product_name'][$k],
                    'item_type' => $_POST['item_type'][$k],
                    'item_size' => $_POST['item_size'][$k],
                    'item_qty' => $_POST['item_qty'][$k],
                    'item_price' => $_POST['item_price'][$k],
                    'price' => $_POST['item_price'][$k] * $_POST['item_qty'][$k],
                  ];
        $stmt->execute($record);
    }
}

That should fix your code. Now to fix your method.

Firstly, don't store derived values. You can calculate the total price when required (SELECT qty * price as totalprice, ...)

Secondly, a single INSERT..SELECT SQL query will replace all the above code (two if you count the check that data exists to be transferred). Here's how I would do it (no form required)


$res = $pdo->prepare("SELECT SUM(item_qty * item_price) 
                      FROM temp_shopping
                      WHERE trans_ref = ?
                      ");
$res->execute([$_SESSION['trans_ref']]);
$total_price = $res->fetchColumn();

if ($total_price == 0) {
    echo "NO RECORDS TO TRANSFER<br>";
}
else {
    $res = $pdo->prepare("INSERT INTO shopping(
                            trans_ref,
                            category,
                            product_name,
                            item_type,
                            item_size,
                            item_qty,
                            item_price,
                            date
                            )
                            SELECT
                                trans_ref,
                                category,
                                product_name,
                                item_type,
                                item_size,
                                item_qty,
                                item_price,
                                CURDATE()
                            FROM temp_shopping
                            WHERE trans_ref = ?    
                            ");
    $res->execute([$_SESSION['trans_ref']]);
    echo "Total value transferred : " . number_format($total_price,2) . '<br>;';
    
}

Thanks so very much. Will try out your second option and the table normalization once i get this up and running. But i have having error on your first code: Warning: Invalid argument supplied for foreach()

Thanks

Barand · June 16, 2019

Can you post the output you are now getting from

echo '<pre>', print_r($_POST, 1), '</pre>';

I-AM-OBODO · June 16, 2019

7 minutes ago, Barand said:
Can you post the output you are now getting from
echo '<pre>', print_r($_POST, 1), '</pre>';

Array
(
    [category] => Milk
    [product_name] => Peak
    [item_type] => Refill
    [item_size] => 380
    [item_qty] => 2
    [item_price] => 3500
    [price] => 7000
    [total_price] => ₦26,300.00
    [send] => Complete Shopping
)

Barand · June 16, 2019

Read the replies.

7 hours ago, Barand said:

Your problem is the input names. You have multiple items with the same name so, when posted, the last value with that name has overwritten all the previous ones. Only the final row is posted.

You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" )

I-AM-OBODO · June 16, 2019

13 minutes ago, Barand said:

Read the replies.

Yeah. I have read it. But this error: Warning: Invalid argument supplied for foreach() is from your own code:

the line number is 127:

foreach ($_POST['category'] as $k => $category) {

Edited June 16, 2019 by I-AM-OBODO

Barand · June 16, 2019

You might have read it but you totally ignored it.

8 hours ago, Barand said:

You need to append "[ ]" to your input names so they are posted in arrays (EG name="category[]" )

Good bye.

I-AM-OBODO · June 16, 2019

56 minutes ago, Barand said:

You might have read it but you totally ignored it.

Good bye.

oops! oversight. this is what i got:

Array
(
    [category] => Array
        (
            [0] => Beverages
            [1] => Milk
            [2] => Cereals
            [3] => Beverages
            [4] => Milk
        )

    [product_name] => Array
        (
            [0] => Bournvita
            [1] => Dano
            [2] => Golden morn
            [3] => Milo
            [4] => Peak
        )

    [item_type] => Array
        (
            [0] => Refill
            [1] => Evap
            [2] => NONE
            [3] => Tin
            [4] => Evap
        )

    [item_size] => Array
        (
            [0] => 1
            [1] => 160
            [2] => 1
            [3] => 500
            [4] => 380
        )

    [item_qty] => Array
        (
            [0] => 4
            [1] => 2
            [2] => 3
            [3] => 2
            [4] => 3
        )

    [item_price] => Array
        (
            [0] => 1800
            [1] => 1400
            [2] => 1800
            [3] => 1700
            [4] => 1400
        )

    [price] => Array
        (
            [0] => 7200
            [1] => 2800
            [2] => 5400
            [3] => 3400
            [4] => 4200
        )

[total_price] => ₦23,000.00
[send] => Complete Shopping
)

and error is: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '799988' for key 'trans_id''
but the first item is inserted into the database

Thanks

Barand · June 16, 2019

Then you have a primary key or unique key constraint on "trans_id" in your shopping table, which seems an odd thing to do as it appears that you are extracting groups of product items with the same trans_id ( $_SESSION['trans_id'] ) from your temporary table to insert into the shopping table, all with that same trans_id..

I-AM-OBODO · June 16, 2019

3 hours ago, Barand said:

Then you have a primary key or unique key constraint on "trans_id" in your shopping table, which seems an odd thing to do as it appears that you are extracting groups of product items with the same trans_id ( $_SESSION['trans_id'] ) from your temporary table to insert into the shopping table, all with that same trans_id..

I cant seem to locate trans_id and the error is reporting as duplicate should be trans_ref and not trans_id. however, the trans_ref is the reference for the transaction. since we can have multiple items in a single transaction/session.

thanks

Barand · June 17, 2019

Post your shopping table structure. (It should look something like this...)

mysql> SHOW CREATE TABLE shopping\G;
*************************** 1. row ***************************
       Table: shopping
Create Table: CREATE TABLE `shopping` (
  `shopping_id` int(11) NOT NULL AUTO_INCREMENT,
  `trans_ref` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `product_name` varchar(30) DEFAULT NULL,
  `item_type` int(11) DEFAULT NULL,
  `item_size` varchar(10) DEFAULT NULL,
  `item_qty` int(11) DEFAULT NULL,
  `item_price` decimal(10,2) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`shopping_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I-AM-OBODO · June 18, 2019

On 6/16/2019 at 1:45 PM, Barand said:
Just a comment on your tables.

Data items like product_name, category, price are properties of the product and should, therefore, only be in the product table. The shopping table should hold just the product_id. (Read up on "data normalization")
+-----------------+
|  product        |
+-----------------+
| product_id      |-------+
| product_name    |       |
| category        |       |         +------------------+
| size            |       |         |  shopping        |
| price           |       |         +------------------+
+-----------------+       |         |  shopping_id     |
                          |         |  trans_ref       |
                          +--------<|  product_id      |
                                    |  qty             |
                                    |  date            |
                                    +------------------+

Thank you very much. All seem to be working well for now. I had to delete the table and start all over again. The form version is working well.

I-AM-OBODO · June 18, 2019

On 6/16/2019 at 7:45 PM, Barand said:

You might have read it but you totally ignored it.

Good bye.

Hello,
I tried doing some deduction from another table but its giving me Array to string conversion

	$update = $pdo->prepare("
UPDATE stocks
SET item_qty = item_qty - '$_POST[item_qty]'
WHERE category = '$category'
AND product_name = '$_POST[product_name]'
AND item_type = '$_POST[item_type]'
AND item_size = '$_POST[item_size]'
");
$update->execute();

Barand · June 18, 2019

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            |
                                    +------------------+

Sign In

Insert into database at all at once

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information