title: php mysql list reps by state without multiple rows in rep table
body: <p>I have a problem that I haven't been able to solve in an efficient way. I need to dynamically generate a list of reps for each of the 50 states in the USA. Each state has a variable number of reps that is equal to one or more and which will fluctuate. Each rep can potentially show up in more than one state. To make things more complex, several states are divided into non-standardized regions (e.g. "North", "Upstate", "Central", etc. but not consistent nomenclature across all states). The final product will produce a series of tables similar to what can be seen at <a href="https://www.compatico.com/rep-tables.php" rel="nofollow noreferrer">https://www.compatico.com/rep-tables.php</a>.</p>
  
  <p>Right now, I'm envisioning two database tables:</p>
  
  <pre><code>**StateTable_example1**
  state_id  |  state_name  |  state_reps
  MI            Michigan       1,2,3,4
  OH            Ohio           3,4,5,6,7
  IN            Indiana        2,4,5
  
  **RepTable_example1**
  rep_id  |  rep_fname  |  replname  |  (more columns not relevant here)
  1          John          Smith
  2          Jane          Doe
  etc.
  </code></pre>
  
  <p>One idea that I have to handle regional data is to tack on extra data in the StateTable.state_reps column, sorta like this:</p>
  
  <pre><code>**StateTable_example2**
  state_id  |  state_name  |  state_reps
  MI            Michigan       1-Upper Peninsula,2-Lower Peninsula,3-Western
  OH            Ohio           3-Northern,4-Southern
  IN            Indiana        2-Northern,4-Eastern,5-Western
  </code></pre>
  
  <p>That would allow one row in the RepTable per rep while still allowing for regional variations... which would be great for updating RepTable data for stuff like phone numbers, emails, fax numbers, etc... but I can't begin to wrap my head around how to write a single query in php/mysql to handle that efficiently.</p>
  
  <p>My other idea is to add a column to the RepTable to handle region and then have multiple rows for each rep if they have a position in more than one state... like this:</p>
  
  <pre><code>**StateTable_example3**
  state_id  |  state_name  |  state_reps
  MI            Michigan       1,2,3
  OH            Ohio           4,5
  IN            Indiana        6,7,8
  
  **RepTable_example3**
  rep_id  |  rep_region  |  rep_fname  |  replname  |  (more columns not relevant here)
  1          Upper Peninsula   John          Smith
  2          Lower Peninsula   Jane          Doe
  3          Western           Tom           Jones
  4          Northern          Tom           Jones
  5          Southern          Amy           Wong
  6          Northern          Jane          Doe
  7          Eastern           Amy           Wong
  8          Western           Jim           Johnson
  </code></pre>
  
  <p>I think that the php/mysql statement for this method would be cleaner but then there's the question of updating rep information (which changes frequently) when each rep is represented by multiple rows.</p>
  
  <p>I've thought about a third table, to map the reps to states, but I can't figure out how to do that in a way that allows for disparate regions for each rep (e.g., in the above data Amy Wong's region is "Southern" for Ohio but "Eastern" for Indiana).</p>
  
  <p>Anyone know of a way to do this efficiently, without multiple calls to the database?  Any help would be much appreciated!</p>
  
  <p>*****EDIT****  The final output, while resembling the tables in the link above, will actually be used as input into another script with graphically maps the reps. The table data becomes tooltips. The actual final output will look like this:  <a href="https://www.compatico.com/find-a-rep.php" rel="nofollow noreferrer">https://www.compatico.com/find-a-rep.php</a> (but with more region data, as that's currently lacking).</p>
  
  <p>*****EDIT_2***** I renamed my example tables to clarify.</p>