Mysql : Table multiple rows Insert into one sql row

Question

Currently I'm creating a software, where i can create multiple row in a table.

But the why I wrote PHP code, it create multiple rows into SQL but i need to create one row against one invoice number.

Html (auto generate using ajax):

<tr>
                            <td><input class="form-control" type="text" name="product_name[]" value="'.$proData['name'].'"></td>
                            <td><input class="form-control qnt'.$proData['id'].'" min="0" step="any" type="number" name="qnt[]" placeholder="Quantity"></td>
                            <td><input class="form-control unit_price'.$proData['id'].'" type="number" min="0" step="any" name="unit_price[]" placeholder="Unit Price"></td>
                            <td><input class="form-control pack_size'.$proData['id'].'" type="number" min="0" step="any" name="pack_size[]" placeholder="Pack Size"></td>
                            <td><input class="form-control unit_pack'.$proData['id'].'" type="number" min="0" step="any" name="unit_pack[]" placeholder="Unit Pack (jar/Drum)"></td>
                            <td><input class="form-control total_kg'.$proData['id'].'" type="number" name="total_kg[]" placeholder="Total Kg/s" value="" readonly></td>
                            <td><input class="form-control total_price'.$proData['id'].'" type="number" name="total_price[]" placeholder="Total Price &#2547;" value="" readonly></td>
                            <td><button type="button" class="rowDelete btn btn-danger"><i class="icon-trash"></i></button></td>
                        </tr>

php Code :

public function add(){
        // Form Inputs
        $invoice = $this->input->post('invoice');
        $date = $this->input->post('date');
        $created_by = $this->input->post('created_by');
        $products = $this->input->post('products');
        $qnt = $this->input->post('qnt[]');
        $unit_price = $this->input->post('unit_price[]');
        $pack_size = $this->input->post('pack_size[]');
        $unit_pack = $this->input->post('unit_pack[]');
        $total_kg = $this->input->post('total_kg[]');
        $total_price = $this->input->post('total_price[]');
        $payed = $this->input->post('payed');
        $price_less = $this->input->post('price_less');
        $price_discount = $this->input->post('price_discount');
        $price_due = $this->input->post('price_due');
        $grand_total = $this->input->post('grand_total');
        $payMethod = $this->input->post('payMethod');
        $bank_name = $this->input->post('bank_name');
        $cheque_no = $this->input->post('cheque_no');
        $bank_acc_no = $this->input->post('bank_acc_no');

        for($i = 0; $i < count($qnt); $i++){
            $purchaseData = array(
                'invoice_no' => $invoice,
                'date' => $date,
                'product_id' => $products,
                'create_date' => $created_by,
                'qnt' => $qnt[$i],
                'unit_price' => $unit_price,
                'pack_size' => $pack_size,
                'unit_pack' => $unit_pack,
                'total_kg' => $total_kg,
                'total_price' => $total_price,
                'payed' => $payed,
                'price_less' => $price_less,
                'price_discount' => $price_discount,
                'price_due' => $price_due,
                'grand_total' => $payMethod,
                'payMethod' => $grand_total,
                'bank_name' => $bank_name,
                'cheque_no' => $cheque_no,
                'bank_acc_no' => $bank_acc_no
            );

            $PurchaseQuery = $this->db->insert('purchase', $purchaseData);

            if($PurchaseQuery){
                $purchaseAdded = "Product Purchase Add.";
                $this->session->set_flashdata('purchaseAdded', $purchaseAdded);
                redirect('Purchase');
            }
        }
    }

Please Help. I'm Using CI.

