CODEWITHSUNDEEP
phpmysqlsearch
Chinmay235
Chinmay235

Reputation: 3414

Search query from multiple fields MySQL

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?

Upvotes: 4

Views: 92926

Answers (3)

KarelG
KarelG

Reputation: 5234

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 LIKE instead of = operator, since the last compares the values for equality while LIKE compares by 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 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.

Upvotes: 3

wesleywmd
wesleywmd

Reputation: 605

$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 OR to an AND like the other answer suggests, it still will not work properly. user_email = '%".$email."%'" will find email addresses that start and end with%`.

Upvotes: 2

James
James

Reputation: 4783

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."%

Upvotes: 11

Related Questions