property_id facility_id ----------- ----------- 0000000001 0000000001 0000000001 0000000003 0000000001 0000000004 0000000001 0000000010 0000000002 0000000009 0000000002 0000000002 0000000002 0000000001 0000000003 0000000008 0000000003 0000000007 0000000003 0000000001 0000000009 0000000011 0000000002 0000000003 0000000002 0000000004 0000000003 0000000003 0000000003 0000000004
using self-joins (see http://www.devshed.com/Server_Side/MySQL/Join/page5.html or Google "mySQL JOIN") you can do a query like this to find any properties that have facilities 3,4 AND 10.
select parent.property_id
from property_facilities as parent, property_facilities as a, property_facilities as b
where parent.property_id = a.property_id
and parent.property_id = b.property_id
and parent.facility_id = '0000000003'
and a.facility_id = '0000000004'
and b.facility_id = '0000000010';
You would use PHP then to generate the sql and create as many self-joins as necessary. Not an elegant solution - as if you had to do a search on about say 15 facilities you could have a 30 line where clause. Can anyone suggest a more "elegant" solution using SQL?
Thanks.
Vinny
At 11:34 AM 3/09/2003 +0100, Kae Verens wrote:
Ralph Guzman wrote:The second approach is to store amenities as a text delimited string for each property in the facilities table. So if a certain property has a pool, fireplace and fitness center, I would have 'pool|fireplace|fitness' in the amenities field. I can then use PHP to explode or create an array so that I can manipulate the string. Anybody have any thoughts or advice on this?
my first thought is that it's not necessary to explode with PHP.
this will return true for something like 'pool|fireplace|fitness' mysql_query('select id from theTable where amenities like "%pool%"')
you could make it more efficient by using boolean logic:
if 1=pool,2=fireplace,4=fitness, then 3=fireplace+pool, 5=fitness+pool, 7=fitness+fireplace+pool
just thoughts.
Kae
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
