Umar Farooq Umar Farooq - 2 months ago 7
MySQL Question

inserting multiple values against multiple ids in database table

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.
here is my form table
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

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 />';
}