Search query from multiple MySQL fields

Question

Anyone can help me ... Here are two fields: one name, and the other field is email. I want to search by name or email, but it does not work, it is only a 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 data when searching for a name and email?

Answer 1

You have = for your email

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email ='%".$email."%'"; 

Must be

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email like '%".$email."%'"; 

Other wise, without similar emails, literally (=) try to match any emails in your database that are% ". $ Email." %

Answer 2

there is a big difference between AND - and OR state. If you want to access both by email and by name, use AND . Also, if you use% variables, use LIKE instead of the = operator, since the latter compares the values ​​for equality, and LIKE compares, matching the values.

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' AND user_email LIKE '%".$email."%'"; 

The difference between AND and OR is in their value fields.

  b1 | b2 | OR | AND **************************** true | true | true | true true | false| true | false false| true | true | false false| false| false| false 

(b1 = hit by username, b2 = hit by email), so he selects only the username with OR -statement, because he already has a match.

Answer 3

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email ='%".$email."%'"; 

You cannot use = and wrap the variable in %

try the following:

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email like '%".$email."%'"; 

or that:

 $sql=" SELECT * FROM users WHERE fname like '%".$name."%' OR user_email ='".$email."'"; 

Anyone should work. If you just change OR to AND , as another answer suggests, it still won’t work properly. user_email = '%".$email."%'" will find email addresses that start and end with %`.

Answer 4

Chinmai, I don't have SQL Server for testing, but here are some things you can try to shed some light on the reason.

What happens if you replace the SQL statement so that it checks user_email first?
Or ONLY for user_email?
Can you register SQL to validate this correctly?
And as James says, do you intend to do LIKE or an email equality search?

Also, since you create SQL directly from the input (and BTW, leaving it open for SQL injection attacks), is there a "@" special character in MS SQL expressions?

Answer 5

Ideal for many columns / 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"]; } ?>