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.

Answer 1

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.