SELECTING from Multiple Rows?

[godwheel]

Heya Guys,

Quick question, my friend and I have been going nuts over this because we cant figure it out, we've tried subqueries and the such and nothing.

We have a form that has multiple checkboxes:

Community Features
[] Pool
[] Tennis Courts
[] Concierge
[] ...

We built our database as follows (kept it brief for sanity purposes)

LISTINGS
listingID, address
1001, 9000 NE 2nd Avenue
1002, 1234 Easy Street

PROPERTY FEATURES
propFeatID, featName
1, Pool
2, Tennis Courts
3, Concierge
4. ...

Linking the two, is a FeatureLinker
listingID, propFeatID
1001, 1
1001, 3

THE QUESTION
Say a property we wanted to find a property that has more than one of those features, in this example property 1001, has more than one feature (Pool, Concierge). How would you go about doing that?

We tried: SELECT listingID FROM FeatureLinker WHERE propFeatID = 1 AND propFeatID = 3.

The reason that didn't work is because it is a column, I'm looking for rows. So we've determined that.

How should I go about tackling that? I tried a few subqueries but my mind kept looping around into nothingness.

Thanks 😃

[NogDog]

I would create the form fields with an array-style name:

<input type="checkbox" name="features[]" value="1" />Pool

Then you could process the results as:

<?php
if(count($_POST['features'])) // at least one was selected
{
   $features = array();
   foreach($_POST['features'] as $value)
   {
      $features = (int)$value; // make sure they're all integers
   }
   $sql = "SELECT * FROM listings WHERE listingID IN (" .
          "SELECT DISTINCT listingID FROM FeatureLinker WHERE " .
          "propFeatID IN (" . implode(',', $features) . "))";
   );
   // run query, etc....
}
?>

[tsinka]

If you want to make sure to only fetch the ids that have records for every selected feature then you may need to use n inner joins on the linker table (and query based on rows and not on columns).

Examples:

SELECT fl1.lngListingID FROM FeatureLinker f1 INNER JOIN FeatureLinker f2 ON f1.listingID = f2.listingID AND f2.propFeatID = 3 WHERE f1.propFeatID = 1

SELECT fl1.lngListingID FROM FeatureLinker f1 INNER JOIN FeatureLinker f2 ON f1.listingID = f2.listingID AND f2.propFeatID = 3 INNER JOIN FeatureLinker f3 ON f1.listingID = f3.listingID AND f3.propFeatID = 4 WHERE f1.propFeatID = 1

I tested similar queries on a MySQL server and the performance was ok. But that depends on the number of features selected.

Using IN isn't sufficient in that case because that would also fetch linstings that have only a subset of the chosen features.

edit:

sorry NogDog ... I read your post too hastily ... your solution works, too. I wonder, which one is more performant

Thomas

[godwheel]

Heya guys,

Thanks for the reply, I'm going to try this out first thing tomorrow and report back (I will also let you know which is faster).

Right now the form is in an array propFeatID[] so that part should work fine.

Anyway, thanks and ill report back.