CODEWITHSUNDEEP
phpmysqlselect
user1622003
user1622003

Reputation: 1

multiple where clauses php

I was wondering if someone could help me. The following code will produce all users where the type = bus but wont produce only the approved = 1. I would be greatful if someone could point out my mistake. The search query is tiggered by someone entering a word into a text box through a form, this part works fine, only users with the search word in there profile appear and only them with type=bus but approved = 1 seems to have no effect and returns all approved, I really hope someone can help.

$query = "select * from users where type='bus' and approved=1 and
name like \"%$trimmed%\"  
or profile_words like \"%$trimmed%\"  
or full_name like \"%$trimmed%\"  
or tag_line like \"%$trimmed%\"  
or referral like \"%$trimmed%\"  
or profession like \"%$trimmed%\"  
order by full_name";

Upvotes: 0

Views: 195

Answers (4)

John Woo
John Woo

Reputation: 263893

Group your condition like this, 

$query = "
select *
from users 
where  type='bus' and 
       approved = 1 and
          (
             name like \"%$trimmed%\" or 
             profile_words like \"%$trimmed%\"  or 
             full_name like \"%$trimmed%\"   or 
             tag_line like \"%$trimmed%\"  or 
             referral like \"%$trimmed%\" or 
             profession like \"%$trimmed%\
          )
order by full_name";

I have a suggestion for you, please use either PDO or MySQLI extension to avoid from SQL Injection.

Example of PDO,

<?php

$query = "
select *
from users 
where  type='bus' and 
       approved = 1 and
          (
             name like ? or 
             profile_words like ?  or 
             full_name like ?   or 
             tag_line like ?  or 
             referral like ? or 
             profession like ?
          )
order by full_name";

$name = '%' . $trimmed . '%';

$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $name);
$stmt->bindParam(3, $name);
$stmt->bindParam(4, $name);
$stmt->bindParam(5, $name);
$stmt->bindParam(6, $name);

$stmt->execute();

?>

Upvotes: 1

petesiss
petesiss

Reputation: 994

Bracket your where operators. Currently you are getting 

where (type='bus' and approved=1)
or .... [all the other operators]

Upvotes: 0

Nikola
Nikola

Reputation: 15048

I think your part for approved is fine, but I think the problem may be in the starting of the OR. Try putting brackets by separating the AND and OR statements like this:

$query = "select * from users where type='bus' and approved=1 and
( name like \"%$trimmed%\"  
or profile_words like \"%$trimmed%\"  
or full_name like \"%$trimmed%\"  
or tag_line like \"%$trimmed%\"  
or referral like \"%$trimmed%\"  
or profession like \"%$trimmed%\" )  
order by full_name";

Upvotes: 0

Del Pedro
Del Pedro

Reputation: 1213

$query = "select * from users where type='bus' and approved=1 and
(name like \"%$trimmed%\"  
or profile_words like \"%$trimmed%\"  
or full_name like \"%$trimmed%\"  
or tag_line like \"%$trimmed%\"  
or referral like \"%$trimmed%\"  
or profession like \"%$trimmed%\")  
order by full_name";

?

Upvotes: 0

Related Questions