How to insert multiple dynamic rows into the database

Question

I have a multiple row dynamic table that I created using php and jQuery. Here's the link to view the table.

Everything is working fine except when I insert the data into the database, the serial numbers do not save sequentially. My insert queries are as below:

for($i = 0; $i < count($_POST['C_Objectives']); $i++)
{
    $sql = "INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,Measures,Targets,subtotal,Corporate_Objective,Row_Number,ID) Values ('$formno','||<==','==','==','".$_POST['SubTotals'][$i]."','".$_POST['C_Objectives'][$i]."','".$_POST['SNo'][$i]."','$statement')";
    $stmt = sqlsrv_query($conn, $sql);
    if($stmt === false)
        die(print_r(sqlsrv_errors(), true));
    else
        echo " ";
}

for($i = 0; $i < count($_POST['Measures']); $i++)
{
    $sql = "INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,Measures,Targets,Weightage,Row_Number,target_date,ID) VALUES ('$formno','".$_POST['Objectives'][$i]."','".$_POST['Measures'][$i]."','".$_POST['Achievement'][$i]."','".$_POST['Weightage_Target'][$i]."','".$_POST['SNo'][$i]."','".$_POST['Date_Target'][$i]."','$statement')"; 
    $stmt = sqlsrv_query($conn, $sql);
    if($stmt === false)
        die(print_r(sqlsrv_errors(), true));
    else
        echo " ";
}

The serial number is saved in the column Row_Number, using $_POST['SNo'][$i]. Is it possible to save both of the dynamic rows using 1 insert query so that the serial numbers are saved sequentially?

This is the $_POST array result:

    [Row_Number] => Array
        (
            [0] => 1
            [1] => 2
        )

    [C_Objectives] => Array
        (
            [0] => A
            [1] => B
        )

    [Objectives] => Array
        (
            [0] => a1
            [1] => a4
            [2] => a7
            [3] => b1
        )

    [Measures] => Array
        (
            [0] => a2
            [1] => a5
            [2] => a8
            [3] => b2
        )

    [Achievement] => Array
        (
            [0] => a3
            [1] => a6
            [2] => a9
            [3] => b3
        )

    [Date_Target] => Array
        (
            [0] => 2016-09-09
            [1] => 2016-09-09
            [2] => 2016-09-09
            [3] => 2016-09-09
        )

    [Weightage_Target] => Array
        (
            [0] => 25
            [1] => 25
            [2] => 25
            [3] => 25
        )

    [SNo] => Array
        (
            [0] => 3
            [1] => 4
            [2] => 5
            [3] => 6
        )

    [SubTotals] => Array
        (
            [0] => 75
            [1] => 25
        )

    [GrandTotal] => 100
)

I've also tried making the column auto-increment, but yet doesn't save the data in the same order as it is entered in the front end.

since `$formno` is used for Serial_Number. so always same value will be saved. — Alive to die - Anant, Sep 06 '16 at 10:21
The serial number is saved in Row_Number as `$_POST['SNo'][$i]` — SR1092, Sep 06 '16 at 10:22
Make one insert query using concatenation and call it at a time. — Amy, Sep 06 '16 at 10:23
what i say that before any processing just check POST array is coming or not? and if coming then print that POST array value and show us the output, — Alive to die - Anant, Sep 06 '16 at 11:26
If I am understanding correctly, you should modify the table to make the serial number field an auto-incrementing number. Then you don't need to handle anything when inserting the records. Also it is unclear why you need to do two different inserts into the table? — Shawn, Sep 08 '16 at 21:20
@Shawn I am using 2 different inserts because there rows that are dynamic / can be repeated. — SR1092, Sep 09 '16 at 05:47

ICE · Accepted Answer · 2018-11-29T14:11:03.860

Your inserting has performance issue. Please change your way for inserting to the database. You can do all of them in one query. Even if you have 20 loop for first "for" and 20 loop for 2nd "for".

Answer to What you asked

If you want to insert by $_POST['SNo'] order, change this line

for($i = 0; $i < count($_POST['C_Objectives']); $i++)

to the

foreach($_POST['SNo'] as $i)

If you need multiple insert at once, just do this:

INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,...)
VALUES (Value1,Value2,...), (Value1,Value2,...)

This is What you MUST do

In your code, you did the same query in 6 queries. It can even be more than 6 with more $_POST['Measures'] or $_POST['C_Objectives'] array length. You need to Put them in one query and when you don't need to set the value, just set it to the column default value. for example NULL

Something like this:

