Insert array into database using PHP and SQL

Question

I’m trying to insert values from a array inside the database using PHP, but I’m having difficulty in the applied method. It’s the first time I use arrays for that reason.

My current code is:

<input type='text' id="productName[]" name="productName[]">

<input type='text' id="quantity[]" name="quantity[]">

include_once("connection.php");

 $name = $_POST['productName'];
 $quantity = $_POST['quantity'];

$name = '"' . implode('","', $name) . '"';
$quantity = '"' . implode('","', $quantity) . '"';

$sql = "SELECT * FROM product WHERE name IN ($name)";
$result = mysqli_query($conn_app, $sql);
if (mysqli_affected_rows($conn_app) > 0){
    while($row = mysqli_fetch_assoc($result)){
        $itemId = $row['product_id'];
    };

$insert = "INSERT INTO item_box (quantity,product_id) VALUES ('$quantity','$itemId')";
$result = mysqli_query($conn_app, $insert);

I need each product and its quantity to be entered in the database. For example:

INSERT INTO item_box (quantity, product_id)VALUES (13,2),(23,3),(40,3);

Currently the code points error in the syntax of SQL, for it is being returned thus:

INSERT INTO item_box (quantity,product_id) VALUES ("20","30","40","50","2")

Answer 1

From what I saw, you make a query in the product table to recover the id, then add the item in the item_box table .

Since your inputs are arrays, a way to add to the item_box table would be as follows:

$contador = count($_POST["productName"]);
for ($x = 0; $x < $contador; $x++){
$produto = $_POST["productName"][$x];
$quantidade = $_POST["quantity"][$x];
$sql = "SELECT * FROM product WHERE name = '$produto'";
$result = mysqli_query($conn_app, $sql);
if (mysqli_affected_rows($conn_app) > 0){
while($row = mysqli_fetch_assoc($result)){
$itemId = $row['product_id'];
$insert = "INSERT INTO item_box (quantity,product_id) VALUES ('$quantidade','$itemId')";
$result = mysqli_query($conn_app, $insert);
};
}
}