note 86893 deleted from function.odbc-exec by danbrown
| From: | danbrown@php.net | Date: | Tue, 11 Nov 2008 23:44:55 +0000 |
| Subject: | note 86893 deleted from function.odbc-exec by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-146406@lists.php.net to get a copy of this message | ||
Note Submitter: Tov @ Networking Innovations LLC
----
I was attempting to setup a small search engine for an ODBC, Access, database. Was having several
problems with the odbc_exec/odbc_execute/odbc_prepare functions. My problem relied on the gathering
of static information from a form and using that information in the query string. I kept on
receiving the "Syntax error (missing operator) in query expression [query] etc..."
form:
<?php
echo "<strong>Search $table</strong><br><form name='search'
method='GET' action='".$_SERVER['PHP_SELF']."'>
Search: <input name='search' type='text' value='Search...'
maxlength='50'><br>
Choose Field To Search By: "; echo "<select name='search_field'>";
for ($i=1; $i <= odbc_num_fields($rs); $i++)
{
$r_a = $return_array[$i-1] = odbc_field_name($rs, $i);
echo"<br>\n<option
value='".$r_a."'>".$r_a."</option>";
}
echo "</select>
<br><input type='submit' name='submit_search'
value='Submit'>
<input type='hidden' name='find_record'
value='".$table."'>
</form>";
?>
Search function
<?php
global $dsn;
global $user;
global $pass;
$table = $_GET['find_record'];
$search_field = $_GET['search_field'];
$search = $_GET['search'];
$conn=odbc_connect($dsn,$user,$pass);
$sql = "SELECT * FROM [$table] WHERE $search_field LIKE '".$search."'
";
$resu = odbc_exec($conn,$sql);
$num_field= odbc_num_fields($resu);
while(odbc_fetch_row($resu))
{
echo"<table>";
for($i=1; $i <= $num_field; $i++)
{
$r_a = $return_array[$i-1] = odbc_field_name($resu, $i);
echo"<tr width='100'><td
width='200'>".$r_a.":</td>";
echo "<td
width='400'>".odbc_result($resu,$i)."</td></tr>";
}
echo" </table><br>";
}
odbc_close($conn);
}
?>
The error was reporting on the $sql = "SELECT * FROM..."
It took me quite some time to figure out that it was the $search_field that was giving me the
problem and not what I was searching for, $search. After some time pondering why some of the tables
could search with certain fields I realized that the fields I was trying to search had spaces in
the field name. Thus to fix such problem I added the braces around the search_field variable in the
query string to read as:
$sql = "SELECT * FROM [$table] WHERE [$search_field] LIKE '".$search."'
";
I hope this helps some out so they don't have to go through the hours of thought to solve the
same kind of issue.