Closed

MySQL Table Updates in PHP

This project was awarded to oth for $30 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Skills Required
Project Budget
$30 - $100 USD
Total Bids
5
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 [url removed, login to view] or [url removed, login to view], where it should be [url removed, login to view]). Other fields, such as privacy_url or subscription_category, are not inserted by default with the subscription record, so [url removed, login to view] 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 '[url removed, login to view]'

Step 2. Overwrite all records matching source_url '[url removed, login to view]' with '[url removed, login to view]'

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

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

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

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
[url removed, login to view]|[url removed, login to view]|[url removed, login to view]|Website Scripts Newsletter|C1L2
[url removed, login to view]|[url removed, login to view]|[url removed, login to view]|PHP Scripts Newsletter|C2L1
[url removed, login to view]|[url removed, login to view]|[url removed, login to view]|CGI Scripts Newsletter|C2L2
[url removed, login to view]|[url removed, login to view]|[url removed, login to view]|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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online