Em Andamento

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 [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.

Habilidades: Linux, PHP

Ver mais: mysql batch update php, php mysql batch update, php mysql overwrite table, several table updates mysql, www my best php com, writing matters, writing in script, writing a batch file, where to find website programmer, where to find some programmer, where to find best web programmer, where to find a web programmer, where to find a software programmer, where i can find programmer, where i can find a programmer, where find programmer works, where find a programmer, where can i find programmer, what can i do with php and mysql, websites development how much, website development step by step, website development projects in php, web development skills list, web development projects in php, web development projects for final year

Acerca do Empregador:
( 7 comentários ) Tampa, United States

ID do Projeto: #159364