I think I didn't explain correctly -- the remote zip and the employer's zip are two different values, if a job is in a different location from where the employer lives then that field will be populated. This would be a ton easier if they were the same value or if I had the luxury of redoing the database structure, which I do not at this stage of the game (right before deploy).
If anyone has any suggestions, thanks! -- Kristina > On Saturday 07 June 2008, Kristina Anderson wrote: > > > I have three tables, an Employer table, a Provider table and a Jobs > > table. Each Provider (worker) has up to nine zip codes and a possible > > metro area which contains all the zip codes in their city which is > > their "service area". > > > > When they log on, I need to display only the Jobs in any of their zip > > codes. > > > The easiest thing would be to always populate the zip code in the Jobs > table. Don't call it "remote zip", call it "job zip" and populate it when > you create the job whether it's the same as the employer's zip or not. > > > SELECT * From Jobs INNER JOIN Employees ON (Employees.EmployeeID = > $EmployeeID) AND ((Employees.zip1 = Jobs.jobzip) OR (Employees.zip2 = > Jobs.jobzip) OR (Employees.zip3 = Jobs.jobzip)) > > or let's say you want to show the employer too... > > SELECT * FROM Jobs INNER JOIN Employees ON ((Employees.EmployeeID = > $EmployeeID) AND ((Employees.zip1 = Jobs.jobzip) OR (Employees.zip2 = > Jobs.jobzip) OR (Employees.zip3 = Jobs.jobzip))) LEFT JOIN Employers ON > (Jobs.EmpID = Employers.EmpID) > > > Now, you can still do it in one query even if you don't change the job zip > code as I suggested above - you'll have to sub-select the zip from the > employer table, left as an exercise for the reader... > > As a general rule, if you can make the database do your mix and matching, I > prefer to do it there rather than in arrays. In 99% percent of cases the > database will happily give you exactly what you need if you just ask it > nicely. > > > Michael Sims > > _______________________________________________ > New York PHP Community Talk Mailing List > http://lists.nyphp.org/mailman/listinfo/talk > > NYPHPCon 2006 Presentations Online > http://www.nyphpcon.com > > Show Your Participation in New York PHP > http://www.nyphp.org/show_participation.php > > _______________________________________________ New York PHP Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
