NYCPHP Meetup

[Kristina Anderson]

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
> 
>