Updating two tables with an inner join

[Abdallah Taha]

I am having trouble updating it. I have two tables one is employees and other is attendance.

employees table has fields as (id, employee_id, firstname, lastname, address, email, salary, creacted_on) and attendance table has fields as (id, employee_id, date, time, description). Here is my code for editing.

<?php        $query = "SELECT emp.employee_id, emp.firstname, emp.lastname, att.time, att.date, att.description from attendance AS att INNER JOIN employees AS emp where att.employee_id = emp.id";        $query_run = mysqli_query($connection, $query);        if (mysqli_num_rows($query_run) > 0) {          while ($row = mysqli_fetch_assoc($query_run)) {        ?>            <tr>              <td><?php echo date('M d, Y', strtotime($row['date'])); ?></td>              <td><?php echo $row['employee_id']; ?></td>              <td><?php echo $row['firstname'] . ' ' . $row['lastname']; ?></td>              <td><?php echo date('h:i A', strtotime($row['time'])); ?></td>              <td><?php echo ($row['description']) ?></td>              <td>                <form action="attendance_edit.php" method="post">                  <input type="hidden" name="edit_id" value="<?php echo $row['id']; ?>">                  <button type="submit" name="edit_btn" class='btn btn-success btn-sm edit btn-flat'><i class='fa fa-edit'></i> Edit</button>                </form>              </td>              <td>                <form action="attendance_code.php" method="post">                  <input type="hidden" name="deleteattendance_id" value="<?php echo $row['id']; ?>">                  <button onclick="return  confirm('do you want to delete Y/N')" type="submit" name="deleteattendance_btn" class='btn btn-danger btn-sm delete btn-flat'><i class='fa fa-trash'></i> Delete</button>                  </form>                  </td>                </tr>            <?php

page edit : $connection = mysqli_connect("localhost", "root", "", "project08");            if (isset($_POST['edit_btn'])) {                $id = $_POST['edit_id'];                $query = "SELECT * FROM attendance WHERE id= '$id'";                $result = mysqli_query($connection, $query);                while ($row = mysqli_fetch_assoc($result))            ?>                <form action="attendance_code.php" method="POST">                    <input type="hidden" name="edit_id" value="<?php echo $row['id'] ?>">                    <div class="form-group">                        <label> Date </label>                        <input type="date" name="edit_date" value="<?php echo date('M d, Y', strtotime($row['date'])); ?>" class="form-control datepicker" placeholder="Enter Date">                    </div>                    <div class="form-group">                        <label>Time</label>                        <input type="time" name="edit_time" value="<?php echo $row['time'] ?>" class="form-control timepicker" placeholder="Enter Time">                    </div>                    <div class="form-group">                        <label>Description</label>                        <input type="text" name="edit_description" value="<?php echo $row['description'] ?>" class="form-control" placeholder="Enter Description">                    </div>                    <a href="attendance.php" class="btn btn-danger"> CANCEL </a>                    <button type="submit" name="update_btn" class="btn btn-primary"> Update </button>                </form>            <?php

if (isset($_POST['update_btn'])) { $id = $_POST['edit_id']; $date = $_POST['edit_date']; $time = $_POST['edit_time']; $description = $_POST['edit_description']; $query = "UPDATE attendance att JOIN employees emp ON (att.employee_id = emp.employee_id) SET date = '$date', time = '$time', description = '$description' WHERE id = '$id'"; $query_run = mysqli_query($connection, $query); if ($query_run) { $_SESSION['status'] = "Your data is Updated"; $_SESSION['status_code'] = "success"; header('Location: attendance.php'); } else { $_SESSION['status'] = "Your data is NOT updated"; $_SESSION['status_code'] = "error"; header('Location: attendance.php'); } }

[Liutauras Vilda]

Hello.

You may want to explain the problem a bit more. You mentioned you have a trouble updating the table for what it seems, however, that was it. It may be hard for somebody to help you - there could be many troubles.

[Tim Holloway]

I don't know of any dialect of SQL that allows one SQL command to update more than one table at a time.

You can use a SELECT with a JOIN to figure out which rows to update, but each table must be updated separately.

Obviously that, however, poses a risk - what would happen if another user tried to access that data after the first table had been updated but before the second table was updated?

The solution to that is to wrap the updates in a transaction. Begin the transaction, do each table update, then commit the transaction. You may need to turn auto-commit off on your database to make that work. Also, if there is a problem while doing updating, you'd rollback the transaction rather than committing it.

Transactions make the updates an atomic operation, so there is no risk of someone getting in there while things are only half-done. It's all or nothing.

[Abdallah Taha]

Thanks, Guys, What I'm looking for is to update data from table attendance which is LEFT INNER with table employees. So I used this query

UPDATE attendance att LEFT JOIN employees emp ON(att.employee_id = emp.id)  SET date = '2018-04-27', time = '08:00:00', description = 'Sick' WHERE att.id = 1

but I'm still confused why it doesn't work the update.