Reset and start ID from 1 & increment squentially, when every day date change

PHP
1 
// Get the current date
        $currentDate = date('Y-m-d');
       
      // Check if the date needs to be reset
        $query = "SELECT Date, token_no FROM userdata";
        $result = mysqli_query($conn, $query);

        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $storedDate = $row["Date"];
            $tokenID = $row["token_no"];

            if ($storedDate != $currentDate) {
                // Reset the token ID to 1
                $tokenID = 1;
                $updateQuery = "UPDATE userdata SET Date = '$currentDate', token_no = 1";
                mysqli_query($conn, $updateQuery);
            } else {
                // Increment the token ID
                $tokenID++;
                $updateQuery = "UPDATE userdata SET token_no = $tokenID";
                mysqli_query($conn, $updateQuery);
            }
        } else {
            // No record in the table, insert a new one with token ID 1
            $insertQuery = "INSERT INTO userdata (Date, token_no) VALUES ('$currentDate', 1)";
            mysqli_query($conn, $insertQuery);

table

2

Okay, and the question is…?

Other than your UPDATE queries have no WHERE, so they will update the entire table every time.

3

For the example result (picture) you posted, the logic would never use an UPDATE query. You would only INSERT rows. If there is not a row with the current date, you would insert a row with the current date and a 1 for the token_no. If there is a row with the current date, you would insert a row with the current date and use the current latest row token_no + 1 for the token_no.

If you were doing this for real, it must be concurrent safe. You would need to perform this using a single (atomic) query. You would use an INSERT … SELECT query, with the SELECT part of the query containing conditional logic to produce either a 1 for the token_no or the current latest row token_no + 1 to use for the INSERT value.

1 Like