pdo_sqlsrv模式，支持查询，修改，插入，删除，翻页。
<?php
// 数据库连接 使用 pdo_sqlsrv sqlsrv 驱动连接 SQL Server
try {
$pdo = new PDO("sqlsr:Server=192.168.0.1;Database=数据库名", "用户名", "密码");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("❌ 数据库连接失败: " . $e->getMessage());
}
// 分页处理
$limit = 10;
$page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
$userid = isset($_GET['userid']) ? intval($_GET['userid']) : 123456;
$start_time_raw = $_GET['start_time'] ?? date('Y-m-01\T00:00:00');
$start_time = (new DateTime($start_time_raw))->format('Y-m-d H:i:s');
// 插入数据
$msg = "";
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['add'])) {
try {
$insert_sql = "INSERT INTO CHECKINOUT (USERID, CHECKTIME, CHECKTYPE, VERIFYCODE, SENSORID, Memoinfo, WorkCode, sn, UserExtFmt)
VALUES (:USERID, :CHECKTIME, :CHECKTYPE, :VERIFYCODE, :SENSORID, :Memoinfo, :WorkCode, :sn, :UserExtFmt)";
$stmt = $pdo->prepare($insert_sql);
$stmt->execute([
':USERID' => $_POST['USERID'],
':CHECKTIME' => (new DateTime($_POST['CHECKTIME']))->format('Y-m-d H:i:s'),
':CHECKTYPE' => $_POST['CHECKTYPE'],
':VERIFYCODE' => $_POST['VERIFYCODE'],
':SENSORID' => $_POST['SENSORID'],
':Memoinfo' => $_POST['Memoinfo'] ?: null,
':WorkCode' => $_POST['WorkCode'],
':sn' => $_POST['sn'],
':UserExtFmt' => $_POST['UserExtFmt']
]);
$msg = "✅ 添加成功";
} catch (Exception $e) {
$msg = "❌ 添加失败：" . $e->getMessage();
}
}
// 修改数据
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit'])) {
try {
$update_sql = "UPDATE CHECKINOUT SET
CHECKTIME = :CHECKTIME, CHECKTYPE = :CHECKTYPE, VERIFYCODE = :VERIFYCODE,
SENSORID = :SENSORID, Memoinfo = :Memoinfo, WorkCode = :WorkCode, sn = :sn, UserExtFmt = :UserExtFmt
WHERE USERID = :USERID AND CHECKTIME = :ORIG_TIME";
$stmt = $pdo->prepare($update_sql);
$stmt->execute([
':CHECKTIME' => (new DateTime($_POST['CHECKTIME']))->format('Y-m-d H:i:s'),
':CHECKTYPE' => $_POST['CHECKTYPE'],
':VERIFYCODE' => $_POST['VERIFYCODE'],
':SENSORID' => $_POST['SENSORID'],
':Memoinfo' => $_POST['Memoinfo'] ?: null,
':WorkCode' => $_POST['WorkCode'],
':sn' => $_POST['sn'],
':UserExtFmt' => $_POST['UserExtFmt'],
':USERID' => $_POST['USERID'],
':ORIG_TIME' => $_POST['ORIG_TIME']
]);
$msg = "✅ 修改成功";
} catch (Exception $e) {
$msg = "❌ 修改失败：" . $e->getMessage();
}
}
// 删除数据
if (isset($_GET['delete'])) {
$delete_userid = intval($_GET['uid']);
$delete_time = $_GET['time'];
try {
$stmt = $pdo->prepare("DELETE FROM CHECKINOUT WHERE USERID = ? AND CHECKTIME = ?");
$stmt->execute([$delete_userid, $delete_time]);
$msg = "✅ 删除成功";
} catch (Exception $e) {
$msg = "❌ 删除失败：" . $e->getMessage();
}
}
// 查询总记录数，判断分页范围
$stmtCount = $pdo->prepare("SELECT COUNT(*) FROM CHECKINOUT WHERE USERID = ? AND CHECKTIME >= ?");
$stmtCount->execute([$userid, $start_time]);
$total_records = (int)$stmtCount->fetchColumn();
$total_pages = (int)ceil($total_records / $limit);
if ($page > $total_pages && $total_pages > 0) {
$page = $total_pages;
}
$offset = ($page - 1) * $limit;
// 查询记录
$stmt = $pdo->prepare("SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY CHECKTIME DESC) AS RowNum, *
FROM CHECKINOUT WHERE USERID = ? AND CHECKTIME >= ?) AS Result
WHERE RowNum BETWEEN ? AND ?");
$stmt->execute([$userid, $start_time, $offset + 1, $offset + $limit]);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>考勤管理</title>
<!-- 移动设备适配 -->
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/5.3.0/css/bootstrap.min.css">
<style>
input.form-control-sm { padding: 0.1rem 0.3rem; font-size: 0.85rem; }
th, td { vertical-align: middle; white-space: nowrap; font-size: 14px; }
/* 移动端自适应调整 */
@media (max-width: 767.98px) {
table {
font-size: 12px;
white-space: normal; /* 自动换行 */
}
th, td {
white-space: normal;
word-break: break-word;
padding: 0.3rem;
}
.form-control-sm {
font-size: 1rem;
padding: 0.25rem 0.5rem;
}
.container {
padding-left: 0.5rem;
padding-right: 0.5rem;
}
.btn {
font-size: 0.9rem;
padding: 0.25rem 0.5rem;
}
/* 横向滚动表格 */
.table-responsive {
overflow-x: auto;
}
}
</style>
</head>
<body>
<div>
<h3>考勤记录管理</h3>
<?php if (!empty($msg)) echo "<div class='alert alert-info'>$msg</div>"; ?>
<form method="get" class="row g-2 mb-3">
<div>
<label>用户ID</label>
<input type="number" name="userid" value="<?= htmlspecialchars($userid) ?>">
</div>
<div>
<label>起始时间</label>
<input type="datetime-local" name="start_time" value="<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>" step="1">
</div>
<div class="col-auto mt-4">
<button type="submit" class="btn btn-primary">查询</button>
</div>
</form>
<form method="post">
<input type="hidden" name="add" value="1">
<table class="table table-bordered table-sm align-middle">
<thead>
<tr>
<th>USERID</th><th>CHECKTIME</th><th>CHECKTYPE</th><th>VERIFYCODE</th>
<th>SENSORID</th><th>Memoinfo</th><th>WorkCode</th><th>sn</th><th>UserExtFmt</th><th>操作</th>
</tr>
</thead>
<tbody>
<tr>
<td><input name="USERID" class="form-control form-control-sm" value="<?= $userid ?>"></td>
<td><input type="datetime-local" name="CHECKTIME" class="form-control form-control-sm" step="1"></td>
<td><input name="CHECKTYPE" class="form-control form-control-sm" value="I"></td>
<td><input name="VERIFYCODE" class="form-control form-control-sm" value="1"></td>
<td><input name="SENSORID" class="form-control form-control-sm" value="1"></td>
<td><input name="Memoinfo" class="form-control form-control-sm"></td>
<td><input name="WorkCode" class="form-control form-control-sm" value="0"></td>
<td><input name="sn" class="form-control form-control-sm" value="abc12345678"></td>
<td><input name="UserExtFmt" class="form-control form-control-sm" value="1"></td>
<td><button class="btn btn-success btn-sm" type="submit">添加</button></td>
</tr>
</tbody>
</table>
</form>
<div class="table-responsive mb-3">
<table class="table table-bordered table-sm align-middle">
<thead>
<tr>
<th>USERID</th><th>CHECKTIME</th><th>CHECKTYPE</th><th>VERIFYCODE</th>
<th>SENSORID</th><th>Memoinfo</th><th>WorkCode</th><th>sn</th><th>UserExtFmt</th><th>操作</th>
</tr>
</thead>
<tbody>
<?php foreach ($data as $row): ?>
<tr>
<td><?= htmlspecialchars($row['USERID']) ?></td>
<td><?= htmlspecialchars($row['CHECKTIME']) ?></td>
<td><?= htmlspecialchars($row['CHECKTYPE']) ?></td>
<td><?= htmlspecialchars($row['VERIFYCODE']) ?></td>
<td><?= htmlspecialchars($row['SENSORID']) ?></td>
<td><?= htmlspecialchars($row['Memoinfo']) ?></td>
<td><?= htmlspecialchars($row['WorkCode']) ?></td>
<td><?= htmlspecialchars($row['sn']) ?></td>
<td><?= htmlspecialchars($row['UserExtFmt']) ?></td>
<td>
<button class="btn btn-primary btn-sm" type="button" onclick="editRow(this)">修改</button>
<a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&delete=1&uid=<?= $row['USERID'] ?>&time=<?= urlencode($row['CHECKTIME']) ?>"
onclick="return confirm('确定删除该记录？');" class="btn btn-danger btn-sm">删除</a>
</td>
</tr>
<?php endforeach; ?>
<?php if (empty($data)): ?>
<tr><td colspan="10">无数据</td></tr>
<?php endif; ?>
</tbody>
</table>
</div>
<!-- 分页 -->
<nav aria-label="Page navigation">
<ul class="pagination justify-content-center">
<li class="page-item <?= $page <= 1 ? 'disabled' : '' ?>">
<a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $page-1 ?>">上一页</a>
</li>
<?php for ($i=1; $i <= $total_pages; $i++): ?>
<li class="page-item <?= $page == $i ? 'active' : '' ?>">
<a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $i ?>"><?= $i ?></a>
</li>
<?php endfor; ?>
<li class="page-item <?= $page >= $total_pages ? 'disabled' : '' ?>">
<a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $page+1 ?>">下一页</a>
</li>
</ul>
</nav>
<p>
<center><a href="/index_tds.php" target="_self">pdo_tds模式</a>
</p>
</div>
<script>
function editRow(button) {
let tr = button.closest('tr');
if (button.textContent === '修改') {
// 变成可编辑状态
for(let i=0; i<tr.cells.length - 1; i++) {
let cell = tr.cells[i];
let val = cell.textContent;
let inputType = 'text';
if (i === 1) inputType = 'datetime-local';
cell.innerHTML = `<input type="${inputType}" class="form-control form-control-sm" value="${val.trim()}" />`;
}
button.textContent = '保存';
button.classList.remove('btn-primary');
button.classList.add('btn-success');
} else {
// 保存逻辑这里你可以用ajax提交或者刷新页面提交，暂时提示功能
alert('请实现保存功能');
}
}
</script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/5.3.0/js/bootstrap.bundle.min.js"></script>
</body>
</html>推荐本站淘宝优惠价购买喜欢的宝贝:
本文链接：https://zblog.hqyman.cn/post/11962.html 非本站原创文章欢迎转载，原创文章需保留本站地址！
微信支付宝扫一扫，打赏作者吧～休息一下~~
萌ICP备20248119号
