Hi Mark, On Fri, Mar 23, 2012 at 7:29 PM, Mark Phillips <m...@phillipsmarketing.biz>wrote:
> I have a servlet/jsp/MySQL web site in production, and there are about > 2,000 records in the flights table. One of the foreign keys is teacher_id. > Up to this point, there is a one to many relationship between teacher_id > and the data in the flights table. I need to change the data model to allow > for a many to many relationship between teacher_id and the data in the > flight table. What is the best way to do this? > > Thanks, > > Mark > This is the best technical treatise on many/many relationships for PHP/MySQL: http://www.tonymarston.net/php-mysql/many-to-many.html The conversion procedure itself relatively simple: 1) Backup everything in case something goes wrong. Code: mkdir ~/tmp cd ~/tmp mysqldump -u root -p database > database.bck.sql; (cd */public/www/whatever/..*; tar pczv *database*) > *database.*tar.gz 2) Use a SQL tool, such as mysql to access the database directly. 3) Run the "show tables" command to find out the name of your join table. Code: mysql> show tables; accounts accounts_something accounts_foo_bar 4) Run "desc *accounts_foo_bar*" to make sure that it's the join table used for the relationship. Join tables look like this. Code: mysql> desc *accounts_foo_bar* +---------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+-------------+------+-----+---------+-------+ | id | varchar(36) | NO | PRI | NULL | | | date_modified | datetime | YES | | NULL | | | deleted | tinyint(1) | YES | | 0 | | | accounts_bccounts_ida | varchar(36) | YES | MUL | NULL | | | accounts_ccounts_idb | varchar(36) | YES | | NULL | | +---------------------------+-------------+------+-----+---------+-------+ 5) Save the data from the table using sqldump; Code: mysqldump -u *root* -p --database *database* --table *accounts_foo_bar* > ~/tmp/*accounts_foo_bar.sql* Look at the ~/tmp/*accounts_foo_bar.sql* file and verify that it has a insert statement to restore your data. 6) Go into navicat or MySQL Studio, select the Accounts module, select the Relationships tab and delete the one-to-many relationship between Accounts and *Satellite Foo*. Click the little checkbox that says "delete the table" -- don't worry, you have all the data backed up. *The table name and schema will be recreated in the next step (7), but the new table will have different indexes -- we're going to let the Navicat or Studio do all the heavy lifting for us*. This can take a while, be sure to let it finish. 7) White still in the MySQL Studio, on the Accounts module Relationships tab, add a new Many-to-Many relationship between Accounts and *Satellite Foo*. 8) View the main Accounts tab, and verify that there is a "*Satellite Foo*" subpanel. View the *Satelite Foo* tab and verify that there is an Accounts subpanel. 9) Edit the ~/tmp/*accounts_foo_bar*.sql file, deleting everything except the "insert" statement. 10) In mysql, source the ~/tmp/*accounts_foo_bar*.sql file to restore your relationship data. 11)Now go back to the Accounts model, you will notice that the *Satellite Foo* subpanel now displays all the related *Satellite Foo*. You might have to add some grants or open some ports to allow 3306 access for Navicat (which runs in wine great) or MySQL Studio. Email me off list for specific questions. -- (503) 754-4452 Android (623) 239-3392 Skype (623) 688-3392 Google Voice ** it-clowns.com
--------------------------------------------------- PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
