PHP/MySQL Solution for product request routing

My company offers a video product free of charge to anyone who requests it. This product is distributed through regional representatives, and is available in VHS or DVD. I want to create a page using PHP and MySQL and a form that will allow the customer to simply enter their address, and select their preference of DVD or VHS. The page should then send an email request to the appropriate representative, based on the state that was entered on the form. I've created a table with the information linking each state to an email address, and I'm not sure how to proceed from here. My website is hosted on a server that supports PHP.

What code have you gotten so far?

My recomendation would be this:

Start writing a pseduocode for what you want to do, start with a structure chart for what the program will need to do, then write the algorithm, you may even want to put together a flowchart for the runthru of the program and then try coding it from there

I haven't actually started writing code yet, because I'm having difficulty in visualizing the overall process.

If I could figure out the major steps, I should have no problem writing the code.

What I have now is a request form which includes a place to enter the state (along with a complete mailing address), and a database table that defines an email address for each state. The final result that I'm looking for is an email that goes to the correct representative, which passes along the mailing address, and the custumer's preferred format of DVD or VHS.

K.. any program does 3 things, input > process > output

you want to design your program backwards

output > process > input

you already know your final result so go from there. Write down the steps for everything, draw a chart of the steps that need to be taken, then write your code from there.

HTH

Hi MrAnderson,

Or should I call you neo?

Here is the flow for your application:

1 - Create a DB called Regions
2 - Create table regions.states
3 - Create table regions.reps
4 - Create table regions.states_reps (relation)
5 - Create table regions.customers

The above is not written in stone. There are several ways to approach this.
It can be highly normalized or you can denormalize... depends on how you want
to work it.

Here is the way it should work:

Customer enters info -
- script matches input state to a state in regions.states_reps
- the above may produce multiples if you have more than one rep in a region
so you have to figure out how to handle this
a. Don't allow two reps to a region
b. Send e-mail to all reps in that region
c. Send e-mail to the first selected rep in the region
d. Let the script pick randomly if multiple matches come up
e. Put a counter in the table to count the times a rep got a lead -- the
next available rep that has the least amount of leads thus far would get
the next lead

- show the customer a thank you page and maybe even send them an e-mail
confirmation (this would be a good page to display your phone number as
well


Good Luck -- contact me if you need further help with this.

Thanks for your help. And yes, Neo is fine.