php - Updating data in database base on id?-CodePudding

I am working on a project that takes students attendance in class and I want to update the database data through PHP whilst running a SQL function of UPDATE, but I want to be able to update it base on the id of the data.

This is the code that I am working with at the moment.

<?php

require_once './dba.php';

$status = "";

if(isset($_POST['time_in'])) {

    $query = "INSERT INTO nameOfTable (datetime) VALUES (NOW())";

    $d = $conn->prepare($query);

    $d->execute();     

} elseif(isset($_POST['time_out'])) {
    $query = "UPDATE nameOfTable SET datetime = NOW() WHERE id = ? ";

    $d = $conn->prepare($query);

    $d->execute();     
} else {
    $status = "Can't time in!";
}

CodePudding user response：

Use $conn->lastInsertId() to get the ID that was assigned when they clocked in. Save that in a session variable and use it when they clock out.

<?php

require_once './dba.php';

$status = "";

if(isset($_POST['time_in'])) {
    $query = "INSERT INTO nameOfTable (datetime) VALUES (NOW())";
    $d = $conn->prepare($query);
    $d->execute();     
    $_SESSION['clock_id'] = $conn->lastInsertId();
} elseif(isset($_POST['time_out'])) {
    if (!isset($_SESSION['clock_id'])) {
      $status = "You need to clock in first!";
    } else {
      $query = "UPDATE nameOfTable SET datetime = NOW() WHERE id = :id ";
      $d = $conn->prepare($query);
      $d->execute(['id' => $_SESSION['clock_id']]);   
    }
} else {
    $status = "Can't time in!";
}

CodePudding user response：

You must remember to prepare the query and bind the parameters onto it.

Use the $id variable to prepare the query with the appropriate ID.

Make sure you authenticate the session before passing the ID to the query, otherwise an attacker can manipulate this data to pull anyone's data they wish.

// Its helpful to create elements within the code to bind onto. :id is ours.
$query = "UPDATE nameOfTable SET datetime = NOW() WHERE id = :id ";

$d = $conn->prepare($query);

// Run the query & bind id to :id
$d->execute(['id' => $id]);

CodePudding user response：

You try update

    $query = "UPDATE nameOfTable SET datetime = NOW() WHERE id = :id ";

    $d = $conn->prepare($query);

    $d->execute(['id' => $id ]);