can where clause contain OR?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m2babaey
Forum Contributor
Posts: 364
Joined: Sun May 20, 2007 9:26 am

can where clause contain OR?

Post by m2babaey »

Hi
can i have such a sql code?:
select from table where ( keyword like '%$keyword%' or color like '%$color%' ) and (category=2 or category=3)
plus you help with this question, do you have a better idea?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: can where clause contain OR?

Post by califdon »

m2babaey wrote:Hi
can i have such a sql code?:
select from table where ( keyword like '%$keyword%' or color like '%$color%' ) and (category=2 or category=3)
plus you help with this question, do you have a better idea?
Definitely. That's very common. It works just like you'd expect.
m2babaey
Forum Contributor
Posts: 364
Joined: Sun May 20, 2007 9:26 am

Re: can where clause contain OR?

Post by m2babaey »

this is my code:

Code: Select all

 
<?php
if($subcat_array==0) $rest=1;
else{
$r=1;
$toadd="";
while($go_up-$r+1){
if($subcat_array[$r]){
//add or if $toadd not empty:
if($toadd) $toadd.=" OR ";
$toadd="(ps.subcategoryID='$subcat_array[$r]' AND ps.productID=pp.productID ) ";
$rest=$rest.$toadd;
}
$r++;
}
}
    $search="SELECT * FROM `pp.products`,ps.prod_subcat WHERE (`pp.productID` LIKE '%$key%' OR  `pp.productName` LIKE '%$key%' OR `pp.productDescription` LIKE '%$key%' OR `pp.productColour` LIKE '%$key%' OR `pp.productID` LIKE '%$code%' OR `pp.productName` LIKE '%$name%' OR `pp.productColour` LIKE '%$colour%' ORDER BY pp.productID LIMIT $limit1,50 ) AND ($rest)";
?>

my purpose is to search a product from the subcategories that their checkbox have been checked
1 product can be in more than 1 subcategory so the subcat_id is not in product table but the product id and subcategories id are in prod_subcat table.
So what corrections to the code above is required for it to work?
Last edited by m2babaey on Tue Sep 02, 2008 4:18 am, edited 1 time in total.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: can where clause contain OR?

Post by matthijs »

If you would use code=php tags around the code it will be much more readable. Also, neatly indenting the code would help a lot. It'll make it easier for people here to look at your code and help you with your question

Last, I hope you do escape the data used in the query?
m2babaey
Forum Contributor
Posts: 364
Joined: Sun May 20, 2007 9:26 am

Re: can where clause contain OR?

Post by m2babaey »

thanks for your interest to help me
i did so, but from the time that DN upgraded to phpbb3 i used to find

Code: Select all

not working
thank you
m2babaey
Forum Contributor
Posts: 364
Joined: Sun May 20, 2007 9:26 am

Re: can where clause contain OR?

Post by m2babaey »

let me divide it :
1. how can I check all array elements are null? ( line 8)
2. can I select everything when joing 2 tables? like this:

Code: Select all

 
$search="select * from products, prod_subcat where products.productID=prod_subcat.productID ";
 
3. i have still problem with sql syntax:

Code: Select all

 
    $search="SELECT * FROM `products` WHERE (`productID` LIKE '%$key%' OR  `productName` LIKE '%$key%' OR 
`productDescription` LIKE '%$key%' OR `productColour` LIKE '%$key%' OR `productID` LIKE '%$code%' OR `productName` LIKE 
'%$name%' OR `productColour` LIKE '%$colour%' ORDER BY productID LIMIT $limit1,50 ) AND (1)";
 

will throw out:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY productID LIMIT 0,50 ) AND (1)' at line 1
thanks
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: can where clause contain OR?

Post by jayshields »

1. There is no line 8.
2. Yes.
3. You need to close your opening bracket (after WHERE) before your ORDER BY clause. Also, the ORDER BY clause should be the last thing on your query. Check the SELECT syntax on the MySQL manual.
m2babaey
Forum Contributor
Posts: 364
Joined: Sun May 20, 2007 9:26 am

Re: can where clause contain OR?

Post by m2babaey »

thanks for your help
line 8 is in 3rd post
#

Code: Select all

if($subcat_array[$r]){
 
Post Reply