using a second table for criteria

nik101 · #1 (**permalink**) 09-28-05, 14:30

hi,

I'm doing a database for renting apartments, and initially my simple database has a table of apartments, with simple criteria. For instance
Name, address, bedrooms, floor...

I'm now trying to make it more generic so it can be re-used by more people, and allow the users to add new criteria (for instance Bedrooms and Floor are pretty standard, but someone might also want Number of Beds, or town district etc).

So now I have;

Apartments: Name, address
Criteria: CriteriaName, apartment, value

it's many Criteria to one Apartment, so we might have
Apartment( 'RoseApt', '23 Abbey Lane')
and
Criteria('Bedrooms', 'RoseApt', '5')
Criteria('Floor', 'RoseApt', '2') etc.

This seemed a good idea until I tried to do a query.
In my previous simple table I could do;

Code:

select * from Apartments where Floor < 4 and Bedrooms = 2;

The closest I could get was;

Code:

SELECT * FROM Apartments inner join Criteria on 
Apartments.Name=Criteria.apartment and ((CriteriaName='Floor' and 
criteriavalue < 4 ) or ( CriteriaName='Bedrooms' and criteriavalue=2 ))

but unsurprisingly this isn't right as it gives a result even if one of the criteria is false (ie Bedrooms = 1 not 2).

What I want is to use 'and' instead of the 'or', but it's obvious why that doesn't work - so how to do I do this? Does it have to break down to a mulit stage process where I try each criteria in turn whittling down the group? This seems like an expensive way of doing things.

Maybe it's just a bad design?

thanks,
nik

pootle flump · #2 (**permalink**) 09-29-05, 04:11

Hi

Something like:

Code:

 
SELECT * 
FROM Apartments inner join 
(SELECT Apartment from Criteria WHERE CriteriaName='Floor' and criteriavalue < 4) AS A on Apartments.Name = A.Apartment INNER JOIN 
(SELECT Apartment from Criteria WHERE CriteriaName='Bedrooms' and criteriavalue = 2) AS B on B.Apartment = A.Apartment

might do it.

I think you are right though - your design sounds a bit fishy - imagine scaling that query up each time you want to add a new set of criteria.

HTH

nik101 · #3 (**permalink**) 09-29-05, 04:59

ah, fantastic - that works. I'm really grateful for that!

Expanding the query for more and more criteria isn't a big deal really - here's my php code;

Code:

#users form creates the query like as an array
$criteria = array();
$criteria[] = array('name'=>'Floor', 'value'=>'3');
$criteria[] = array('name'=>'Bedrooms', 'value'=>'1');

# query is created from array
$query = "select Apartments.apartment from Apartments ";
$i = 0;
foreach ($criteria as $criterion)  {
	$query = $query."inner join (select apartment from 
criteria where criteria.criteriaName='".$criterion['name']."' 
and criteria.criteriavalue = '".$criterion['value']."') as A$i 
on A$i.apartment = Apartments.apartment ";
	$i++;
}

I doubt users will have more than 3 or criteria anyway - my site only uses 3 (bedrooms, people, and arrondissement (paris district)).

My next headache will be accomodating text comparison versus numeric comparison, and also enabling < or > queries... should be doable by adding an operator into that criteria array though.

thanks,
Nik

Internet Services

Web Development

Digital Marketing

Consumer Tech