I have some problem in PHP using MS Access database, when I running the query in PHP show error
odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect
But when I’m going echo the “$query” variable and running the syntax in Microsoft Access is running well.
And this is my query:
<?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 = date('d/m/Y', strtotime($_REQUEST['from_date'])) . ' 00:00:00'; $to_date = date('d/m/Y', strtotime($_REQUEST['to_date'])) . ' 23:59:59'; $query = "INSERT INTO CalculateData(USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, datein, timein, dateout, timeout) SELECT USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, Format(CHECKTIME, "dd/mm/yyyy") AS datein, FORMAT(MIN(CHECKTIME), 'h:m:s') AS timein, Format(CHECKTIME, "dd/mm/yyyy") AS dateout, FORMAT(MAX(CHECKTIME), 'h:m:s') AS timeout FROM TransactionLog WHERE CHECKTIME BETWEEN #$from_date# AND #$to_date# GROUP BY USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CHECKTIME "; if ($query) { // echo $query; } $letsgo = odbc_exec($conn, $query); if ($letsgo === false) { die(print_r( odbc_error(), true)); }else{ } header("location: index.php"); ?>
And this is my table field CalculateData : CalculateData
And this is my table field TransactionLog :
TransactionLog
Advertisement
Answer
First, your sql command must evaluate to something like this:
CHECKTIME BETWEEN #2021/09/28 00:00:00# AND #2021/09/29 23:59:59#
Thus, try:
$from_date = date('Y/m/d', strtotime($_REQUEST['from_date'])) . ' 00:00:00'; $to_date = date('Y/m/d', strtotime($_REQUEST['to_date'])) . ' 23:59:59'; $query = "INSERT INTO CalculateData (USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, datein, timein, dateout, timeout) SELECT USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, Format(CHECKTIME, '#yyyy/mm/dd#') AS datein, FORMAT(MIN(CHECKTIME), '#h:m:s#') AS timein, Format(CHECKTIME, '#yyyy/mm/dd#') AS dateout, FORMAT(MAX(CHECKTIME), '#h:m:s#') AS timeout FROM TransactionLog WHERE CHECKTIME BETWEEN #$from_date# AND #$to_date# GROUP BY USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CHECKTIME";
You could also try this simpler approach:
SELECT USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, DateValue(CHECKTIME) AS datein, TimeValue(MIN(CHECKTIME)) AS timein, DateValue(CHECKTIME) AS dateout, TimeValue(MAX(CHECKTIME)) AS timeout