inserting multiple values against multiple ids in database table

Question

i have a form that takes date, time in and time out of all employees at once and submit it into the database table called attendance. the names of the employees are fetched from the database table and every employee has a unique id. now the question is, how can i format the query that can insert the data into the database at once. this is my php code.

<?php 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "employee_record";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$output="";
    $query= "SELECT * ";
    $query.= "FROM employe ";
    $result=mysqli_query($conn,$query);
        if(!$result){
            echo ("Database query failed. ".mysqli_connect_error());
        }
    <form  action="test.php" method="POST" >
        <table border="1" style="margin-top: 20px">
            <thead>
                <th>Employee Name</th>
                <th>Date</th>
                <th>Time In</th>
                <th>Time Out</th>
            </thead>
            <tbody>
        <?php   
        $id=array();
        $date=array();
        $timein=array();
        $timeout=array();
        while($employe = mysqli_fetch_assoc($result)){ 
                echo "<tr><td>";
                    echo "<input type=\"hidden\" name=\"id[]\" value=\"";
                     echo $employe['id']; 
                    echo "\"/>";
                     echo $employe['firstname']." ".$employe['lastname']."</td>";
                     echo "<td><input type=\"date\" name=\"date[]\" value=\"\"/></td>";
                     echo "<td><input type=\"datetime-local\" name=\"timein[]\" value=\"\"/></td>";
                     echo "<td><input type=\"datetime-local\" name=\"timeout[]\" value=\"\"/></td>";
                echo "</tr>";
        }
    ?>
    </tbody>
    </table>
    <input type="submit" name="submit" value="Submit" />
    </form>
?>

Now say i have the data like.. id(1,2,3), date(date1,date2,date3), timeIn(time1,time2,time3), timeOut(time1,time2,time3). the amount of data varies becouse m fetching the ids from data table where number of employees can be more or less . now i'm just stuck on how can i insert this data against the ids in the database. any help will be appreciated..

Answer 1

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

    print_r($_POST);// die;
                /*        Array
                (
                    [date] =2019-11-18
                    [id] = Array
                        (
                            [8] = 8
                            [9] = 9
                            [11] = 11
                            [14] = 14
                            [15] = 15
                            [17] = 17
                            [16] = 16
                        )

                    [a_status] = Array
                        (
                            [8] = present
                            [9] = present
                            [11] = present
                            [14] = present
                            [15] = present
                            [17] = present
                            [16] = present
                        )

                    [submit] = Submit
                )*/

    $i=0;
      $id =$_POST['id'];
      $a_status = $_POST['a_status'];
      $date = $_POST['date'];

      foreach ($a_status as $key => $id) {

        $sql = "INSERT INTO `tbl_attendance`(`stid`, `date`, `a_status`) VALUES('$key','".$_POST['date']."','$id')";
        echo $sql; //die;
        $res = mysqli_query($conn,$sql);
        // print_r($res);
        $i++;
      }
    //hi
}

?>

Answer 2

Update (2019)

After reviewing my original answer, I realized that I wouldn't use that method today. Basically because now I know, that executing one prepared statement row by row in single transaction is fast enough, unless we are talking about thousands of rows (which is unlikely the case when the data is user generated). Also any SQL injection protection was missing in the answer, which is now no issue with parameterized queries.

$ids      = [];
$dates    = [];
$timeIns  = [];
$timeOuts = [];

// validate $_POST data and put into arrays above

$stmt = $conn->prepare("
    UPDATE attendance SET
        date    = ?,
        tiemeIn = ?,
        timeOut = ?
    WHERE user_id = ?
");

$numRows = count($ids);

$conn->begin_transaction();
    for ($i = 0; $i < $numRows; $i++) {
        $stmt->bind_param('sssi', $date[$i], $timeIns[$i], $timeOuts, $ids[$i]);
        $stmt->execute();
    }
$conn->commit();

Original answer (2015)

To insert multiple (M) rows with N columns you can use the following syntax:

INSERT INTO tablename 
    (colname1, colname2, .. , colnameN) 
VALUES
    (valueRow1Colname1, valueRow1Colname2, .. , valueRow1ColnameN),
    (valueRow2Colname1, valueRow2Colname2, .. , valueRow2ColnameN),
    ..
    (valueRowMColname1, valueRowMColname2, .. , valueRowMColnameN);

You can create that query with PHP using function join or implode.

$ids      = array();
$dates    = array(); 
$timeIns  = array();
$timeOuts = array();

// validate $_POST data and put into arrays above

$numInserts = count($ids);
if ($numInserts > 0) {
    $values = array();
    for ($i = 0; $i < $numInserts; $i++) {
        $values[] = "({$ids[$i]}, '{$dates[$i]}', '{$timeIns[$i]}', '{$timeOuts[$i]}')";
    }
    $valuesJoined = join(',', $values);

    $query = "
        INSERT INTO attendance (user_id, date, tiemeIn, timeOut)
        VALUES {$valuesJoined}
        ON DUPLICATE KEY UPDATE 
            date    = VALUES(date),
            tiemeIn = VALUES(tiemeIn),
            timeOut = VALUES(timeOut)
    ";
    $result=mysqli_query($conn,$query);
}

Answer 3

IF I understand the question correctly then perhaps something along these lines might help. This is, of course, untested with your data but I think the idea should work.

/*
    ------------------------------
    To process the form submission
    ------------------------------
*/

$update=false;/* change this if the final output sql looks correct..*/
$keys=array_keys( $_POST );
$length=count( $_POST[ $keys[ 0 ] ] );

for( $i=0; $i < $length; $i++ ){

    $params=array();

    foreach( $keys as $field ) {
        $value=$_POST[ $field ][ $i ];

        if( $field!=='id' ) $params[]="`{$field}`=\"".str_replace('"',"'",$value)."\"";
        else $where=" where `id`='".$value."';";
    }

    $sql="update `employee` set ".implode(', ',$params ) . $where;
    if( $update ) $result=mysqli_query( $conn, $sql );
    else echo $sql.'<br />';
}