php 从多个字段搜索查询 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18161660/
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
Search query from multiple fields MySQL
提问by Chinmay235
Anyone can help me... Here is two fields one is name and another field is email. I want to search by name or email but it doesnt work, it only search by name
任何人都可以帮助我...这里有两个字段，一个是姓名，另一个字段是电子邮件。我想按姓名或电子邮件搜索，但不起作用，只能按姓名搜索
<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("chinmay", $con) or die("ERROR");
if(isset($_REQUEST['submit'])){
$name=$_POST['name'];
$email=$_POST['email'];
$sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email ='%".$email."%'";
$q=mysql_query($sql);
}
else{
$sql="SELECT * FROM users";
$q=mysql_query($sql);
}
?>
<form method="post">
<table width="200" border="1">
<tr>
<td>Name</td>
<td><input type="text" name="name" value="<?php echo $name;?>" /></td>
<td>Email</td>
<td><input type="text" name="email" value="<?php echo $email;?>" /></td>
<td><input type="submit" name="submit" value=" Find " /></td>
</tr>
</table>
</form>
<table>
<tr>
<td>Name</td>
<td>Email</td>
<td>Address</td>
</tr>
<?php
while($res=mysql_fetch_array($q)){
?>
<tr>
<td><?php echo $res['fname'].' '.$res['lname'];?></td>
<td><?php echo $res['user_email'];?></td>
<td><?php echo $res['address'];?></td>
</tr>
<?php }?>
</table>
How to get the data when i search both name and email?
当我搜索姓名和电子邮件时如何获取数据？
回答by James
You have = for your email
您的电子邮件有 =
$sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email ='%".$email."%'";
Should be
应该
$sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email like '%".$email."%'";
Other wise without the like on email, it is literally (=) trying to match any emails in your DB which are %".$email."%
其他明智的没有像电子邮件一样，它实际上是 (=) 试图匹配您数据库中的任何电子邮件，即 %".$email."%
回答by KarelG
there is a major difference between AND- and OR-statement. If you want a hit on both email and name, use AND. Also if you are using % variables, use LIKEinstead of =operator, since the last compares the values for equality while LIKEcompares by matching the values.
AND- 和 -OR语句之间存在重大差异。如果您想同时获得电子邮件和姓名，请使用AND. 此外，如果您使用 % 变量，请使用LIKE而不是=运算符，因为最后一个比较相等的值，而LIKE通过匹配值进行比较。
$sql=" SELECT * FROM users WHERE fname like '%".$name."%' AND user_email LIKE '%".$email."%'";
The difference between ANDand ORis in their value fields.
AND和之间的区别在于OR它们的值字段。
b1 | b2 | OR | AND
****************************
true | true | true | true
true | false| true | false
false| true | true | false
false| false| false| false
(b1 = hit on username, b2 = hit on email)that's why it's only selecting the username using an OR-statement because it already has a match.
（b1 = 点击用户名，b2 = 点击电子邮件）这就是为什么它只使用 -OR语句选择用户名，因为它已经有匹配项。
回答by wesleywmd
$sql=" SELECT * FROM users WHERE fname like '%".$name."%'
OR user_email ='%".$email."%'";
You cannot use =and wrap you variable in%
你不能使用=和包装你的变量%
try this:
尝试这个：
$sql=" SELECT * FROM users WHERE fname like '%".$name."%'
OR user_email like '%".$email."%'";
or this:
或这个：
$sql=" SELECT * FROM users WHERE fname like '%".$name."%'
OR user_email ='".$email."'";
Either one should work. If you simple change the ORto an ANDlike the other answer suggests, it still will not work properly. user_email = '%".$email."%'" will find email addresses that start and end with%`.
任何一个都应该工作。如果你简单的改变OR，以一个AND像其他的答案表明，它仍然无法正常工作。user_email = '%".$email."%'" will find email addresses that start and end with%`。
回答by Michael Lemaire
Chinmay, I don't have a SQL Server available to test against, but here's some things to try that might shed some light on the cause.
Chinmay，我没有可用于测试的 SQL Server，但这里有一些可以尝试的事情，可能会阐明原因。
What happens if you swap around the SQL expression so that it tests user_email first?
Or ONLY search for user_email?
Can you log the SQL to confirm it's correct?
And as James says, do you intend to do a LIKE or equality search on the email?
如果交换 SQL 表达式以便它首先测试 user_email 会发生什么？
还是只搜索 user_email？
你能记录 SQL 以确认它是正确的吗？
正如詹姆斯所说，您打算在电子邮件上进行“喜欢”或“平等”搜索吗？
Also, since you're constructing the SQL directly from the input (and BTW that's leaving it open for an SQL injection attack) is "@" a special character in MS SQL expressions?
另外，由于您是直接从输入构建 SQL（顺便说一句，这让它对 SQL 注入攻击开放）是 MS SQL 表达式中的“@”特殊字符吗？
回答by xxxxxx
Ideal for many Columns / geeignet für sehr viele Spalten / dla wielu kolumny
适用于许多列 / geeignet für sehr viele Spalten / dla wielu kolumny
<?php
//YOUR TABLE/DEINE TABELLE/TWOJA TABELA
$table="table";
//YOUR SEARCHSTRING/DEIN SUCHSTRING/SlOWO DO WYSZUKIWANIA
$nichtsichererstring="searchstring";
$suchstring=mysql_real_escape_string($nichtsichererstring);
$query = "SELECT * from ".$table." where ( ";
$results = mysql_query("DESCRIBE ".$table);
$zaehler=mysql_num_rows($results);
$x=1;
while($row = mysql_fetch_array($results)) {
$query = $query.$row["Field"]." LIKE '%".$suchstring."%' ";
if($x<$zaehler){
$query = $query." OR ";
}
$x++;
}
$query =$query.")";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
//YOUR RESULTS!/DEINE ERGEBNISE/TWOJE WYNIKI
echo $row["titel"];
}
?>
