php insert multiple rows in MYSQL database

Question

I have a HTML form that i populate from database with a "foreach" loop, so the fields in the name have the same name. The data that i want to post back into the database comes from variables that are arrays. At this moment when i insert into database, i get the right TIMES of insert, only the values that are inserted (in this case 4 times) are the same values, as in the last row of the HTML form.

I have spent days searching the internet, and rebuilding code, but cant find the solution. I tried implode, even extract values from the VAR, but am sure i am on the wrong track. I am a beginner, just asking to be put back on the right track. Thanks so much...

<?php   
        if(isset($_GET['submit']))
        {    
        $client_id = ($value->ID);
        $qry = "INSERT INTO salesorder (client_id)
                VALUES ('$client_id')";
        $result=mysqli_query($mysql,$qry) or die(mysqli_error($mysql));
        $order_id =  mysqli_insert_id($mysql);
        foreach ( $results as $result ) :
                                      
                $food_id = $_GET['foodid']; print_r($food_id);
                $qty = $_GET['qty'];
                $qry="INSERT INTO orderline (order_id, food_id, qty) VALUES ($order_id, '$food_id', '$qty') ";
                $result=mysqli_query($mysql,$qry) ;

                endforeach; 
    }               

    ?>

    <tbody>
            <form action="" method="GET">
        <?php foreach ( $results as $result ) : ?>
            <tr><td><input name="qty" size="2" type="number"></td>
            <td><?php print($result->food_type); ?></td>
            <td><input name="foodid[]" size="4" type="number" value=<?php print($result->food_id); ?>></td><tr>
            
        <?php
            endforeach;
        ?>
            </tbody>

this is what the form looks like...

So the form is first dynamic loaded, from the database. could be 2 lines, or 50 lines... but my problem is, after the client fills out qty where he wants to place order off, to "read" the whole form, and load it back into the database

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jul 15 '20 at 10:58
What does your HTML look like? could you share that as well? — mw509, Jul 15 '20 at 10:59
I would expect `$_GET['foodid']` and `$_GET['qty']` to always return the same values. If they were arrays then you'd be accessing them with an index. What values are you sending to the server? Where are the arrays that you mention? — David, Jul 15 '20 at 11:03
thanks all, yes i know the code is wide open, i use this merely on my localhost for testing and will adapt to security before we go live. — Harald van Haaster, Jul 15 '20 at 12:21
You really should start by learning prepared statements. There is no reason to write this code, delete it, and write it again. — Dharman, Jul 15 '20 at 12:22
Please [edit] your post to include any additional information you have to your question. Avoid adding this in the comments, as they are harder to read and can be deleted easier. The edit button for your post is just below the post's tags. — Dharman, Jul 15 '20 at 12:22
this is what rhe form looks like. the problem is not that i cant retrieve the data from the form after input from client (qty), but to get it in a right way into the database. — Harald van Haaster, Jul 15 '20 at 12:23
If `$_GET['foodid']` is an array then you need to iterate it or use keys to access the elements. — Dharman, Jul 15 '20 at 12:27
please can you explain that with an example, as that is i guess my problem — Harald van Haaster, Jul 15 '20 at 12:29

score 1 · Accepted Answer · answered Jul 15 '20 at 12:38

If you are getting an array from your HTML form then you need to loop on this array and insert each row separately into DB. To do this you need to use prepared statement and a loop.

if (isset($_GET['submit'])) {
    $client_id = $value->ID; // Wherever this value comes from...

    // Insert new sales order
    $stmt = $mysql->prepare('INSERT INTO salesorder (client_id) VALUES (?)');
    $stmt->bind_param('s', $client_id);
    $stmt->execute();
    $stmt->store_result();

    $order_id = $mysql->insert_id;

    // prepare the SQL statement
    $orderline_stmt = $mysql->prepare('INSERT INTO orderline (order_id, food_id, qty) VALUES (?,?,?)');

    // loop on each element from HTML form 
    // i.e. <input name="foodid[]" >
    foreach ($_GET['foodid'] as $key => $food_id) {
        $qty = $_GET['qty']; // should this be an array too?
        // $qty = $_GET['qty'][$key]; <-- if it's also an array

        $orderline_stmt->bind_param('sss', $order_id, $food_id, $qty);
        $orderline_stmt->execute();
    }
}

And it works! Thank you so much Dharman, really appreciate. Have been struggling for a couple of days, but now that i read this, it makes so much sense... — Harald van Haaster, Jul 15 '20 at 13:23

score 0 · Answer 2 · edited Jul 15 '20 at 11:36

Am not sure what your HTML looks like but having the same name for all your inputs does not neccessarily mean they have become an array. you will need to define them as array in HTML using []. Below is an example

HTML

<form method="post" name="myform">
<input type="text" name="array[]" Value="101"/>
<input type="text" name="array[]" Value="102"/>
<input type="text" name="array[]" Value="103"/>
<input type="text" name="array[]" Value="104"/>
<input type="submit" name="submit" Value="submit"/>
</form>

PHP

if(isset($_POST['submit'])){

foreach($_POST['array'] as $myarray) {

    echo $myarray.'<br>';

}

this should get you the unique values from each input and then you do what you want with it.

Here is a link to several other solutions that could help: Post text box array in PHP

php insert multiple rows in MYSQL database

2 Answers2

Linked

Related