Page 1 of 1
can where clause contain OR?
Posted: Mon Sep 01, 2008 9:01 pm
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?
Re: can where clause contain OR?
Posted: Mon Sep 01, 2008 9:49 pm
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.
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 12:21 am
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?
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 3:57 am
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?
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 4:21 am
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
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 4:42 am
by m2babaey
let me divide it :
1. how can I check all array elements are null? ( line

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
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 6:21 am
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.
Re: can where clause contain OR?
Posted: Tue Sep 02, 2008 7:26 am
by m2babaey
thanks for your help
line 8 is in 3rd post
#