dblib_tds模式，支持查询，修改，插入，删除，翻页。
<?php
// 数据库连接，使用 dblib 驱动连接 SQL Server
try {
$dsn = "dblib:host=192.168.0.1:1433;dbname=数据库名;charset=UTF-8;tds_version=7.0";
$username = "用户名";
$password = "密码";
$pdo = new PDO($dsn, $username, $password);
$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="1"></td>
<td><input name="UserExtFmt" class="form-control form-control-sm" value="0"></td>
<td><button type="submit" class="btn btn-success btn-sm">添加</button></td>
</tr>
</tbody>
</table>
</form>
<div style="max-height: 500px; overflow-y: auto;">
<table class="table table-bordered table-striped table-hover 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>
<form method="post">
<input type="hidden" name="edit" value="1">
<input type="hidden" name="USERID" value="<?= htmlspecialchars($row['USERID']) ?>">
<input type="hidden" name="ORIG_TIME" value="<?= htmlspecialchars($row['CHECKTIME']) ?>">
<td><?= htmlspecialchars($row['USERID']) ?></td>
<td><input type="datetime-local" name="CHECKTIME" value="<?= (new DateTime($row['CHECKTIME']))->format('Y-m-d\TH:i:s') ?>" class="form-control form-control-sm"></td>
<td><input name="CHECKTYPE" value="<?= htmlspecialchars($row['CHECKTYPE']) ?>" class="form-control form-control-sm"></td>
<td><input name="VERIFYCODE" value="<?= htmlspecialchars($row['VERIFYCODE']) ?>" class="form-control form-control-sm"></td>
<td><input name="SENSORID" value="<?= htmlspecialchars($row['SENSORID']) ?>" class="form-control form-control-sm"></td>
<td><input name="Memoinfo" value="<?= htmlspecialchars($row['Memoinfo']) ?>" class="form-control form-control-sm"></td>
<td><input name="WorkCode" value="<?= htmlspecialchars($row['WorkCode']) ?>" class="form-control form-control-sm"></td>
<td><input name="sn" value="<?= htmlspecialchars($row['sn']) ?>" class="form-control form-control-sm"></td>
<td><input name="UserExtFmt" value="<?= htmlspecialchars($row['UserExtFmt']) ?>" class="form-control form-control-sm"></td>
<td>
<button type="submit" class="btn btn-primary btn-sm mb-1">修改</button>
<a href="?delete=1&uid=<?= $row['USERID'] ?>&time=<?= urlencode($row['CHECKTIME']) ?>" onclick="return confirm('确认删除吗？')" class="btn btn-danger btn-sm">删除</a>
</td>
</form>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<!-- 分页 -->
<nav aria-label="分页导航">
<ul class="pagination justify-content-center">
<?php if ($page > 1): ?>
<li><a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=1">首页</a></li>
<li><a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $page - 1 ?>">上一页</a></li>
<?php else: ?>
<li class="page-item disabled"><span>首页</span></li>
<li class="page-item disabled"><span>上一页</span></li>
<?php endif; ?>
<li class="page-item disabled"><span><?= $page ?> / <?= max($total_pages,1) ?></span></li>
<?php if ($page < $total_pages): ?>
<li><a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $page + 1 ?>">下一页</a></li>
<li><a href="?userid=<?= $userid ?>&start_time=<?= htmlspecialchars(str_replace(' ', 'T', $start_time)) ?>&page=<?= $total_pages ?>">尾页</a></li>
<?php else: ?>
<li class="page-item disabled"><span>下一页</span></li>
<li class="page-item disabled"><span>尾页</span></li>
<?php endif; ?>
</ul>
</nav>
<p>
<center><a href="/index.php" target="_self">pdo_sqlsrv模式</a>
</p>
</div>
</body>
</html>推荐本站淘宝优惠价购买喜欢的宝贝:
本文链接：https://www.hqyman.cn/post/11963.html 非本站原创文章欢迎转载，原创文章需保留本站地址！
微信支付宝扫一扫，打赏作者吧～休息一下~~
萌ICP备20248119号
