Hey guys,

I'm trying to figure out what the best way to handle my situation would be, and am looking for suggestions and feedback.

I'm putting together a program that will be running through a list (possibility of hundreds of thousands) of accounts in a MySQL database. It'll grab one of the fields (zip code, in this case) and then get specific data for that zip code from another MySQL table, and then send an email to the email address for that account with the data in it.

What I'm trying to figure out is the best way to handle going through that many records. The timing of the emails is critical, so it's not a process that can run for very long. My initial thought was to save the data for that zip code in an array, and the next time that zip code comes up for an account, just grab the data for it from the array. But, I'm not sure what the capabilities of PHP are with doing such. There are somewhere around 52,000 zip codes, and the data associated with each one is around 3.5 KB, but I'm not sure how this would equate to memory requirements.

So, does anyone have any suggestions as to what the best way would be to cache the data? I don't know if just storing it as an array variable throughout the processing is the best way, or if there's a more efficient and faster way available.

Thanks!

Matt

    15 days later

    There are a number of ways around this:
    First, you can store the zip code information within MySQL, and then write SQL queries to extract the information andplace within an e-mail. Placing indexes on the zip code will increase efficiency.

    If importing the information into MySQL is too much of a daunting task, have the zip code information place within its own file. Build a folder structure on your server by state (and region, if zip codes are broken up that way .... apologies for the ignorance - I'm not from the US). For example:
    /CA
    /CA/90
    /CA/90/1
    /CA/90/2
    /TX
    /TX/12
    /TX/12/1
    ...
    Then as a user requests zip code information, break the zip code and navigate to the correct folder to obtain the specific zip code information.

    Just a couple of ideas to ponder over .... !

      Thanks for the suggestions! I'll work with them and see what I come up with. It'll be interesting to see how they work. Thanks again 🙂

        Write a Reply...