[nycphp-talk] comparing arrays to build query?
Michael Sims
jellicle at gmail.com
Sun Jun 8 07:18:19 EDT 2008
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
More information about the talk
mailing list