mardi 3 mars 2015

Manage duplicate records and other entries based on duplicate records in MySQL


I have following list of tables for my PHP Application.



wi_individual_g(ind_id,ind_name,ind_father_name,ind_spouse_name,is_employee,org_id,ind_dob,...)
wi_individual_p(ind_id,prg_id,...)
wi_program(prg_id,prg_name)
wi_group(grp_id,grp_name,...)
wi_organization(org_id,org_name,...)
wi_training(trn_id,trn_name,...)
wi_indv_org(ind_id,grp_id)
wi_indv_training(ind_id,trn_id)


The individuals can be involved to



Organization -> defined in wi_individual_g if is_employee='yes'
Group -> defined in wi_indv_org
Training -> defined in wi_indv_training
Program -> defined in wi_individual_p


I have create a function in MySQL to generate IDs of every Table. I have written PHP Scripts and AJAX for data entry for all of these tables. Among the PHP forms for data entry using AJAX, I'd forgotten to disable the Submit button to prevent multiple entry for wi_individual_g table.


My Problem:


The users tried submitting the same data multiple times. Each time the user submitted, the new ID was generated and thus same data was inserted multiple times creating the duplicate data on wi_individual_g.


Then while assigning the ind_id to other tables, the user used different IDs of duplicate data for assigning ind_id to Group, Training or Program. For an instance



wi_individual_g
ind_id ind_name ind_father_name ind_spouse_name is_employee org_id
20150203000X ABC CDE EFG yes 2015040001
20150203000Y ABC CDE EFG yes 2015040001
20150203000Z ABC CDE EFG yes 2015040001


These IDs had been assigned as



wi_indv_training
ind_id trn_id
20150203000X 2015010001

wi_indv_org
ind_id grp_id
20150203000Y 2015050001


Now I need to use SQL to



1) Delete all duplicates from wi_individual_g except one
2) Update all other tables to one ind_id that are referenced by duplicate data


How can that be possible using SQL. Any help would be highly praised. Thank you!





Aucun commentaire:

Enregistrer un commentaire