OT: SQL Question

[Lisa Kachold]

Hi Mark,

On Fri, Mar 23, 2012 at 7:29 PM, Mark Phillips
<mark at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20120323/f507dcb9/attachment.html>