'WHERE' question

klyve1

How do I add multiple 'WHERE's to a query?
I have tried:
'WHERE cat=1 AND cat=2'
'WHERE cat=1 AND 2'
But they don't want to work for me, what am I missing?
Klyve

Lars_Berg

WHERE cat=1 AND cat=2

is syntaically correct. It is rather useless as it will always be false.

WHERE cat=1 or cat=2

might be what you want. If not, you need to tell what results you expect.

klyve1

I have a lookup table for 'villa' and 'amenity' and I want to find villas that match all amenities chosen. I.e. Villa that has a pool AND TV AND parking.

Lars_Berg

select villa.name from villa inner join amenity on villa.id = amenity.villaid 
where amenity.cat in ('POOL','TV','PARKING')
group by villa.name
having count(*) = 3

Some guessing with regard to your column names.

klyve1

I'm very new to this and although your answer makes some sense I'm not sure how to implement this in what I have already.
Currently I have a page with dynamically generated checkboxes from the lookup tables (yep 2!) and pass them to the result page which looks like this:

$select = "SELECT villa.vID, vName, vAdd1, vTown, vCountry";
$from = " FROM `villa`, `catlookup`, `amenlookup`";
$where_cat = " WHERE villa.vID=catlookup.vID AND villa.vID=amenlookup.vID AND catID=$cats[0]";
$where_am = " AND amenID=$ams[0]";

if (!$cats) {
echo "No categories chosen";
} elseif (!$ams) {
echo "No amenities chosen";
} else
	{
while(list($keyc, $Cat) = each($cats))
{
$where_cat .= " AND $Cat";
}
while(list($keya, $Am) = each($ams))
{
$where_am .= " AND $Am";
}
$query = ($select . $from . $where_cat . $where_am);

Thanks for your help Lars