NYCPHP Meetup

[Michael Sims]

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