Insert array into MySQL database from form

Question

I have tried inserting array data from form into my database but am getting only the last row:

I want to insert these array into Database:

ProductID Quantity
2            10
3            5
4            8

but I am seeing only this data in my Database:

ProductID Quantity
4            8

See my code below:

define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME','store');
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
// Check connection
if (mysqli_connect_errno())
{
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$productID=$_POST['product_id'];
$IssueQty=$_POST['quantity'];

$inventory=mysqli_query($con,"insert into inventory_log(productID, IssueQty)
values('$productID', '$IssueQty')");

You are inserting only one set of row then how you can expect the DB to show 3 rows? — Manish Pareek, Sep 26 '20 at 12:25
can you insert a single row? if so, can you run exactly the same code in a loop? — Your Common Sense, Sep 26 '20 at 12:25
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 26 '20 at 13:03

Manish Pareek · Answer 1 · 2020-09-26T12:55:54.037

2

An example of multiple INSERT:

$statement = $con->prepare("INSERT INTO inventory_log (productID, IssueQty)
VALUES (?,?)");
foreach ($_POST as $row) {
    $statement->bind_param($row['product_id'],$row['quantity']);
    $statement->execute();
}

edited Sep 26 '20 at 12:55

answered Sep 26 '20 at 12:33

Manish Pareek

409
5
12

thanks mPareek for your help. just wondering how to replace this: (?,?) – vascoder Sep 26 '20 at 13:03
What do you mean replace `(?,?)`? These are placeholders and that is how the SQL should look like. You must bind the parameters inside the loop – Dharman Sep 26 '20 at 13:04
You don't need to replace it. They will be automatically replaced by `bind_param()`. This is known as prepared statement and is a more secure way to write queries. See this [mysqli_prepare](https://www.php.net/manual/en/mysqli.prepare.php) – Manish Pareek Sep 26 '20 at 13:06

Insert array into MySQL database from form

1 Answers1