Database Lookup Optimization and/or client-side validation alternative

[Eric Linders]

Hi everyone. :-)

Our site gets a ton of traffic on our contact forms, which collect the
standard information (name, address, city, state, zip, home phone,
etc.) The form validation is done with PHP.

To eliminate (or at least deter) people wasting time submitting phony
information, we can compare the state to the zip code (one DB table)
and the zip code to the area code (another table in same DB).

I can use two select statements to compare the state to zip and zip to
area code (respectively), or I can manually create a solution so that
the lookups are done on the client side using JavaScript. If speed
won't be an issue, I prefer to do it using PHP/MySQL because that way
I only have to maintain the zip, state and area code data in the DB.

Having said all of that, below are two questions:

1. Will these simple lookups in each table take that long? To
reiterate: these forms are critical and must work fast. (FYI, there
are about 58,000 records in each table.)

2. Should I index certain fields, or perform some other MySQL admin
task(s) to help increase the speed of the lookups either now (before
implementation) or as regular maintenance?

The table that I use to compare zip to state has the following fields
(of which only the first two are used for what I am describing here):
zip varchar(5)
state char(2)
city varchar(35)
county varchar(35)
primary_city_code int(1)
usps_code char(1)

The 2nd table that has only zip codes and area codes has the following
fields:
zip varchar(5)
area char(3)

I should mention that....

(1) I am an intermediate PHP programmer and beginning MySQL admin.

(2) My web host has PHPMyAdmin installed, which is what I use to
create and maintain the DB and its tables.

(3) Finally, I did pick up Mr. Dubois' MySQL book, although I need to
implement this solution in about 1 week, so I don't have time to
master MySQL without your help! :-)

Thanks for any/all tips to help me finish this project quickly! :-)
- Eric

[DCB]

"Eric Linders" <el********@hotmail.com> wrote in message
news:49*************************@posting.google.co m...

Hi everyone. :-)

Our site gets a ton of traffic on our contact forms, which collect the
standard information (name, address, city, state, zip, home phone,
etc.) The form validation is done with PHP.

To eliminate (or at least deter) people wasting time submitting phony
information, we can compare the state to the zip code (one DB table)
and the zip code to the area code (another table in same DB).

I can use two select statements to compare the state to zip and zip to
area code (respectively), or I can manually create a solution so that
the lookups are done on the client side using JavaScript. If speed
won't be an issue, I prefer to do it using PHP/MySQL because that way
I only have to maintain the zip, state and area code data in the DB.

Having said all of that, below are two questions:

1. Will these simple lookups in each table take that long? To
reiterate: these forms are critical and must work fast. (FYI, there
are about 58,000 records in each table.)

2. Should I index certain fields, or perform some other MySQL admin
task(s) to help increase the speed of the lookups either now (before
implementation) or as regular maintenance?

[snipped]

Hello Eric,

I'd have thought the best approach would be to use both Javascript (client
side) and PHP / MySQL (server side). Reasoning:-

Assuming 90% of browsers have Javascript enabled, then you might logically
catch 90% of phony information without a lookup to the database. Thereby
speeding up corrections, and without creating uneccessary overhead.

The server side validation should still take place of course, to keep your
data clean.

Regarding your indexes, yes - they should be installed WHERE they are to be
used in your queries.
http://www.mysql.com/doc/en/MySQL_indexes.html
You can use EXPLAIN SELECT to optimize
http://www.mysql.com/doc/en/EXPLAIN.html

I hope that helps you.
D

[DCB]

"Eric Linders" <el********@hotmail.com> wrote in message
news:49*************************@posting.google.co m...

Hi everyone. :-)

Our site gets a ton of traffic on our contact forms, which collect the
standard information (name, address, city, state, zip, home phone,
etc.) The form validation is done with PHP.

To eliminate (or at least deter) people wasting time submitting phony
information, we can compare the state to the zip code (one DB table)
and the zip code to the area code (another table in same DB).

I can use two select statements to compare the state to zip and zip to
area code (respectively), or I can manually create a solution so that
the lookups are done on the client side using JavaScript. If speed
won't be an issue, I prefer to do it using PHP/MySQL because that way
I only have to maintain the zip, state and area code data in the DB.

Having said all of that, below are two questions:

1. Will these simple lookups in each table take that long? To
reiterate: these forms are critical and must work fast. (FYI, there
are about 58,000 records in each table.)

2. Should I index certain fields, or perform some other MySQL admin
task(s) to help increase the speed of the lookups either now (before
implementation) or as regular maintenance?

[snipped]

Hello Eric,

I'd have thought the best approach would be to use both Javascript (client
side) and PHP / MySQL (server side). Reasoning:-

Assuming 90% of browsers have Javascript enabled, then you might logically
catch 90% of phony information without a lookup to the database. Thereby
speeding up corrections, and without creating uneccessary overhead.

The server side validation should still take place of course, to keep your
data clean.

Regarding your indexes, yes - they should be installed WHERE they are to be
used in your queries.
http://www.mysql.com/doc/en/MySQL_indexes.html
You can use EXPLAIN SELECT to optimize
http://www.mysql.com/doc/en/EXPLAIN.html

I hope that helps you.
D