mysql值定时器和不断增加和更新，直到点击停止?

Question

我是一个情况在这里，我有一个mysql表名为考勤，有一个列名为"break"在mysql时间格式00:00:00现在我需要一种方法来更新时间根据用户id当用户点击开始和停止它当点击停止。

在这里找到了这段代码，现在有人可以指导我通过实现我的目标吗?

my SQL query is

$stmt = $db->prepare("SELECT MAX(attn_id) AS id FROM attendance WHERE member_id = '".$_SESSION['member_id']."'");
$stmt->execute();
$row = $stmt->fetch();  
timervalue = $row['break'];      
$stmt = $db->prepare("UPDATE attendance SET break = '".timervalue."' WHERE attn_id= '".$row['id']."'");
$stmt->execute();

默认情况下break的值存储为00:00:00

我如何实现下面的代码，使它更新我的数据库?还要去掉毫秒数，我只需要h:m:s

<script type='text/javascript'>
$(function() {
// Some global variables
var startTime = 0,
elapsed   = 0,
timerId   = 0,
$timer    = $("#timer");

function formatTime(time) {
var hrs = Math.floor(time / 60 / 60 / 1000),
min = Math.floor((time - hrs*60*60*1000) / 60 / 1000),
sec = Math.floor((time - hrs*60*60*1000 - min*60*1000) / 1000);

hrs = hrs < 10 ? "0" + hrs : hrs;
min = min < 10 ? "0" + min : min;
sec = sec < 10 ? "0" + sec : sec;

return hrs + ":" + min + ":" + sec;
}

function elapsedTimeFrom(time) {
return formatTime(time - startTime + elapsed);
}

function showElapsed() {
$timer.text(elapsedTimeFrom(Date.now()));
}

function startTimer() {
// React only if timer is stopped
startTime = startTime || Date.now();
timerId = timerId || setInterval(showElapsed, 1000);
}

function pauseTimer() {
// React only if timer is running
if (timerId) {
clearInterval(timerId);
elapsed += Date.now() - startTime;
startTime = 0;
timerId = 0;
}
}

function resetTimer() {
clearInterval(timerId);
$timer.text("01:00:00");
startTime = 0;
elapsed = 0;
timerId = 0;
}
function editTimer() {
pauseTimer();
$timer.prop("contenteditable", true);
$timer.css("border", "1px solid red");
}
function setElapsed() {
var time = $timer.text(),
arr  = time.split(":");
$timer.prop("contenteditable", false);
$timer.css("border", "1px solid black");
elapsed = parseInt(arr[0]*60*60, 10);
elapsed += parseInt(arr[1]*60, 10);
elapsed += parseInt(arr[2], 10);
elapsed *= 1000;
}

function sendTime() {
pauseTimer();
// Set hidden input value before send
$("[name='time']").val(formatTime(elapsed));
}
$("[name='start']").click(startTimer);
$timer.dblclick(editTimer);
$timer.blur(setElapsed);
$("form").submit(sendTime);

});
</script>
<h1><div id="timer">00:00:00</span></div>
<form action="" method="post">
<input type="button" name="start" value="Start">
<input type="submit" name="action" value="Submit">
<input type="hidden" name="time" value="00:00:00">
</form>

衷心感谢你对我的帮助，并给予你宝贵的时间和意见。

感谢

Answer 1

这是一个"概念验证"…

<?php
/*
-- TABLE CREATION STATEMENTS
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (t TIME NOT NULL DEFAULT 0);
INSERT INTO my_table VALUES ('00:01:50');
*/
require('path/to/connection/stateme.nts');
//If something was posted, update the table. Otherwise, skip this step...
if(sizeof($_POST) != 0){ 
$x=$_POST['tts'];
$query = "UPDATE my_table SET t = SEC_TO_TIME(?) LIMIT 1";
$stmt = $pdo->prepare($query);
$stmt->execute([$x]);
}
//Grab the stored value from the database...
$query = "
select t
, time_to_sec(t) tts
, LPAD(HOUR(t),2,0) h
, LPAD(MINUTE(t),2,0) i
, LPAD(SECOND(t),2,0) s
from my_table
limit 1
";
//Format the data for ease of use...
if ($data = $pdo->query($query)->fetch()) {
$t = $data['t'];
$tts = $data['tts'];
$h = $data['h'];
$i = $data['i'];
$s = $data['s'];
} else {
$t = 0;
$tts = 0;
$h = '00';
$i = '00';
$s = '00';
}
?>
//Make a simple form, with one, hidden input...
<form id="myForm" method="post">
<input name="tts" type= "hidden" id="tts" value="tts">
<span id="hour"><?php echo $h; ?></span>:
<span id="min"><?php echo $i; ?></span>:
<span id="sec"><?php echo $s; ?></span>
<input id="startButton" type="button" value="Start/Resume">
<input id="pauseButton" type="button" value="Pause">
<button id="submit">Save</button>
</form>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<!-- The timer/stopwatch thingy-->
<script>
var Clock = {
totalSeconds: <?php echo $tts ?>,
start: function () {
if (!this.interval) {
var self = this;
function pad(val) { return val > 9 ? val : "0" + val; }
this.interval = setInterval(function () {
self.totalSeconds += 1;
$("#hour").text(pad(Math.floor(self.totalSeconds / 3600 % 60)));
$("#min").text(pad(Math.floor(self.totalSeconds / 60 % 60)));
$("#sec").text(pad(parseInt(self.totalSeconds % 60)));
<!-- Also provide data in seconds, for ease of use -->             
<!-- Note, this is 'val', not 'text'               -->
$("#tts").val(pad(parseInt(self.totalSeconds)));
}, 1000);
}
},
pause: function () {
clearInterval(this.interval);
delete this.interval;
},
resume: function () {
this.start();
}
};

$('#startButton').click(function () { Clock.start(); });
$('#pauseButton').click(function () { Clock.pause(); });
</script>