MySQL Table Updates in PHP

IN PROGRESS
Bids
5
Avg Bid (USD)
N/A
Project Budget (USD)
$30 - $100

Project Description:
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:
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 '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:

subscription_month-year '07-2007'

source_url|new source_url|privacy_url|subscription_category|id2
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
domain4.com|http://www.domain4.com|http://www.domain4.com/privacy.html|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.

Skills required:
Linux, PHP
Additional Files: script_set_example.tar.gz
Hire candsadvertising
Project posted by:
candsadvertising United States
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.