php - Updating data in database base on id?

Question

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!";
}

You forgot to call $d->bind_param() to provide a value for the ? placeholder. — Barmar
– Barmar, Commented Dec 21, 2021 at 2:03
Oh, I think I get it. You want the ID that was created by AUTO_INCREMENT when you inserted the previous time. Set a session variable from $conn->lastInsertId(), and use that when they clock out. — Barmar
– Barmar, Commented Dec 21, 2021 at 2:30

Barmar · Accepted Answer · 2021-12-21 02:33:54Z

3

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!";
}

answered Dec 21, 2021 at 2:33

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Oliver Kucharzewski · Accepted Answer · 2021-12-21 02:05:21Z

0

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]);

answered Dec 21, 2021 at 2:05

Oliver Kucharzewski

2,7155 gold badges32 silver badges56 bronze badges

5 Comments

s.alonzo Over a year ago

Do I need to define variable $id to run $d->execute? It is returning me that debug result

Oliver Kucharzewski Over a year ago

Yes. Make sure you define $id. If you want to test, you can do $id = 1 if you have a row with id of 1 :)

s.alonzo Over a year ago

But what if I want to update without specifiying id of 1, 2, etc

Oliver Kucharzewski Over a year ago

Where would you like to get the ID from? What is the purpose of this script? :) You can use $_POST and $_GET to practice. If you dont want to use these, you can use a $_SESSION variable. Depends what you want to do. I'll help you out, just clarify what you need.

mickmackusa Over a year ago

If you aren't going to specify the id value, then you don't need to use a prepared statement and your UPDATE will affect EVERY row in your table. Be sure that that behavior is what you want.

LihnNguyen · Accepted Answer · 2021-12-21 02:06:51Z

0

You try update

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

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

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

answered Dec 21, 2021 at 2:06

LihnNguyen

8836 silver badges12 bronze badges

2 Comments

s.alonzo Over a year ago

Do I need to define variable $id to run $d->execute? It is returning me that debug result

Barmar Over a year ago

@s.alonzo Of course you do. How else would it know which ID you want to update?

Collectives™ on Stack Overflow

php - Updating data in database base on id?

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related