read csv, find id, then insert mysql

[esiason14]

I'm trying to update an existing table with csv data. I want to dump it into the table playerstats which is linked to the players table by player_id. So, I need to first find the player_id, that matches the first name and last name of each record in the file before I can import. Make sense? Anyone have any suggestions on how to go about this? It would be much appreciated.

Heres a sample record that I want to import:

Lance Niekro,SF,9,1,2,0,0,0,2,2,1,0,0,0.222,0.222,0.364,0.586

Here is my playerstats table:

CREATE TABLE `playerstats` (
 `stat_id` int(10) unsigned NOT NULL auto_increment,
 `player_id` smallint(5) unsigned default '0',
 `year` smallint(5) unsigned default NULL,
 `mlbteam_id` char(3) binary default NULL,
 `ab` int(11) default '0',
 `runs` int(11) default '0',
 `hits` int(11) default '0',
 `doubles` int(11) default '0',
 `triples` int(11) default '0',
 `hr` int(11) default '0',
 `rbi` int(11) default '0',
 `walks` int(11) default '0',
 `so` int(11) default '0',
 `sb` int(11) default '0',
 `cs` int(11) default '0',
 `avg` decimal(4,3) default '0.000',
 `slg` decimal(4,3) default '0.000',
 `obp` decimal(4,3) default '0.000',
 `ops` decimal(4,3) default '0.000',
 `wins` int(3) default '0',
 `losses` int(3) default '0',
 `ip` decimal(4,2) default '0.00',
 `ha` int(4) default '0',
 `bb` int(4) default '0',
 `ko` int(4) default '0',
 `era` decimal(3,2) default '0.00',
 `k9` decimal(3,2) default '0.00',
 `sobb` decimal(3,2) default '0.00',
 `saves` int(4) default '0',
 `er` int(4) default '0',
 `gs` int(4) default '0',
 `cg` int(4) default '0',
 `whip` decimal(3,2) default '0.00',
 PRIMARY KEY (`stat_id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=2962;

PLAYERS Table

 `player_id` smallint(5) unsigned NOT NULL auto_increment,
 `lname` varchar(50) default NULL,
 `fname` varchar(50) default NULL,
 `picture_loc` varchar(25) default NULL,
 `mlbteam_id` char(3) binary default NULL,
 `league_abbv` char(2) binary default NULL,
 `position_id` char(3) binary default NULL,
 `height` varchar(6) NOT NULL default '',
 `weight` char(3) NOT NULL default '',
 `birth` varchar(10) default NULL,
 `college` varchar(50) NOT NULL default '',
 `jersey` smallint(2) default NULL,
 PRIMARY KEY (`player_id`)
) TYPE=MyISAM AUTO_INCREMENT=1350;

[FalseDawn]

Not quite sure what you are trying to do, but

So, I need to first find the player_id, that matches the first name and last name of each record in the file before I can import

Suggests that you are proposing scanning the csv in some way for records?
It would be better to import the csv data into a temporary table in its entirety, and then work your queries from that.

[adwatson]

I'd use perl to do it... Read the file one line at a time, split each line by the comma. Search in the player table for a matching record (on name), grab the player_id value you'll need. Then put together update or insert queries as needed to get the data into the database.