嗨,我正试图得到一个搜索工作的网站。它有两个输入输入,一个是下拉菜单。
<div id="search">
<form action="projectsearchall.php" method="post" enctype="multipart/form-data">
<h3>Search for an Item</h3>
<p>Keywords</p><p><input name="keywords" type="text" value="keywords"></p>
<p>Select A Location</p><p>
<select name="location" id="jumpMenu">
<option>Any Location</option>
<option>Antrim</option>
<option>Armagh</option>
<option>Carlow</option>
<option>Cavan</option>
</select>
</p>
<p>
</form>
</div>
我似乎不知道如何结合这两个输入来给出一个结果,我可以单独做,但不能一起工作以获得更准确的结果。
php$keywords = $_POST['keywords'];
$keylocation =$_POST['location'];
$username = $_SESSION['username'];
//MySQL Database Connect
include 'connect.php';
//make sql query
$result = mysqli_query($con,"SELECT * FROM projectitem where description like '%$keywords%' or item like '%$keywords%' or location like '%$keywords%'");
提前感谢!
我想你可以在运行查询之前做一些预处理。
首先,你需要给你的选择选项一些值来检查。
我不知道你确切的数据库结构,但假设你正在使用选择文本,你可能想试试这个:
$query = "SELECT * FROM projectitem WHERE (description LIKE '%$keywords%' OR item LIKE '%$keywords%')";
这是你的基本查询,现在运行它将检查关键字,但没有位置。
if($keylocation != "Any location") $query .= " AND location = '$keylocation'";
最后一行将把位置作为额外的过滤器添加到查询中。运行它,看看它能做什么。(我不确定这里的字符串比较)
啊,是的,作为最后的建议:确保通过转义函数mysqli_escape_string
运行您的输入。
您实际上没有使用$keylocation
的值;要缩小搜索范围,您需要AND
而不是OR
:
$stmt = mysqli_prepare($con, 'SELECT * FROM projectitem
where (description LIKE ? OR item LIKE ?) AND location LIKE ?');
mysqli_stmt_bind_param($stmt, 'sss', "%$keywords%", "%$keywords%", "%$keylocation%");
mysqli_stmt_execute($stmt);
// etc.
因为下拉菜单可能有"任意位置",你需要动态地改变你的查询:
$sql = 'SELECT * FROM projectitem WHERE 1'; // base query
$types = ''; $vars = array();
if (!empty($keywords)) {
$sql .= ' AND (description LIKE ? OR item LIKE ?)';
$types .= 'ss';
$vars[] = "%$keywords%";
$vars[] = "%$keywords%";
}
if ($keylocation != 'Any Location') {
$sql .= ' AND location LIKE ?';
$types .= 's';
$vars[] = $keylocation;
}
$stmt = mysqli_prepare($con, $sql);
if ($types) {
mysqli_stmt_bind_param($stmt, $types, $vars);
}
mysqli_stmt_execute($stmt);
首先是SQL注入
使用mysqli_real_escape_string
例如,如果keywords为null,则查询将如下所示
$result = mysqli_query($con,"SELECT * FROM projectitem where description like '%%' or item like '%%' or location like '%$keylocation%'");
和description like '%%'
返回所有行
你必须先检查数据
$query = "SELECT * FROM projectitem where 1=1 "
if($keywords)
$query .= " AND ( description like '%$keywords%' AND item like '%$keywords%' )";
if($keylocation)
$query .= " AND location like '%$keylocation%'";