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

// 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

Okay, and the question is…?

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

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.