Update a NULL datetime column from PHP form

So I’ve been trying possible solutions but didn’t get any success. I wanted to UPDATE the “timeOut” column (which has a NULL value) at the end of the day. I wanted to change it from NULL to whatever is filled on the form. Here’s my php code:

<?php

require '../system/database.php';

    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $id = null;
    if ( !empty($_GET['id'])) {
        $id = $_REQUEST['id'];
    }
    if (null==$id){
        header("Location:record.php");
    }
    if ($_POST) {       
         //$Name = $_POST['name'];
         //$TimeIn = $_POST['timeIn'];
        //$TimeIn = date('Y-m-d H:i:s',strtotime($TimeIn));
        $TimeOut = $_POST['timeOut'];
        $TimeOut = date('Y-m-d H:i:s',strtotime($TimeOut));
        $valid = true;
        if ($valid){
             $sql = "UPDATE attendance SET timeOut = ? WHERE id = ?"; 
             $q = $pdo->prepare($sql);
             $q->execute(array($TimeOut,$id));
             header('location:record.php');
        }
     }else{
         $sql = "SELECT * FROM attendance WHERE id = ?";
         $q = $pdo->prepare($sql);
         $q->execute(array($id));
         $data = $q->fetch(PDO::FETCH_ASSOC);
         $Name = $data['name'];
         $TimeIn = $data['timeIn'];
         $TimeOut = $data['timeOut'];
         Database::disconnect();
     }
?>

and here’s my php form

<form action="edit-record.php" method="post" role="form">
  <div class="form-row">
     <div class="form-group col-sm-2">
          <label for="name">Name of Employee</label>
          <select class="form-control" name="name" readonly>
            <option selected="selected"><?php echo !empty($Name)?$Name:'';?></option>
          </select>
     </div>
     <div class="form-group col-sm-2">
             <label for="timeIn">Date/ Time In</label>
             <input type="text" class="form-control" name="timeIn" readonly value="<?php echo !empty($TimeIn)?$TimeIn:'';?>">
     </div>
     <div class="form-group col-sm-2">
             <label for="timeOut">Date/ Time Out</label>
             <input type="text" class="form-control" id='datetimepicker4' name="timeOut">
     </div>
     <div class="form-group col-sm-1">
             <label for="timeSubmit" style="color:white">Submit</label>
             <button type="button submit" class="btn btn-secondary">Set Time Out</button>
     </div>
   </div>
</form>

Answer

You have two issues. The first is that your debugging methods are failing you, you have header('location:record.php') twice, both for an error and success. Additionally, after every header you should have an exit so the code stops firing. As the code currently is written even with the error state the update still fires but (error #2) since $id is set to NULL because your form has no id value the where matches no records and no update occurs.

So your PHP should be:

<?php
    require '../system/database.php';
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
    $id = null;
    if ( !empty($_GET['id'])) {
        $id = $_REQUEST['id'];
    }
    if (null==$id){
        header("Location:record.php");
        //header("Location:record.php?status=no+id");
        exit();
    }
    if ($_POST) {       
        //$Name = $_POST['name'];
        //$TimeIn = $_POST['timeIn'];
        //$TimeIn = date('Y-m-d H:i:s',strtotime($TimeIn));
        $TimeOut = $_POST['timeOut'];
        $TimeOut = date('Y-m-d H:i:s',strtotime($TimeOut));
        $valid = true;
        if ($valid){
             $sql = "UPDATE attendance SET timeOut = ? WHERE id = ?"; 
             $q = $pdo->prepare($sql);
             $q->execute(array($TimeOut,$id));
             header('location:record.php');
             //header('location:record.php?status=update+fired');
             exit();
        }
     }else{
         $sql = "SELECT * FROM attendance WHERE id = ?";
         $q = $pdo->prepare($sql);
         $q->execute(array($id));
         $data = $q->fetch(PDO::FETCH_ASSOC);
         $Name = $data['name'];
         $TimeIn = $data['timeIn'];
         $TimeOut = $data['timeOut'];
         Database::disconnect();
     }
?>

and then your HTML should be (presuming these are on the same page, if not change $id to the id variable):

<form action="edit-record.php?id=<?php echo $id;?>" method="post" role="form">
  <div class="form-row">
     <div class="form-group col-sm-2">
          <label for="name">Name of Employee</label>
          <select class="form-control" name="name" readonly>
            <option selected="selected"><?php echo !empty($Name)?$Name:'';?></option>
          </select>
     </div>
     <div class="form-group col-sm-2">
             <label for="timeIn">Date/ Time In</label>
             <input type="text" class="form-control" name="timeIn" readonly value="<?php echo !empty($TimeIn)?$TimeIn:'';?>">
     </div>
     <div class="form-group col-sm-2">
             <label for="timeOut">Date/ Time Out</label>
             <input type="text" class="form-control" id='datetimepicker4' name="timeOut">
     </div>
     <div class="form-group col-sm-1">
             <label for="timeSubmit" style="color:white">Submit</label>
             <button type="button submit" class="btn btn-secondary">Set Time Out</button>
     </div>
   </div>
</form>

alternatively to get in POST

<form action="edit-record.php" method="post" role="form">
      <div class="form-row">
         <div class="form-group col-sm-2">
              <label for="name">Name of Employee</label>
              <select class="form-control" name="name" readonly>
                <option selected="selected"><?php echo !empty($Name)?$Name:'';?></option>
              </select>
         </div>
         <div class="form-group col-sm-2">
                 <label for="timeIn">Date/ Time In</label>
                 <input type="text" class="form-control" name="timeIn" readonly value="<?php echo !empty($TimeIn)?$TimeIn:'';?>">
         </div>
         <div class="form-group col-sm-2">
                 <label for="timeOut">Date/ Time Out</label>
                 <input type="text" class="form-control" id='datetimepicker4' name="timeOut">
         </div>
        <input type="hidden" value="<?php echo $id;?>" name="id" />
         <div class="form-group col-sm-1">
                 <label for="timeSubmit" style="color:white">Submit</label>
                 <button type="button submit" class="btn btn-secondary">Set Time Out</button>
         </div>
       </div>
    </form>

Additionally see commented out header calls. I would use those and on record.php look at $_GET['status'] to see what occurs.

Advertisement

Answer