//first we create $values array. it contains all values that you need to insert to the db
$values = array();
$j=0;

for($i = 0; $i < count($_POST['C_Objectives']); $i++){
    $values[$j]['Serial_Number'] = $formno;
    $values[$j]['Objectives'] = '||<==';
    //and fill others here
    //fill all cols that you wrote inside your query with the correct order
    $j++;
}

for($i = 0; $i < count($_POST['Measures']); $i++){
    $values[$j]['Serial_Number'] = $formno;
    $values[$j]['Objectives'] = $_POST['Objectives'][$i];
    //and fill others here
    //fill all cols that you wrote inside your query with the correct order
    $j++;
}

//now create (value1,value2,...),(value1,value2,...),...
$query = NULL;
foreach($values as $value){
    $tmp = NULL;
    foreach($value as $v){
        $tmp .= ($v=='')? 'NULL,' : "'$v',";
    }
    $tmp = rtrim($tmp,',');
    $query .= "($tmp),";
}
$query = rtrim($query,',');

//Now Insert
$sql = "INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,...) VALUES $query";

In this example I just showed you how to do it. Remember, you must check $v and prepare it by your column type. check if $_POST[KEY] is set and it's array. Don't insert to the database if $query is empty.

Very Important about your codes

If this is not your original code there is no problem but if it is, please change the way you are using $_POST inside your query. It has very low security. at least you need to validate them before using it.

@Asish use php's `error_log` or `echo $query;` before $sql to see that. — ICE, Feb 17 '21 at 12:51
No, I meant if you could have written that in the answer. It's difficult to write this code in another project and check it out. — Asish, Feb 18 '21 at 05:48

kaustubh · Answer 2 · 2016-09-09T08:23:13.720

Yes you can able to insert in single insert query.

        $arrMeasuresInsData = array();
        for($i = 0; $i < count($_POST['C_Objectives']); $i++) {
          $sql = "INSERT INTO Appraisal_Objectives (Serial_Number, Objectives, Measures, Targets, subtotal, Corporate_Objective, Row_Number, ID, Weightagen, target_date) 
          Values ('$formno',
                    '||<==',
                    '==',
                    '==',
                    '".$_POST['SubTotals'][$i]."',
                    '".$_POST['C_Objectives'][$i]."',
                    '".$_POST['SNo'][$i]."',
                    '$statement',
                    '',
                    '')";

            if(!empty($_POST['Measures'][$i])) {
                $arrMeasuresInsData[$i] = $_POST['Measures'][$i];
                $sql .= ",('$formno',
                        '".$_POST['Objectives'][$i]."',
                        '".$_POST['Measures'][$i]."',
                        '".$_POST['Achievement'][$i]."',
                        '',
                        '',             
                        '".$_POST['SNo'][$i]."',
                        '".$_POST['Date_Target'][$i]."',                
                        '".$_POST['Weightage_Target'][$i]."',
                        '$statement',)";
            }

          $stmt = sqlsrv_query($conn, $sql);
          if($stmt === false)
          {
            die(print_r(sqlsrv_errors(), true));
          }
          else
          {
            echo " ";
          }
        }

        for($i = 0; $i < count($_POST['Measures']); $i++) {
            if(isset($arrMeasuresInsData[$i])) {
                continue;
            }
          $sql="INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,Measures,Targets,Weightage,Row_Number,target_date,ID) 
                VALUES ('$formno',
                        '".$_POST['Objectives'][$i]."',
                        '".$_POST['Measures'][$i]."',
                        '".$_POST['Achievement'][$i]."',
                        '".$_POST['Weightage_Target'][$i]."',
                        '".$_POST['SNo'][$i]."',
                        '".$_POST['Date_Target'][$i]."',
                        '$statement')"; 
          $stmt = sqlsrv_query($conn, $sql);
          if($stmt === false)
          {
            die(print_r(sqlsrv_errors(), true));
          }
          else
          {
            echo " ";
          }       
       }

This is brilliant, but has a small issue. Only enters the number of records present in `count($_POST['C_Objectives']` — SR1092, Sep 09 '16 at 07:18
I have edited my answer as per you requirement please check and let me know if you have any other doubts.. — kaustubh, Sep 09 '16 at 08:24
Now it's inserting the exact amount of rows, but not in the correct order. See the screenshots in the question. — SR1092, Sep 09 '16 at 08:55
Please give me the exact array which is you are receiving so i can help you well. — kaustubh, Sep 09 '16 at 09:36
Do one thing create multi dimension array based on "C_Objectives" it wil help you to handle the data based on sequence. based on your current array you will not get proper output because you will not get any idea of which data is belongs to which "C_Objectives". — kaustubh, Sep 09 '16 at 13:24

