Update a NULL datetime column from PHP form

Question

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>

it's supposed to update my database, but it's not, my "timeOut" column remains null — Richelle
– Richelle, Commented Feb 1, 2020 at 13:45
for test, set $timeOut manually(to make sure the DataBase part is all right) — Amin Shojaei
– Amin Shojaei, Commented Feb 1, 2020 at 13:48
the id depends on which row is selected, I only need to update the timeout of the selected row that's why I didn't set a value for the id — Richelle
– Richelle, Commented Feb 1, 2020 at 13:49
@AminShojaei tried to do it manually on sql, and it's working fine. My column is updating. but when it comes from the form, it won't change — Richelle
– Richelle, Commented Feb 1, 2020 at 13:57
@Richelle what did you get in your $_POST['timeOut']. And upon checking you form has the type="button submit". Either submit / button is possible but not both. — Boopathi D
– Boopathi D, Commented Feb 1, 2020 at 14:16

user3783243 · Accepted Answer · 2020-02-01 14:20:38Z

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.

Totally missed the ?id=<?php echo $id;? on my form action. will keep the other key points on mind. Thank you so much!

Collectives™ on Stack Overflow

Update a NULL datetime column from PHP form

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related