How to combine if i found the two rows are identical in a table?

PHP
1

Hi all,
I have one table like company…
In this table compname is one column…
User1 enters a company name differently… Ex:TCS
another user2 enters a company name differently… Ex:Tata Consultancy Services…
Related products and articles and news are inserted in the respective table with respective company…
As an admin::
I found, the two companies are equal but the diff is only compname entered by the user1,user2.
I want to merge/update the related products,articles and some news related to the company and gives the common name or preferred name to it and update all related tables and gives rights to the authorized user among user1/user2.

How can i do it…
If possible give me GUI interface also to do this…
Please give me suggestions…

Thanking you…

2

Thanks to Michael,
What i have in mind you represent the same…
but i want the help to implement it…
is there any programatic help regarding this topic…
Give me some ideas on this…

3

@r937 > I think he’s asking about how to design a UI for performing the record merge - not the actual database queries involved. With that in mind…

Create a button called “Merge” and display it to the users with permission to use it on each record. When clicked prompt the user on the next screen to select the record(s) to ‘merge into’ it. When they click ok on that screen present a third screen that shows all of the records and asks for confirmation.

At this point you have a Primary record and a group of targets. The information of the primary over rides the targets if there is a conflict. The basic process at this point is to change the foreign keys of the child records of the targets to the id of the primary record that will be receiving the merges.

The database structure will obviously influence this greatly - and this will be much easier if the database is more or less hierarchical in structure - that is you have parent records with children. In this case you just need to reassign some foreign keys and call it a day.

I have a patient record system with a merge feature for patient records so for an example I’ll walk over how I do it in that system. Patient records have addresses, phone numbers and “episodes of care” for their main child records. The episode of care has a claim table for a child, which in turn has bills, which in turn has procedures and in turn has ledgers items. Ledger items don’t have a patient key - to find the patient of a ledger record you have to walk up the tree. That said, it makes merging easier because the number of tables referencing the patient record is minimal.

When merging patients I throw a warning if the SSN numbers do not match (Note, I would have primary keyed on SSN, but HIPPA law requires that the SSN be encrypted and working with encrypted keys is not ideal, further we don’t always have the SSN - we usually do but not always). If the merge goes through the addresses are compared with exact matches discarded. If there’s a slight misspelling the user will have to delete the redundant address entry manually. We already have to store multiple addresses since historical addresses must be submitted for historical claims even if the present address is different, so the patient having multiple addresses isn’t much of a factor (ditto phone numbers). The primary address and phone is still set by the Primary record of the merge.

I hope this helps.

4

how can i do without directly interact with database or without login to database panel…
I want programatically and backend the query will execute and shows result as modified the user for the particular company and company name also…

Now it is small table, in future it will increase the size of the table data…
For that reason i want programatically…

5

yes, i understand

you will have to do this slowly and carefully, one related table at a time

6

Thank you for your reply…
I have one table like company…
In this table compname is one column…
User1 enters a company name differently… Ex:TCS
another user2 enters a company name differently… Ex:Tata Consultancy Services…
As an admin::
I found, the two companies are equal but the diff is only compname entered by the user1,user2.

I want to merge/update company and gives the common name or preferred name to it and gives rights to the authorized user among user1/user2.

7

slowly, carefully, and one related table at a time