Search results are iffy

[devknob]

Anyone see a reason this wouldnt work?

SELECT * FROM 'listings' 
WHERE 'siteaddy' LIKE '%siteaddy%' OR
'ccontact' LIKE '%name%' OR
'ccompany' LIKE '%cname%' OR
'tenant' LIKE '%tenant%' OR
'sitestate' LIKE '%st%' OR
'propertytype' LIKE '%type%' OR
'askingprice' BETWEEN '0' && '10' OR
'caprate' BETWEEN '0' && '10' OR
'cashoncash' BETWEEN '0' && '10'
ORDER BY id DESC LIMIT 0 , 30

[Rhysyngsun]

Two things to change:

'listings' 

to either

listings

or

`listings`

 

and then change your &&'s to AND

 

full code:

 

SELECT * FROM listings
WHERE 'siteaddy' LIKE '%siteaddy%'
OR 'ccontact' LIKE '%name%'
OR 'ccompany' LIKE '%cname%'
OR 'tenant' LIKE '%tenant%'
OR 'sitestate' LIKE '%st%'
OR 'propertytype' LIKE '%type%'
OR 'askingprice' BETWEEN '0' AND '10'
OR 'caprate' BETWEEN '0' AND '10'
OR 'cashoncash' BETWEEN '0' AND '10'
ORDER BY id DESC LIMIT 0 , 30 

[fenway]

I'd put parenthesis around your BETWEEN clauses... ANDs and ORs don't mix.

[devknob]

Im using this query to take search for items in our db

mysql_query("SELECT * FROM listings 
WHERE 
siteaddy LIKE '%$siteaddy%' AND
ccontact LIKE '%$name%' AND
ccompany LIKE '%$cname%' AND
tenant LIKE '%$tenant%' AND
sitestate LIKE '%$st%' AND
propertytype LIKE '%$type%' OR
(askingprice BETWEEN '$prf' AND '$prt') OR
(caprate BETWEEN '$capf' AND '$capt') OR
(cashoncash BETWEEN '$cocf' AND '$coct')
ORDER BY lid DESC LIMIT 0 , 30");

and if $st = CA, and nothing else is set, it will still return items in states other than CA. What am I missing?

[teng84]

i think the problem is that u use AND

you have tons of and so i guess it only satisfy your the state but not the other ( and )

[fenway]

No need to double-post; I've merged these topics...

 

and if $st = CA, and nothing else is set, it will still return items in states other than CA. What am I missing?

That's because if you where to echo your query (which you didn't) you'd see field LIKE '%%' which matches everything.  You'll have to build the where clause dynamically.