How to update data in MYSQL using foreach loop

Question

I am doing attendance management system but I am unable to understand. please help me

<form method="post">
   <div class="table-responsive py-4">
      <table class="table table-flush" id="datatable-basic">
         <thead class="thead-light">
            <tr>
               <th>Name</th>
               <th>Father's Name</th>
               <th>Hall Ticket</th>
               <th>Department</th>
               <th>Attendace</th>
            </tr>
         </thead>
         <tbody>
            <?php
               if(isset($_POST["search_students"])){
                 $department=$_POST['department'];

                 $sql = "SELECT * FROM student_info WHERE department='$department'";
                 $result = $conn->query($sql);

                 if ($result->num_rows > 0) {
                   while($row = $result->fetch_assoc()) {
                     echo "<tr>";
                     echo  "<td>".$row["name"]."</td>";
                     echo  "<td>".$row["father_name"]."</td>";
                     echo  "<td>".$row["hall_ticket"]."</td>";
                     echo  "<td>".$row["department"]."</td>";
                     echo  "<td>
                             Present <input type='radio' name='attendance[".$row['hall_ticket']."][".$row['department']."]' value='Present'>
                             Absent <input type='radio' name='attendance[".$row['hall_ticket']."][".$row['department']."]' value='Absent'>
                       </td>";
                     echo "</tr>";
                   }
                 }
               }
               ?>
         </tbody>
      </table>
   </div>
   <div class="col-lg-12 mb-3">
      <button type="submit" name="mark_attendance" class="btn btn-success">Mark Attendance</button>
   </div>
</form>
</div>
<?php
   if(isset($_POST["mark_attendance"])){
       $attendance=$_POST['attendance'];

       foreach ($attendance as $key => $value) {



         if($value=="Present") {
           $query="Insert into attendance (hall_ticket,attendance) values ('$key','Present')";
           $insertAttendance=$conn->query($query);               
         }
       else {
         $query="Insert into attendance (hall_ticket,attendance) values ('$key','Absent')";
         $insertAttendance=$conn->query($query);
       }
     }

     if($insertAttendance){
       echo "Attendance Updated Sucessfully";
     }


   }
     ?>
</div>

Here i want 2 key to store into my database as hall ticket value and department value.

attendance[".$row['hall_ticket']."][".$row['department']."]

how to use both variables to store into my db. I want to store each table person attendance with their department and hallticket. i will get the hallticket if i remove department from name and simply use $key to store the values.

Please read about SQL injection. Instead of building queries with string concatenation, use prepared statements with bound parameters. See this page and this post for some good examples. — John Conde
– John Conde, Commented Feb 20, 2019 at 14:19
Where does department come into this? Do you have a department column in your database? Do you not just need a second nested foreach? — Jonnix
– Jonnix, Commented Feb 20, 2019 at 14:24
yes i have column to store the data. how can i do only i need that. — Rahul Kumar Sharma
– Rahul Kumar Sharma, Commented Feb 20, 2019 at 14:25
Don't fire insert on each iteration. Instead make a single query string and use the (),(),()... syntax of insert and fire only once at the end, outside of the for loop. — mighty_dev
– mighty_dev, Commented Feb 20, 2019 at 14:30

Rohit Ghotkar · Accepted Answer · 2019-02-20 15:15:01Z

1

Try following code:

HTML Code (Created dummy data for reference):

<form action='new.php' method="POST" >
    Presnt<input type="radio" name="attendance[111][555]" value="present"><br>
    Absent<input type="radio" name="attendance[111][555]" value="absent"><br><br>
    Presnt<input type="radio" name="attendance[222][555]" value="present"><br>
    Absent<input type="radio" name="attendance[222][555]" value="absent"><br><br>
    Presnt<input type="radio" name="attendance[333][555]" value="present"><br>
    Absent<input type="radio" name="attendance[333][555]" value="absent"><br>
<input type="submit">
</form>

PHP Code:

<?php
 $attendance=$_POST['attendance'];
 $data = "";
foreach ($attendance as $key => $value) {
     $department = array_keys($value)[0];   
     $data.="('". $key ."','". $department ."', '" . $value[$department] . "'),";   
}
$query = "INSERT INTO attendance (hall_ticket,department,attendance) VALUES " . 
rtrim($data, ",");

// Your Query will look like:
// Query = "INSERT INTO attendance (hall_ticket,department,attendance) 
//          VALUES ('111','555', 'present'),
//                 ('222','555', 'present'),
//                 ('333','555', 'absent')";

// Now execute your query
$conn->query($query); 
echo $sql;
?>

Note :

Never hit the database in for loops
Even this code will work for you but this is open for SQL injection, you should use a prepared statement to build and execute you query.

edited Feb 20, 2019 at 15:15

answered Feb 20, 2019 at 15:09

Rohit Ghotkar

8036 silver badges17 bronze badges

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

4 Comments

Rahul Kumar Sharma Over a year ago

One thing more what is the default for date in mySQL for saving only date

Rohit Ghotkar Over a year ago

You can use now() to save current.

Rahul Kumar Sharma Over a year ago

i need to save only date not time

Rohit Ghotkar Over a year ago

If you don't want time then change the type of your column from datetime to date.

WolfCode · Accepted Answer · 2019-02-20 14:48:39Z

0

so you $attendance is

$attendance=array(
$row['hall_ticket'] => array(
$row['departement']"=> [" present " OR "absence"]
);

this what i understand

my solution is

foreach ($attendance as $hall => $departements) {
  foreach($departements as departement => $value){
       $req="INSERT INTO attendance (hall_ticket,department,attendance) 
       values ('".$hall. "','". $departement ."' ,'". $value ."')";
       $insertAttendance=mysqli_query($dataBaseConnect,$req);               
 }

answered Feb 20, 2019 at 14:48

WolfCode

1451 silver badge8 bronze badges

3 Comments

Rahul Kumar Sharma Over a year ago

how should i get the value of $row['hall_ticket'] and $row['departement']

Rahul Kumar Sharma Over a year ago

i want to take data of hall ticket, department and attendence from table. and use to save this data into database

WolfCode Over a year ago

so you want take the data form the table and save to an other table ..?

Collectives™ on Stack Overflow

How to update data in MYSQL using foreach loop

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related