[SOLVED] Automatic normalization

[Jaguar]

I'm importing an Access file into MySQL and there's a lot of redundant data. I have only 100 authors over 80,000 rows. I know I should make a seperate authors table and replace the authors with numbers. Is there any quick way to do this?

 

How I'm doing it is first in MySQL.

SELECT author FROM books GROUP BY author;

 

Then copying the results into Notepad.

author1

author2

...

author100

 

Then turning every line into SQL.

 

UPDATE books SET author = 1 WHERE author = 'name1';

UPDATE books SET author = 2 WHERE author = 'name2';

...

UPDATE books SET author = 100 WHERE author = 'name100';

 

I can write a macro to do it fairly quick but there must be a better way?

[Barand]

<?php
$sql = "CREATE TABLE author (
    authorID int not null auto_increment primary key,
    name varchar(50)
    )";
mysql_query($sql);

$sql = "INSERT INTO author (name)
        SELECT DISTINCT author FROM books";
mysql_query($sql);

/**
* add a new column "authorID" to books table with phpmyadmin or similar, then
*/

$sql = "UPDATE books b INNER JOIN author a ON b.author = a.name SET b.authorID = a.authorID";
mysql_query($sql);

/**
* now drop column "author" from books table
*/

?>

[Jaguar]

<3 I wish I knew this a year ago. Thank you! Worked perfectly.