Vfrdtyky

I need to create a report that will produce a list of instructors and the students that visited the lab under those instructors broken down by date.

But currently I'm adding the checkIn and checkOut time using a getTime function and saving that time as VARCHAR in MySQL because I had problems with the timestamp in MySQL and every check in/check out is it's own row. I'm also afraid that I won't be able to use that data to produce the report correctly.

How can I make it to where when someone checks in a row is created and when they check out later their previous row is updated with the current datetime as checkOut?

// Checking In/Out users

router.post("/checkin", (req, res) => {



const id = req.body.create_id

const firstName = req.body.create_first_name

const lastName = req.body.create_last_name

const checkIn = getDateTime();

const checkOut = getDateTime();



console.log("Checking In...")

console.log("ID: " + req.body.create_id)

console.log("First Name: " + req.body.create_first_name)

console.log("Last Name: " + req.body.create_last_name)

console.log(checkIn)

console.log(checkOut)



// Checking Student In

    if(req.body.person === "Student" && req.body.checkInOut === "checkIn") {

      queryString = "INSERT INTO students ( student_id ,firstName, lastName, checkIn) VALUES(?, ?, ?, ?)"



      getConnection().query(queryString, [id, firstName, lastName, checkIn], (err, rows, fields) => {

        // If error occures 

        if(err) {

          console.log("Failed to check in new user: " + err)

          res.sendStatus(500)

          return

        }

        console.log("Checked in new user with id: ", id)

      })

    }



    // Checking Student Out

    else if(req.body.person === "Student" && req.body.checkInOut === "checkOut") {

      queryString = "INSERT INTO students ( student_id ,firstName, lastName, checkOut) VALUES(?, ?, ?, ?)"



      getConnection().query(queryString, [id, firstName, lastName, checkOut], (err, rows, fields) => {

        // If error occures 

        if(err) {

          console.log("Failed to check out new user: " + err)

          res.sendStatus(500)

          return

        }

        console.log("Checked out user with id: ", id)

      })

    }

you can also create table like

CREATE TABLE `students` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `first_name` varchar(128) NOT NULL DEFAULT '',

  `is_checkout` boolean NOT NULL DEFAULT FALSE,

  `last_name` varchar(128) NOT NULL DEFAULT '',

  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

When user check in you only create one row, current_timestamp of mysql will record current date time
and when user check out you find students by id where student.is_checkout = false and update column is_checkout to true and at that time updated_at will be record current timestamp of checkout date