score 0 · Answer 3 · answered Sep 12 '16 at 09:52

I think you are getting your $_POST array wrongly. You have to change the input form and recieve the input some thing like below:

[C_Objectives] => Array
    (
        [Row_Number] => Array
            (
                [title]         =>      'xxx',
                [0]             =>      Array
                    (
                        [0]     =>      Array
                            (
                                [SNo]           =>  2
                                [Objectives]    =>  a1,
                                [Measures]      =>  a2,
                                [Achievement]   =>  a3,
                                [Date_Target]   =>  2016-09-09,
                                [Weightage_Target]  =>  25
                            ),
                            (
                                [SNo]           =>  3
                                [Objectives]    =>  a1,
                                [Measures]      =>  a2,
                                [Achievement]   =>  a3,
                                [Date_Target]   =>  2016-09-09,
                                [Weightage_Target]  =>  25
                            ),
                            (
                                [SNo]           =>  4
                                [Objectives]    =>  a1,
                                [Measures]      =>  a2,
                                [Achievement]   =>  a3,
                                [Date_Target]   =>  2016-09-09,
                                [Weightage_Target]  =>  25
                            ),
                        [SubTotals] =>  75
                    )   
            )
    },
    (
        [Row_Number] => Array
            (
                [title]         =>      'xxx',
                [0]             =>      Array
                    (
                        [0]     =>      Array
                            (
                                [SNo]           =>  6
                                [Objectives]    =>  a1,
                                [Measures]      =>  a2,
                                [Achievement]   =>  a3,
                                [Date_Target]   =>  2016-09-09,
                                [Weightage_Target]  =>  25
                            ),
                        [SubTotals] =>  25
                    )   
            )
    )

Above is the only example rest you have to understand how to do that.

As it would be difficlut to know which value belong to which row it might be possible that value defined as 2nd below to 3rd row.

Sᴀᴍ Onᴇᴌᴀ · Answer 4 · 2017-06-09T05:26:53.547

With the current code, there will be at least two INSERT statements, and more when $_POST['Measures'] or $_POST['C_Objectives'] contain a larger number of elements.

You can insert multiple records with one statement, and instead of using a for statement, use a foreach so you don't have to do the bookkeeping on the iterator variable. Then store values in arrays and use implode() to combine the sets of values for each record.

Check which values are being inserted into which columns - it appears that in the first for loop of your example, you are inserting the value from $_POST['SNo'][$i] into the ID field...

$values = array();
foreach($_POST['C_Objectives'] as $index=>$value) {
    $rowValues = array();
    $rowValues[] = $_POST['SNo'][$index];   //Serial_Number
    array_push($rowValues,$formno,'||<==','==','==');   //, Objectives, Measures, Targets, subtotal
    $rowValues[] = $_POST['SubTotals'][$index]; //Corporate_Objective
    $rowValues[] = $value;  //Row_Number: $value == $_POST['C_Objectives'][$index];
    $values[] = "('".implode("', '",$rowValues )."')";
}

$fields = array('Objectives','Measures','Achievement','Weightage_Target','SNo','Date_Target');
foreach($_POST['Measures'] as $index=>$value) {
    $rowValues = array($formno);
    foreach($fields as $field) {
        $rowValues[] = $_POST[$field][$index];
    }
    $values[] = "('".implode("', '",$rowValues )."')";
}

$sql = "INSERT INTO Appraisal_Objectives (Serial_Number,Objectives,Measures,Targets,subtotal,Corporate_Objective,Row_Number,ID) VALUES ".implode(', ',$values);

$stmt = sqlsrv_query($conn, $sql);
if($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}
else {
    echo " ";
}

score -1 · Answer 5 · answered Sep 09 '16 at 06:36

-1

what are you going to do? Two loop execution is different insertion .....

Solution: 1. The second update operation. 2. organize the data into the database at once.

answered Sep 09 '16 at 06:36

YaHui Jiang

130
6

What would you suggest? How should I go about it? Can I have 1 insert for both the dynamic rows? – SR1092 Sep 09 '16 at 06:39
What you asking in question @SR1092? its not good practice to insert query you using. And if you want to insert serial no. sequentially then increment the $fromn on each execution of foreach. – Rana Aalamgeer Sep 15 '16 at 06:27

How to insert multiple dynamic rows into the database

5 Answers5

Linked