Jump to content
I-AM-OBODO

Insert into database at all at once

Recommended Posts

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

sample.jpg

Share this post


Link to post
Share on other sites

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

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

?

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Barand
code correction ($db to $pdo)

Share this post


Link to post
Share on other sites

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

 

Share this post


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

Share this post


Link to post
Share on other sites

Can you post the output you are now getting from

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

 

Share this post


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

Share this post


Link to post
Share on other sites

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[]" )

 

Share this post


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

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


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

Share this post


Link to post
Share on other sites

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

 

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites

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

 

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.