Ahhhh! Need some MySQL help!

Hey

I have an SQL kinda like this:

INSERT INTO `fam_quotes` VALUES (20110, 'Ecclesiastes', 'Give not over thy soul to sorrow and afflict not thyself in thy own counsel. Gladness of heart is the life of man and the joyfulness of man is length of days.');
INSERT INTO `fam_quotes` VALUES (20111, 'Ecclesiastes', 'Be not slow to visit the sick.');
INSERT INTO `fam_quotes` VALUES (20112, 'Greg Evans', 'Anger at lies lasts forever. Anger at truth can\'t last.');
INSERT INTO `fam_quotes` VALUES (20113, 'Havelock Ellis', 'The more rapidly a civilization progresses, the sooner it dies for another to rise in its place.');

Code (markup):

Now, what I've done is created a table called authors with the columns authorid and name, and now what I need to do is fill that table with the SQL like above, for each author I need to assign them a unique authorid (column is auto_increment so ...) and fill in their name.

Any ideas?

 

INSERT INTO `authors` (`name`) VALUES ('Author 1'), ('Author 2'), ('Author 3');

Code (markup):

 

Yeah, but I already have the SQL above and there is more to it, there are like, 2000 authors, is there any way to do it all at once in some godlike query?

 

So you want to move authors from "fam_quotes" (column #2?) to a new table?

 

No, I want to assign a unique ID for each author, e.g.
author_id, author_name
1, Einstein
2, Shakestein

Based on the SQL I currently have.

I came up with:

<?php
set_time_limit(0);

$query = mysql_query("SELECT DISTINCT(author) from `quotes`");
while($row = mysql_fetch_array($query)) {
	mysql_query("INSERT into authors (author_name) values ('{$row['author']}')");
	$id = mysql_insert_id();
	mysql_query("UPDATE `quotes` SET author_id = '" . $id . "' WHERE author = '" . $row['author'] . "'");
}
?>

PHP:

To try and acomplish it, and it worked to an extent, however about 9K out of 21K quotes didn't get assigned an author_id for some reason.

Anyone know why?

 

You can try something this:

INSERT INTO `authors` (`author_name`) SELECT DISTINCT(`author`) FROM `quotes`;
UPDATE `quotes` SET `author_id` = (SELECT `author_id` FROM `authors` WHERE `quotes`.`author` = `authors`.`author_name`);

Code (markup):

 

Nevermind, mysql errors were actually there but not being shown, I used mysql_real_escape_string on the inserts and crap and it worked fine.

Thanks for the help though!