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 www.domain.com or www.domain1.com/subpage.html, where it should be http://www.domain.com). Other fields, such as privacy_url or subscription_category, are not inserted by default with the subscription record, so http://www.domain.com/privacy.html needs to be inserted into an empty field.
Here is an example of the fields in a table:
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 'domain1.com'
Step 2. Overwrite all records matching source_url 'domain1.com' with 'http://www.domain1.com'
Step 3. Insert privacy_url 'http://www.domain1.com/privacy.html' for all records matching source_url 'domain1.com'
Step 4: Overwrite subscription_category 'Website Scripts Newsletter' for all records matching source_url 'domain1.com'
Step 5: Overwrite id2 'C1L2' for all records matching source_url 'domain1.com'
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:
domain1.com|http://www.domain1.com|http://www.domain1.com/privacy.html|Website Scripts Newsletter|C1L2
domain2.com|http://www.domain2.com|http://www.domain2.com/privacy.html|PHP Scripts Newsletter|C2L1
domain3.com|http://www.domain3.com|http://www.domain3.com/privacy.html|CGI Scripts Newsletter|C2L2
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.