PHP select * where like
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16423905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Php select * where like
提问by user1944305
Hi I am trying to get a search working for a site. It has 2 inputs for taking in info, one is a dropdown.
嗨，我正在尝试为一个网站进行搜索。它有 2 个用于获取信息的输入，一个是下拉菜单。
<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>
I cannot seem to figure out how to combine the 2 inputs to give a result, I can do it separately, but not working together to get a more accurate result.
我似乎无法弄清楚如何组合 2 个输入来给出结果，我可以单独进行，但不能一起工作以获得更准确的结果。
php
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%'");
Thanks in advance!
提前致谢！
回答by Refugnic Eternium
I think you may do some preprocessing, before running your query.
我认为您可以在运行查询之前进行一些预处理。
First off, you need to give your select options some sort of value to check against.
首先，您需要为选择的选项提供某种值以进行检查。
I don't know your exact database structure, but assuming that you're working with the select texts, you may want to try this:
我不知道您确切的数据库结构，但假设您正在使用选择文本，您可能想尝试以下操作：
$query = "SELECT * FROM projectitem WHERE (description LIKE '%$keywords%' OR item LIKE '%$keywords%')";
This is your base query and running it right now will check against the keywords, but no location.
这是您的基本查询，现在运行它会检查关键字，但不会检查位置。
if($keylocation != "Any location") $query .= " AND location = '$keylocation'";
This last line will add the location as additional filter to your query. Run it, and see what it does. (I'm not sure about the string comparison there though)
最后一行会将位置作为附加过滤器添加到您的查询中。运行它，看看它做了什么。（虽然我不确定那里的字符串比较）
Ah yes, as a final advice: Be sure to run your input through the escape function mysqli_escape_string. Otherwise you're opening yourself to SQL injections.
是的，作为最后的建议：确保通过转义函数运行您的输入mysqli_escape_string。否则，您将面临 SQL 注入。
回答by Ja?ck
You're not actually using the value of $keylocation; to narrow searches down, you need an ANDinstead of OR:
您实际上并没有使用$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.
Update
更新
Since the drop down may have "any location" you would need to dynamically change your query:
由于下拉菜单可能有“任何位置”，您需要动态更改查询：
$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);
回答by Ali Akbar Azizi
first you have sql injection
首先你有sql注入
use mysqli_real_escape_string
用 mysqli_real_escape_string
if keywords for example is null your query will be like this
例如，如果关键字为空，您的查询将是这样的
$result = mysqli_query($con,"SELECT * FROM projectitem where description like '%%' or item like '%%' or location like '%$keylocation%'");
and description like '%%'return all row !
并description like '%%'返回所有行！
you must check data first
你必须先检查数据
$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%'";
