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

CodePudding user response：

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