Connect records in table 1 to records in table 2, one to many relationship, MySQL database
Budget: $10 – $30 USD
MySQL database:
It has a table accounts, members, and members_accounts
Accounts has 5000 records, the other two are empty
Accounts has multiple records who belong to the same one person
What I need now is a script that gives each member a unique record in MEMBERS and connects this record to the one or multiple records in ACCOUNTS
This should work like this:
Step 1: The script first looks up the first record (lowest account_id) in ACCOUNTS, and copies username, password and date to MEMBERS and links these 2 records to each other by adding a record in MEMBERS_ACCOUNTS with the account_id and the member_id.
Step 2: the script searches for other records in ACCOUNTS with the same username, and connects each found record to MEMBERS by adding a new record to MEMBERS_ACCOUNTS with the account_id and the member_id. This should be by order of account_id, starting with lowest.
Then it repeats these two steps on the next record in ACCOUNTS.
The script must be made so that it avoids making duplicates.
This is a one to many relationship: a member can have multiple accounts. An account can only have one member.
I need you to write this script locally, and then send it to me with iinstructions on how to run it through my webbrowser or ssh. I can install it somewhere in public_html.
This may become a trial and error situation, where things don’t work right at first, you’ll stay in touch with me for trouble shooting and adjusting the script if necessary.
I cannot give you access to my website or database because of member privacy. I am not allowed to share these details outside our company, but I can give you a stripped copy of the database with these 3 tables so that you can dry test your scripts locally.
The relevant columns in ACCOUNTS are:
account_id username password date_started
In MEMBERS :
Date member_id username password
Both tables have more columns which are not relevant for this transfer.
It has a table accounts, members, and members_accounts
Accounts has 5000 records, the other two are empty
Accounts has multiple records who belong to the same one person
What I need now is a script that gives each member a unique record in MEMBERS and connects this record to the one or multiple records in ACCOUNTS
This should work like this:
Step 1: The script first looks up the first record (lowest account_id) in ACCOUNTS, and copies username, password and date to MEMBERS and links these 2 records to each other by adding a record in MEMBERS_ACCOUNTS with the account_id and the member_id.
Step 2: the script searches for other records in ACCOUNTS with the same username, and connects each found record to MEMBERS by adding a new record to MEMBERS_ACCOUNTS with the account_id and the member_id. This should be by order of account_id, starting with lowest.
Then it repeats these two steps on the next record in ACCOUNTS.
The script must be made so that it avoids making duplicates.
This is a one to many relationship: a member can have multiple accounts. An account can only have one member.
I need you to write this script locally, and then send it to me with iinstructions on how to run it through my webbrowser or ssh. I can install it somewhere in public_html.
This may become a trial and error situation, where things don’t work right at first, you’ll stay in touch with me for trouble shooting and adjusting the script if necessary.
I cannot give you access to my website or database because of member privacy. I am not allowed to share these details outside our company, but I can give you a stripped copy of the database with these 3 tables so that you can dry test your scripts locally.
The relevant columns in ACCOUNTS are:
account_id username password date_started
In MEMBERS :
Date member_id username password
Both tables have more columns which are not relevant for this transfer.