php - odbc_exec():SQL 错误:[Microsoft][ODBC Microsoft Access Driver] 查询表达式中的语法错误(缺少运算符)
问题描述
我收到错误“警告:odbc_exec():SQL 错误:[Microsoft][ODBC Microsoft Access Driver] 查询表达式'CHECKTIME BETWEEN ts'2021-08-01'} AND ts'2021-08-中的语法错误(缺少运算符) 30'}'., SQLExecDirect 中的 SQL 状态 37000 在 C:\laragon\www\mondelez-report-access\getLog.php 上的第 38 行 37000" 尝试插入 Microsoft 访问。
这是我在 getLog.php 中的代码
<?php
// setup database for your microsoft Access
// you can setup password in you microsoft Access
// this is a variable for your connection in odbc
// "zkConnection" is your ODBC Data Source Administrator
$conn = odbc_connect("zkConnection", "", "");
// create condition for testing conecction
if ($conn) {
// echo "<br>Connection Established</br>";
} else {
echo "Koneksi Gagal";
}
$from_date = $_REQUEST['from_date'];
$to_date = $_REQUEST['to_date'];
$query = "INSERT INTO TransactionLog (USERID, CHECKTIME, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID)
SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, USERINFO.Name, USERINFO.lastname, USERINFO.CardNo, DEPARTMENTS.DEPTID, DEPARTMENTS.DEPTNAME, DEPARTMENTS.SUPDEPTID
FROM
(CHECKINOUT
LEFT JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID)
LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
WHERE CHECKTIME BETWEEN {ts '" . $from_date . "'} AND {ts '" . $to_date . "'} ";
$letsgo = odbc_exec($conn, $query);
if ($letsgo === false) {
die(print_r(odbc_error(), true));
}
header("location: logTransaction.php");
?>
这是我对进度 getLog.php 的看法
<?php
// For starting Session in php
session_start();
// We check if user login or not
// Check with this syntax
if (isset($_SESSION['id'])) {
?>
<DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Mondelez International</title>
<link rel="shortcut icon" href="../mondelez-report/assets/img/mondelez-logo.png" />
<!-- CSS Load Area -->
<link rel="stylesheet" href="assets/plugins/bootstrap/css/bootstrap.min.css" type="text/css" />
<link rel="stylesheet" href="assets/plugins/font-awesome/css/font-awesome.min.css" type="text/css" />
<!-- CSS Custom Load Area -->
<link rel="stylesheet" href="assets/css/mondelez.css" type="text/css" />
</head>
<body>
<?php
include 'database.php';
?>
<!-- Navigation Bar -->
<nav class="navbar navbar-default" style="background-color: var(--mondelez-white); border-color: var(--mondelez-primary);">
<div class="container">
<!-- Brand and toggle get grouped for better mobile display -->
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a class="navbar-brand" href="index.php">
<img alt="Brand" src="../mondelez-report/assets/img/mondelez-logo.png" alt="logo" class="img img-responsive" style="width: 100px; height: 100px; margin-top: -40px;">
</a>
</div>
<!-- Collect the nav links, forms, and other content for toggling -->
<div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
<ul class="nav navbar-nav">
<li><a href="index.php">Calculate Data</a></li>
<li><a href="logTransaction.php">Transaction Log</a></li>
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Reports <span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="attReports.php">Attendance Reports</a></li>
<li><a href="wrkReports.php">Working Days Reports</a></li>
<li><a href="trxReports.php">Transaction Log Reports</a></li>
</ul>
</li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">admin<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="login.php">Logout</a></li>
</ul>
</li>
</ul>
</div><!-- /.navbar-collapse -->
</div><!-- /.container-fluid -->
</nav>
<!-- Navigation Bar End -->
<!-- Header -->
<div class="container">
<div class="row">
<div class="col-md-12">
<img class="mondelez-box-home-and-reports-container-image" src="../mondelez-report/assets/img/mondelez-logo.png" alt="logo" />
</div>
<div class="col-md-12">
<span class="mondelez-box-home-and-reports-container-text">Transaction Log</span>
</div>
</div>
</div>
<!-- Header End -->
<br />
<!-- Form Reports -->
<div class="container">
<div class="row">
<div class="col-md-12">
<form action="getLog.php" method="post" id="sample_form">
<div class="row">
<div class="col-md-6">
<div class="form-group">
<label>Period From : </label>
<input type="date" name="from_date" required="required" class="form-control">
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<label>Period To : </label>
<input type="date" name="to_date" required="required" class="form-control">
</div>
</div>
<div class="col-md-12">
<div class="form-actions">
<button type="submit" class="btn btn-primary" style="float: right;" name="submit">
<i class="fa fa-download" style="padding-right: 5px;"></i>
Get Log
</button>
</div>
</div>
</div>
</form>
</div>
</div>
</div>
<!-- Form Reports End -->
<!-- Footer -->
<div class="container-fluid">
<div class="row">
<div class="col-md-12">
<div class="footer">
Powered by <a class="no-link" href="https://asiasekuriti.co.id/" target="_blank">PT Asia Sekuriti Indonesia.</a>
</div>
</div>
</div>
</div>
<!-- Footer End -->
<!-- JS Load Area -->
<script src="assets/plugins/jquery/jquery.min.js"></script>
<script src="assets/plugins/bootstrap/js/bootstrap.min.js"></script>
<!-- JS Custom Load Area -->
<script src="assets/js/mondelez.js"></script>
</body>
</html>
</DOCTYPE>
<?php
} else {
echo "Invalid Request";
}
?>
请帮忙,我已经尝试了很多教程来解决这个问题,但仍然无法正常工作
解决方案
Access 中的日期值不是文本,因此您的代码应生成如下所示的完成表达式:
WHERE CHECKTIME BETWEEN #2021/01/31# AND #2021/12/31#;
推荐阅读
- java - 如何通过反射API调用POJO中的getter
- angularjs - 在android上使用ionic1模式时如何禁用后退按钮?
- sqlite - 执行查询的时间差异很大
- azure - Azure Active Directory v2.0 似乎不支持 OAuth 会话管理
- python - Flask Jinja2 模板在按钮单击时重新加载生成的表
- laravel - 除非验证器,否则不能使用必需
- parameter-passing - 当多参数值超出子报表值时,tablix 中的 SSRS 子报表失败
- c++ - 如何将一个模板类隐式转换为另一个?
- android - Kotlin:在 android studio 中激活作者模板
- jasper-reports - 如何根据 iReport 上的其他列对字段求和