Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: using a second table for criteria

    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
    Last edited by nik101; 09-28-05 at 15:31. Reason: change notification type

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2004
    Posts
    6

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •