how to Insert multiple arrays with multiple rows into MySQL using PHP PDO

Question

I am trying to insert multiple arrays into MySql database using PHP PDO. Its inserting duplicated row and it is not inserting the way I required. below is my code

HTML

             <div class="col-md-4">
                    <div class="form-group">    
                        <label class="col-form-label"> Enter Product Name</label>
                        <input type="text" class="form-control" name="pname[]" placeholder="Product Name"/>
                    </div>
                </div> 

                <div class="col-md-4">
                    <div class="form-group">    
                        <label class="col-form-label"> No. of Pieces</label>
                        <input type="text" class="form-control" name="pcount[]" placeholder="No.Of Items"/>
                    </div>
                </div> 

                <div class="col-md-3">
                    <div class="form-group">    
                        <label class="col-form-label"> Estimated Amount</label>
                        <input type="text" class="form-control" name="estamount[]" placeholder="Estimated Amount of Each"/>
                    </div>
                    <p class="pull-left">Amount: &nbsp;<div class="Amount"></div></p>
                </div>

And PHP Code is

$totalamount=0;
        foreach($_POST['pname'] as $proname){

            foreach($_POST['pcount'] as $quantity){

                foreach($_POST['estamount'] as $estamount){

                    $totalamount = $quantity*$estamount;

                    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    $sql = "INSERT INTO `order`(custname, contact, product_name, quantity, est_amount, advance_paid, delivery_period, date, status, total_amount, orderid) VALUES(:custname, :contact, :product_name, :quantity, :est_amount, :advance_paid, :delivery_period, :date, :status, :total_amount, :orderid)";
                    $query=$dbh->prepare($sql);
                    $query->bindParam(':custname',$custmorname,PDO::PARAM_STR);
                    $query->bindParam(':contact',$contact,PDO::PARAM_STR);
                    $query->bindParam(':product_name',$proname,PDO::PARAM_STR);
                    $query->bindParam(':quantity',$quantity,PDO::PARAM_STR);
                    $query->bindParam(':est_amount',$estamount,PDO::PARAM_STR);
                    $query->bindParam(':advance_paid',$advancepaid,PDO::PARAM_STR);
                    $query->bindParam(':delivery_period',$delevery,PDO::PARAM_STR);
                    $query->bindParam(':date',$date,PDO::PARAM_STR);
                    $query->bindParam(':status',$status,PDO::PARAM_STR);
                    $query->bindParam(':orderid',$orderid,PDO::PARAM_STR);
                    $query->bindParam(':total_amount',$totalamount,PDO::PARAM_STR);
                    $query->execute();
                    }
              }
        }

I would like to insert $proname , $quantity , $est_amount in one record and its should insert multiple recodes as per inserted values

Possible duplicate of [PDO Prepared Inserts multiple rows in single query](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) — Nico Haase, Aug 19 '18 at 08:19
please read my question carefully. I have multiple arrays with multiple rows — Asesha George, Aug 19 '18 at 08:25
Ahd that makes it a non-duplicate because....??? You've only written that "it is not inserting the way I required", but not what exactly you require — Nico Haase, Aug 19 '18 at 08:30
@NicoHaase If you feel that it's a possible duplicate, it's best to "flag" it as one instead of typing it out as a comment. When it gets flagged, it shows up somewhere that others may pickup on voting to close as such also. Also, if I or someone else who can hammer it with it in one go, then that too will show your name on it, rather than just the person who hammered it. Just thought I'd let you know how voting to close works. Why I think you did that would probably be that you can't "vote" to close as a duplicate. Right you are, *but* you can "flag" as a duplicate though. — Funk Forty Niner, Aug 19 '18 at 12:00
Well, thats what I did: flag it. That comment was written automatically — Nico Haase, Aug 19 '18 at 12:02
@NicoHaase You should ping me as I did for you. I only saw your comment because I'm still in here. I find it odd that it doesn't already show a "1" under the "close" option though. Possible bug? — Funk Forty Niner, Aug 19 '18 at 12:05

score 1 · Accepted Answer · answered Aug 19 '18 at 08:28

As you have a nested for loop it will definitely lead for duplicate records. Let me make a quick correction.

$totalamount=0;
for ($i = 0; $i < count($_POST['pname']); $i++){
    $proname = $_POST['pname'][$i];
    $quantity = $_POST['pcount'][$i];
    $estamount = $_POST['estamount'][$i];
    $totalamount = $quantity*$estamount;

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO `order`(custname, contact, product_name, quantity, est_amount, advance_paid, delivery_period, date, status, total_amount, orderid) VALUES(:custname, :contact, :product_name, :quantity, :est_amount, :advance_paid, :delivery_period, :date, :status, :total_amount, :orderid)";
    $query=$dbh->prepare($sql);
    $query->bindParam(':custname',$custmorname,PDO::PARAM_STR);
    $query->bindParam(':contact',$contact,PDO::PARAM_STR);
    $query->bindParam(':product_name',$proname,PDO::PARAM_STR);
    $query->bindParam(':quantity',$quantity,PDO::PARAM_STR);
    $query->bindParam(':est_amount',$estamount,PDO::PARAM_STR);
    $query->bindParam(':advance_paid',$advancepaid,PDO::PARAM_STR);
    $query->bindParam(':delivery_period',$delevery,PDO::PARAM_STR);
    $query->bindParam(':date',$date,PDO::PARAM_STR);
    $query->bindParam(':status',$status,PDO::PARAM_STR);
    $query->bindParam(':orderid',$orderid,PDO::PARAM_STR);
    $query->bindParam(':total_amount',$totalamount,PDO::PARAM_STR);
    $query->execute();


}

how to Insert multiple arrays with multiple rows into MySQL using PHP PDO

1 Answers1