MySQL Table Updates in PHP

I manage a large database of opt-in subscribers from several websites, and I need a script which will overwrite and/or insert data from various fields in a given table. Some fields, like source_url, are not properly formatted (ie., the data in the field is [login to view URL] or [login to view URL], where it should be [login to view URL]). Other fields, such as privacy_url or subscription_category, are not inserted by default with the subscription record, so [login to view URL] needs to be inserted into an empty field.

Here is an example of the fields in a table:

id,email,first_name,last_name,address,city,state,zipcode,age,gender,telephone,source_url,privacy_url,date-time,ip_address,subscription_category,source_id,subscription_month-year

I need to overwrite the incorrect source_url, and then populate the missing fields with data based on the source_url.

A single table may have data from several different source_urls, so the script should work something like this:

Step 1. Find all records with source_url like '[login to view URL]'

Step 2. Overwrite all records matching source_url '[login to view URL]' with '[login to view URL]'

Step 3. Insert privacy_url '[login to view URL]' for all records matching source_url '[login to view URL]'

Step 4: Overwrite subscription_category 'Website Scripts Newsletter' for all records matching source_url '[login to view URL]'

Step 5: Overwrite id2 'C1L2' for all records matching source_url '[login to view URL]'

Step 6: Move on to process next source_url in list.

Final Step: Overwrite subscription_month-year '07-2007' for all records. (As the table is processed daily for new subscribers, this process can be run against all records in the table, regardless of source_url)

The main thing here is efficiency as there may be 200,000+ records to process on any given day, and if I process any of the master tables (I have been doing this for over 2 years now), there could be several million records in a table.

While my experience is limited, I have been doing this with individual php scripts from the command line over the last several months, using 1 script for each step in the process. It seems (at least with the scripts that I have) that the overwrite process is much more efficient than the insert process, so if it is faster to simply overwrite empty fields then that is how I would like the script to run.

What I envisioned is creating a text file with the variables, and having a php script read that file. The file could be imported into a mysql table if that is faster.. whatever works the best, I am not a programmer. Something like this is what I was thinking:

subscription_month-year '07-2007'

source_url|new source_url|privacy_url|subscription_category|id2

[login to view URL]|[login to view URL]|[login to view URL]|Website Scripts Newsletter|C1L2

[login to view URL]|[login to view URL]|[login to view URL]|PHP Scripts Newsletter|C2L1

[login to view URL]|[login to view URL]|[login to view URL]|CGI Scripts Newsletter|C2L2

[login to view URL]|[login to view URL]|[login to view URL]|Travel Newsletter|C3L1

etc..

While I have several clients in a single table, I have multiple client tables as well. If I could store all variables in 1 file, and have the script ignore any variables which do not exist, that would be best. The point of writing this script is so I can use a single script to process everything, instead of having to use multiple scripts.

Right now, I have a separate script for each step in the update, and a separate set of scripts for every single source_url (which means multiple scripts for any client who has more than 1 website). So if I process multiple sites for a client, and they send me a batch update, I have to first separate each source_url into a separate table, and then run the set of scripts on each individual table. Not very efficient, and very time consuming.

I have uploaded an example of each script I use in the process.

If it matters, the table scheme is MyISAM with a collation of utf8_general_ci. I would like the ability to run the script on tables using a different collation however.

I realize that this is a relatively simply script, but I also have more advanced development projects I would like to do related to a web based database management solution I own. It is also based on php for use with mysql tables, but I do not wish to publish it to an open forum as it is intellectual property. I am hoping to establish a relationship with a provider who has the skills to assist me with the development of this software as well, so please include some detailed information on capabilities with your bid.

Linux PHP

项目ID： #159364

关于项目