我有一个包含车辆信息的数据库(品牌,型号,年份,分支等)
我需要创建一个搜索表单来搜索这个数据库。搜索表单由4个选择框和2个文本框组成。
请查看下面试图查询数据库的代码:
<?php
$dbName = "F:/Domains/autodeal/autodeal.co.za/wwwroot/newsite/db/savvyautoweb.mdb";
// Throws an error if the database cannot be found
if (!file_exists($dbName)) {
die("Could not find database file.");
}
// Connects to the database
// Assumes there is no username or password
$conn = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbName", '', '');
if (isset($_REQUEST['submit'])) {
$searchMake = addslashes($_POST['makeSelection']);
$searchModel = addslashes($_POST['modelSelection']);
$searchBranch = addslashes($_POST['branchSelection']);
$searchYear = addslashes($_POST['yearSelection']);
$minPrice = addslashes($_POST['minPriceSelection']);
$maxPrice = addslashes($_POST['maxPriceSelection']);
$sql = "SELECT Id, Make, Model, Year, Price, SpecialPrice, Branch, StockNO FROM Vehicle WHERE Price >= '$minPrice' AND Price <= '$maxPrice' AND Make LIKE '$searchMake' AND Model LIKE '$searchModel' AND Branch LIKE '$searchBranch' AND Year LIKE '$searchYear'";
$rs = odbc_exec($conn, $sql);
} else {
$sql = "SELECT Id, Make, Model, Year, Price, SpecialPrice, Branch, StockNO FROM Vehicle ORDER BY Make";
$rs = odbc_exec($conn, $sql);
}
echo "t" . "<tr>n";
echo "t" . "<th>Make</th><th>Model</th><th>Year</th><th>Price</th><th>Special Price</th><th>Location</th><th>Stock Number</th>" . "n";
while (odbc_fetch_row($rs)) {
$id = odbc_result($rs, Id);
$make = odbc_result($rs, Make);
$model = odbc_result($rs, Model);
$year = odbc_result($rs, Year);
$price = odbc_result($rs, Price);
$specialPrice = odbc_result($rs, SpecialPrice);
$branch = odbc_result($rs, Branch);
$stockNo = odbc_result($rs, StockNO);
echo "t" . "<tr>n";
echo "tt" . "<td>" . $make . "</td><td><a href=/newsite/selected-vehicles?Id=$id>" . $model . "</a></td><td>" . $year . "</td><td>" . $price . "</td><td>" . $specialPrice . "</td><td>" . $branch . "</td><td>" . $stockNo . "</td>n";
echo "t" . "</tr>n";
}
odbc_free_result($rs);
odbc_close($conn);
// This message is displayed if the query has an error in it
if (!$rs) {
exit("There is an error in the SQL!");
}
?>
当我运行这个脚本时,出现了"there is an error in the SQL"的消息。
任何帮助都将是非常感激的。
Thannk你
根据用户输入进行的sql查询应该进行修改,因为它很有可能会失败,因为用户可能会或可能不会输入所有文本框。基本上,您使用if else
条件创建动态查询
$sql = "SELECT Id, Make, Model, Year, Price, SpecialPrice, Branch, StockNO FROM Vehicle WHERE 1 = 1;
if ($minPrice > -1 )
{
$sql .= $sql " and Price >= $minPrice ";
}
同样,对于其他操作符和比较操作符>
<
,您不应该在price中添加单引号。
尝试打印你的查询并检查它是如何形成的-