PHP ODBC Data Type Mismatch (MS Access)

Question

I got this error message when I try to query from MS Access DB using PHP ODBC. I know this is a duplicate question but none of them have the solution.

I have tried query using SQL Fiddle and it works but somehow in PHP ODBC is not working right now. I also want to know is it normal for "15.00" displayed as "15" instead of "15.00"?

Here is the link -> http://sqlfiddle.com/#!9/087d72/17

Error Message:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect

Table that I have in MS Access DB (Table name "tblPAyTrans")

Employee ID | 1stHalf | 2ndHalf | Month | Year | EPFee |
   1011     |    1    |    0    |   2   | 2017 | 15.00 |
   1011     |    0    |    1    |   2   | 2017 | 29.00 |
   1011     |    0    |    1    |   3   | 2018 | 29.00 |

Output that I want :

Employee ID|1stHalf|2ndHalf|Month|Year|EPFee(1sthalf)|EPFee(2ndHalf)|
   1011    |   1   |   1   |  2  |2017|     15.00    |    29.00     |
   1011    |   0   |   1   |  3  |2018|     29.00    |     0.00     |

Here is my code :

<?php
  $conn=odbc_connect('payrolldb','','COMPLETEPAYROLL');

  if (!$conn)
  {
    exit("Connection Failed: " . $conn);
  }

  $id = 1001;
  $first_half = 1;
  $second_half = 1;

  $sql = "SELECT `Employee ID`,
         (SELECT `1stHalf` FROM `tblPAyTrans` WHERE `Employee ID` = '$id' AND `1stHalf` = '$first_half') AS `FirstHalf`,
         (SELECT `2ndHalf` FROM `tblPAyTrans` WHERE `Employee ID` = '$id'AND `2ndHalf` = '$second_half') AS `SecondHalf`,             
         (SELECT `EPFee` FROM `tblPAyTrans` WHERE `Employee ID` = '$id' AND `1stHalf` = '$first_half') AS `EPF1stHalf`, 
         (SELECT `EPFee` FROM `tblPAyTrans` WHERE `Employee ID` = '$id' AND `2ndHalf` = '$second_half') AS `EPF2ndHalf`,
         `Month`, `Year`
         FROM `tblPAyTrans`
         GROUP BY `Employee ID`,`Month`,`Year`";

  $rs=odbc_exec($conn,$sql);

  if (!$rs)
  {
    exit("Error in SQL");
  }

  echo "<br>RESULT: <br><br>";
  echo "<table border='2'>
          <tr>
            <th>Employee ID</th>
            <th>1st Half</th>
            <th>2nd Half</th>        
            <th>Month</th>
            <th>Year</th>
            <th>EPF1stHalf</th>
            <th>EPF2ndHalf</th>
          </tr>";

  while (odbc_fetch_row($rs))
  {
    $EmployeeID = odbc_result($rs,'Employee ID');
    $FirstHalf = odbc_result($rs,'FirstHalf');
    $SecondHalf = odbc_result($rs,'SecondHalf');     
    $Month = odbc_result($rs,'Month');
    $Year = odbc_result($rs,'Year');
    $EPF1stHalf = $row['EPF1stHalf'];
    $EPF2ndHalf = $row['EPF2ndHalf'];

    echo "<tr>";
      echo "<td>" . $EmployeeID. "</td>";
      echo "<td>" . $FirstHalf. "</td>";
      echo "<td>" . $SecondHalf. "</td>";            
      echo "<td>" . $Month. "</td>";
      echo "<td>" . $Year. "</td>";
      echo "<td>" . $EPF1stHalf. "</td>";
      echo "<td>" . $EPF2ndHalf. "</td>";
    echo "</tr>"; 
  }
  odbc_close($conn);
  echo "</table>";
?>

Please correct me if I do wrong. I really appreciate it.

According to Gustav : Changing the syntax to this also give the same error.

SELECT [Employee ID],
     (SELECT [1stHalf] FROM tblPAyTrans WHERE [Employee ID] = '$id' AND [1stHalf] = '$first_half') AS FirstHalf,
     (SELECT [2ndHalf] FROM tblPAyTrans WHERE [Employee ID] = '$id'AND [2ndHalf] = '$second_half') AS SecondHalf,             
     (SELECT [EPFee] FROM tblPAyTrans WHERE [Employee ID] = '$id' AND [1stHalf] = '$first_half') AS EPF1stHalf, 
     (SELECT [EPFee] FROM tblPAyTrans WHERE [Employee ID] = '$id' AND [2ndHalf] = '$second_half') AS EPF2ndHalf,
     [Month], [Year]
     FROM tblPAyTrans
     GROUP BY [Employee ID],[Month],[Year]

Gustav Gustav · Accepted Answer · 2018-05-17T09:56:09

If this is Access SQL, modify it to:

$sql = "SELECT [Employee ID],
       (SELECT [1stHalf] FROM tblPAyTrans … etc.

Edit: And you may also need concatenating:

… WHERE [Employee ID] = {$id} AND [2ndHalf] = {$second_half}) AS [SecondHalf], …

or:

… WHERE [Employee ID] = " . $id . " AND [2ndHalf] = " . $second_half . ") AS [SecondHalf], …

If a value is text, quotes are needed:

… WHERE [Employee ID] = '{$id}' AND …

PHP ODBC Data Type Mismatch (MS Access)

1 Answers