yes because you are using a for loop to append the data to an array? the qnt must be greater than 1 thus multiple rows are being inserted — Exprator
– Exprator, Commented May 29, 2017 at 9:21
You want to insert multiple data ?Haven't understood the questions to be honest.If you need multiple data use bulk import.Simply INSERT INTO example(ex1,ex1) VALUES('123','123'),('123','123')..... — DaAmidza
– DaAmidza, Commented May 29, 2017 at 9:29
@Arslan.H if i simply insert i get this "Message: Array to string conversion" error bcoz my table rows are auto generate by ajax every time i select the product. my question is "I've one invoice no & i need to insert all data into one sql row. I mean if purchase invoice same then all product insert same row" — Maruf Ahmed
– Maruf Ahmed, Commented May 29, 2017 at 9:54
can you tell me clearly what you want to do? if you want one row to insert, remove the for loop and then normally insert as you are doing it — Exprator
– Exprator, Commented May 29, 2017 at 9:57

Swarna Sekhar Dhar · Accepted Answer · 2017-05-29 11:19:28Z

2

the requirement i quit different . what you can do is ? instead of looping through items . i mean

count($qnt)

just json_encode() the multiple items

or change your pattern and normalize you tables
to put order and items of orders in 2 different table

answered May 29, 2017 at 11:19

Swarna Sekhar Dhar

5461 gold badge8 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

美丽美丽花 · Accepted Answer · 2017-05-29 09:23:44Z

1

Please do this.

public function add(){
        // Form Inputs
        $invoice = $this->input->post('invoice');
        $date = $this->input->post('date');
        $created_by = $this->input->post('created_by');
        $products = $this->input->post('products');
        $qnt = $this->input->post('qnt[]');
        $unit_price = $this->input->post('unit_price[]');
        $pack_size = $this->input->post('pack_size[]');
        $unit_pack = $this->input->post('unit_pack[]');
        $total_kg = $this->input->post('total_kg[]');
        $total_price = $this->input->post('total_price[]');
        $payed = $this->input->post('payed');
        $price_less = $this->input->post('price_less');
        $price_discount = $this->input->post('price_discount');
        $price_due = $this->input->post('price_due');
        $grand_total = $this->input->post('grand_total');
        $payMethod = $this->input->post('payMethod');
        $bank_name = $this->input->post('bank_name');
        $cheque_no = $this->input->post('cheque_no');
        $bank_acc_no = $this->input->post('bank_acc_no');

            $purchaseData = array(
                'invoice_no' => $invoice,
                'date' => $date,
                'product_id' => $products,
                'create_date' => $created_by,
                'qnt' => $qnt[$i],
                'unit_price' => $unit_price,
                'pack_size' => $pack_size,
                'unit_pack' => $unit_pack,
                'total_kg' => $total_kg,
                'total_price' => $total_price,
                'payed' => $payed,
                'price_less' => $price_less,
                'price_discount' => $price_discount,
                'price_due' => $price_due,
                'grand_total' => $payMethod,
                'payMethod' => $grand_total,
                'bank_name' => $bank_name,
                'cheque_no' => $cheque_no,
                'bank_acc_no' => $bank_acc_no
            );

            $PurchaseQuery = $this->db->insert('purchase', $purchaseData);

            if($PurchaseQuery){
                $purchaseAdded = "Product Purchase Add.";
                $this->session->set_flashdata('purchaseAdded', $purchaseAdded);
                redirect('Purchase');
            }
    }

and let me know the result.

answered May 29, 2017 at 9:23

美丽美丽花

2121 gold badge4 silver badges16 bronze badges

4 Comments

Maruf Ahmed Over a year ago

Thank You for your ans. But I'm getting error "Message: Array to string conversion". My html input filed name are same "<input class="form-control qnt" min="0" step="any" type="number" name="qnt[]" placeholder="Quantity">" (table row auto generate using ajax).

美丽美丽花 Over a year ago

can you share your web site url?

美丽美丽花 Over a year ago

hi.pls share your website url.

Maruf Ahmed Over a year ago

@美美花 thank you for your help. #Swarna gave me the right answer.

Collectives™ on Stack Overflow

Mysql : Table multiple rows Insert into one sql row

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related