On Thu, April 28, 2005 3:17 pm, Nick Zukin said: > I have a request from a client that has me a little confounded. He wants > to > have a system where when someone posts a public request with certain > criteria, that criteria is then used to find established profiles/agents > that fit the criteria. Then emails are sent out to the people > corresponding > to those profiles/agents. > > An example: > > Someone posts an ad saying they have a car for sale: > > MODEL: Chevy > MAKE: Silverado > YEAR: 2005 > > Meanwhile, potential clients have saved search agents so that when a car > is > posted if the description meets their criteria they are sent an email. > Thus, > you could have three people, each with agents such as these: > > PERSON1 > MODEL: Chevy > MAKE: Silverado > YEAR (Newer than): 2003 > > PERSON2 > MODEL: Chevy > MAKE: Any > YEAR (Newer than): 2004 > > PERSON3 > MODEL: Any > MAKE: Any > YEAR (Newer than): 2000 > > You would like all of these people to be emailed after the ad is posted, > but > how? This won't work: > > SELECT * FROM agents WHERE mymodel = "Chevy" AND mymake = "Silverado" AND > myyear <= 2005 But this will: WHERE (mymodel = 'Chevy' OR mymodel = 'Any') AND (mymake = 'Silverado' OR mymake = 'Any') AND (myyear <= 2005 OR myyear IS NULL) You probably should use NULL to represent 'Any' and just use IS NULL in all three. > It will limit the results too much. But neither will this: > > SELECT * FROM agents WHERE mymodel = "Chevy" OR mymake = "Silverado" OR > myyear <= 2005 > > That will give too many results. If you had a profile such as: > > PERSON4 > MODEL: Chevy > MAKE: Corvette > YEAR (Newer than): 2003 > > They would be sent the email, too. But they aren't looking for trucks. This, at least, is clearly addressed by the above. I can't guarantee it will address *EVERY* profile -- Only time and practice will tell you that. > Two options I see would be to: > > 1) Do it in reverse. Each time a new ad is posted to then loop through > the > agents doing a search for the criteria and limiting the search to only the > previously posted ad, such as: > > SELECT * FROM ads WHERE admodel = "Chevy" AND admake = "Silverado" AND > adyear >= 2003 AND adid = 8 This won't work any better if the ad doesn't bother to list Make or Year or... Plus, you haven't really addressed the issue of somebody who wants a Chevy (no make specified) newer than 2003. > 2) Create some crazily complex query with nested ands and ors out the > wazoo. I wouldn't say my query was crazily complex... But if it is, then so be it. > Is there something I'm not seeing? Is there a better way? If not, which > of > these seems like less of a strain on the server? Create keys on all the search columns. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
