Syn Syn - 1 month ago 10
MySQL Question

What is the best way to insert multiple form arrays into a MySQL database?

I have written a PHP script that automatically generates and populates an HTML form.

The HTML output is something like this:

<form action="process.php" method="post">
<input type="hidden" name="product_name[]" value="product-1">
<input type="hidden" name="product_price[]" value="0.99">

<input type="hidden" name="product_name[]" value="product-2">
<input type="hidden" name="product_price[]" value="1.39">

<input type="submit" name="submit" value="submit">
</form>


My MySQL Database format is:
product_name, product_price


What method should I use in
process.php
to insert this information into a MySQL database?

If it were just the one array (for example,
product_name
) then I'd write the following code:

$product_name = $_POST['product_name'];

foreach ($product_name as $n){
$sql = "INSERT INTO orders (product_name)
VALUES ('$n')";
$conn->query($sql)
}


But of course this foreach approach does not work with multiple arrays. I thought about and tried populating
product_name
first, and then creating foreach loop to UPDATE the
product_price
row, but this isn't working for me and feels very messy. I'm sure this isn't the best way to do this.

What would be the correct way to handle this?

Answer

You use one of the post arrays to control the loop and then you use the index gained from the foreach i.e. $i to access any/all the other arrays

PDO

$sql = "INSERT INTO orders (product_name, price) VALUES (:name,:price)";
$stmt = $conn->prepare($sql);

foreach ($_POST['product_name'] as $i => $n){
    $stmt->execute( array(':name'=> $n,
                          ':price'=>$_POST['product_price'][$i]) 
                        )
                );
}

MYSQLI

$sql = "INSERT INTO orders (product_name, price, qty) VALUES (?,?,?)";
$stmt = $conn->prepare($sql);

foreach ($_POST['product_name'] as $i => $n){
    $stmt->bind_param("sdi", 
                        $n, 
                        $_POST['product_price'][$i]
                        $_POST['product_qty'][$i]
                    );
    $stmt->execute();
}
Comments