bagofmilk bagofmilk - 7 months ago 14
PHP Question

MySQL SELECT statement COUNT error

EDIT - Using PHP I made an ODBC connection to a local Access database

I'm getting the following error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect , SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\cpanel\modelsearch\test.php on line 37
Error in SQL


I have a feeling some of the syntax is wrong here for an ODBC connection

$sql="SELECT `ITEM`, `DESCRIPTION`, `CUS_LOC_NAME`, `MECH_REL_BY`, `PM_DESIGN`, `SHIP_ACTUAL`, `HPL`, `WO_LINE`, `SO` FROM `Schedule` WHERE `HPL` <> 'PART' AND (LEFT(`DESCRIPTION`,6)=?) AND (LEFT(`CUS_LOC_NAME`,6)=?)";


This statement works fine in Access, but its not translating to MySQL very well. What syntax am i missing here?

FULL CODE RECENTLY EDITED TO REMOVE PDO STATEMENT:

<?php
date_default_timezone_set('America/Chicago');

$sql="SELECT `ITEM`, `DESCRIPTION`, `CUS_LOC_NAME`, `MECH_REL_BY`, `PM_DESIGN`, `SHIP_ACTUAL`, `HPL`, `WO_LINE`, `SO` FROM `Schedule` WHERE `HPL` <> 'PART' AND (LEFT(`DESCRIPTION`,6)=?) AND (LEFT(`CUS_LOC_NAME`,6)=?)";
$rs=odbc_exec($conn,$sql);
if (!$rs) {
exit("Error in SQL");
}

echo "<table><tr>";
echo "<th>ITEM</th>";
echo "<th>DESCRIPTION</th>";
echo "<th>CUS_LOC_NAME</th>";
echo "<th>MECH_REL_BY</th>";
echo "<th>PM_DESIGN</th>";
echo "<th>SHIP_ACTUAL</th>";
echo "<th>HPL</th>";
echo "<th>WO_LINE</th>";
echo "<th>SO</th></tr>";

while (odbc_fetch_row($rs)) {
$item=odbc_result($rs,"ITEM");
$desc=odbc_result($rs,"DESCRIPTION");
$cus=odbc_result($rs,"CUS_LOC_NAME");
$mech=odbc_result($rs,"MECH_REL_BY");
$pm_design=odbc_result($rs,"PM_DESIGN");
$ship=odbc_result($rs,"SHIP_ACTUAL");
$hpl=odbc_result($rs,"HPL");
$wo=odbc_result($rs,"WO_LINE");
$so=odbc_result($rs,"SO");

echo "<tr><td>$item</td>";
echo "<td>$desc</td>";
echo "<td>$cus</td>";
echo "<td>$mech</td>";
echo "<td>$pm_design</td>";
echo "<td>$ship</td>";
echo "<td>$hpl</td>";
echo "<td>$wo</td>";
echo "<td>$so</td></tr>";
}
odbc_close($conn);
echo "</table>";


?>


THE error is at this line: `$rs=odbc_exec($conn,$sql);

This code works just fine when I edit the $sql statement to remove the ands:

$sql="SELECT `ITEM`, `DESCRIPTION`, `CUS_LOC_NAME`, `MECH_REL_BY`, `PM_DESIGN`, `SHIP_ACTUAL`, `HPL`, `WO_LINE`, `SO` FROM `Schedule` WHERE `HPL` <> 'PART'";


So the obvious error is the LEFT() functions. Can someone advise on how to approach this? Also, I'm not sure what the =? means.

Answer

Your problem was that your query included placeholders (?) for a prepared statement, but you were treating it as a normal query. Prepared statements need to be prepared with odbc_prepare() and then executed with odbc_execute().

<?php
date_default_timezone_set("America/Chicago");

$conn = odbc_connect("Prod_Schedule", "", "");
if (!$conn) {
    exit("Connection Failed: $conn");
}

$sql="SELECT `ITEM`, `DESCRIPTION`, `CUS_LOC_NAME`, `MECH_REL_BY`, `PM_DESIGN`, `SHIP_ACTUAL`, `HPL`, `WO_LINE`, `SO` FROM `Schedule` WHERE `HPL` <> 'PART' AND (LEFT(`DESCRIPTION`,6)=?) AND (LEFT(`CUS_LOC_NAME`,6)=?)";

$stmt = odbc_prepare($conn, $sql);
$params = array("value for first ?", "value for second ?");
$result = odbc_execute($stmt, $params);

if ($result) {
    odbc_result_all($stmt);
}
?>

(Who would have guessed there would be a dedicated function for printing a result set as an HTML table!!?)

Comments