How do I submit multiple items from a table into a database?

Question

I have a table and i'm trying to add multiple items from said table to a database. The data is being stored in a php array and then inserted into my database. I have multiple table rows that need to be inserted but only the last item in the table is the item that gets inserted into the database and I need everything from the table to go into the database.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "salestest";
    
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

 $sku = $_POST['txtSKULater'];
$productName = $_POST['txtProductNameLater'];
$quantity = $_POST['txtQuantityLater'];
$retailPrice = $_POST['txtRetailPriceLater'];
$listPrice = $_POST['txtListPrice555Later'];
$total = $_POST['txtTotalLater'];

$sql = "insert into orders( 
      sku, product_name, quantity, retail_price, list_price, total) values(
          '$sku', '$productName', '$quantity', 
          '$retailPrice', '$listPrice', '$total')";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

HTML:

<table id="table">

  <tr>
    <th><label>SKU:</label></th>
    <th><label>Retail Price:</label></th>
    <th><label>List Price</label></th>
    <th><label>Product Name</label></th>
    <th><label>Quantity</label></th>
  </tr>
  <tr>
    <td><input type="text" name="txtSKU" value='123'></td>
    <td><input type="text" name="txtRetail" value='11.75'></td>
    <td><input type="text" name="txtList" value='4.50'></td>
    <td><input type="text" name="txtProductName" value="product1"></td>
    <td><input type="text" name="txtQuantity" value='1'></td>
  </tr> 
  <tr> 
    <td><input type="text" name="txtSKU" value='456'></td>
    <td><input type="text" name="txtRetail" value='12.25'></td>
    <td><input type="text" name="txtList" value='9.40'></td>
    <td><input type="text" name="txtProductName" value='product2'></td>
    <td><input type="text" name="txtQuantity" value='1'></td>
  </tr>
</table>

The vardump of the post array that is being added to the database looks similar this:

array(12) { ["txtCustomerNameLater"]=> string(13) "101 Auto Body" ["txtPhoneLater"]=> string(12) 
"510-524-4857" ["txtEmailLater"]=> string(20) "henrykseng@yahoo.com" ["txtRopoLater"]=> string(5) 
"test3" ["txtAddressLater"]=> string(55) "101 Auto Body 5327 Jacuzzi St Ste 3A Richmond, CA 94804" 
["txtTotalLater"]=> string(4) "15.5" ["txtSKULater"]=> string(13) "8620844-0424R" 
["txtRetailPriceLater"]=> string(5) "11.75" ["txtListPrice555Later"]=> string(4) "7.75" 
["txtProductNameLater"]=> string(45) "Stone Guard Right Volvo XC 90 03-14 Rear Door" 
["txtQuantityLater"]=> string(1) "1" ["submitOrder1"]=> string(0) "" } New record created successfully

You might need to change your HTML by using [forms with PHP](https://www.php.net/manual/en/tutorial.forms.php) — Rojo, Jan 11 '21 at 22:37
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jan 11 '21 at 22:54
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped input values. — ADyson, Jan 11 '21 at 22:54
And **Never** get your web app to login to the database as root. Root can do whatever it likes, so on top of the SQL injection vulnerabilities this just leaves your database an open book for hackers. Instead create a separate user account specifically for this application which has only the permissions it actually _needs_ in order to work properly. Don't even use the root account as a shortcut during development or testing, because you need to test your account permissions as well - otherwise when you go live you might have unexpected errors relating to the user account setup. — ADyson, Jan 11 '21 at 22:55
Thank you for the input, yes this is a dev environment, and hashing passwords, sql injections, and security as a whole are on my to-do list before I go live. I will review the documents you suggested when I get to those steps. — John Taylor, Jan 11 '21 at 23:00
Does this answer your question? [How store multiple language textfield in mysql with php?](https://stackoverflow.com/questions/64910833/how-store-multiple-language-textfield-in-mysql-with-php) — Dharman, Jan 12 '21 at 09:19

score 1 · Accepted Answer · answered Jan 11 '21 at 22:52

<table id="table">
  <tr>
    <th><label>SKU:</label></th>
    <th><label>Retail Price:</label></th>
    <th><label>List Price</label></th>
    <th><label>Product Name</label></th>
    <th><label>Quantity</label></th>
  </tr>
  <tr>
    <td><input type="text" name="txtSKU[]" value='123'></td>
    <td><input type="text" name="txtRetail[]" value='11.75'></td>
    <td><input type="text" name="txtList[]" value='4.50'></td>
    <td><input type="text" name="txtProductName[]" value="product1"></td>
    <td><input type="text" name="txtQuantity[]" value='1'></td>
  </tr> 
  <tr> 
    <td><input type="text" name="txtSKU[]" value='456'></td>
    <td><input type="text" name="txtRetail[]" value='12.25'></td>
    <td><input type="text" name="txtList[]" value='9.40'></td>
    <td><input type="text" name="txtProductName[]" value='product2'></td>
    <td><input type="text" name="txtQuantity[]" value='1'></td>
  </tr>
</table>

You have to pass the values as array by adding [] at the end of name in each field and then catch the values as array with php, like this:

$counts = $sku;
foreach($counts as $index=>$dat){
    $sql = "insert into orders(sku, product_name, quantity, retail_price, list_price, total) values('".$sku[$index]."', '".$productName[$index]."', '".$quantity[$index]."', '".$retailPrice[$index]."', '".$listPrice[$index]."', '".$total[$index]."')";
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

This seems to be working for the most part, except when i check what was inserted into my database it just says "array" for sku, listprice, retailprice, and productname. any clues as to why or how I can update my SQL database in order to be able to read the entries? — John Taylor, Jan 11 '21 at 23:25
for example the sql being entered is: "insert into orders(customer_name, phone, email, ropo, address, sku, product_name, quantity, retail_price, list_price, total,timestamp, user) values( '101 Auto Body', '510-524-4857', 'henrykseng@yahoo.com', 'asdf', '101 Auto Body 5327 Jacuzzi St Ste 3A Richmond, CA 94804', 'Array', 'Array', 'Array', 'Array', 'Array', '52.84', '2021-01-12 01:21:31', 'developer')" — John Taylor, Jan 12 '21 at 00:22

How do I submit multiple items from a table into a database?

1 Answers1