I want to import a tab delimited text file of daily golf scores into a
mysql table but firstly I need to match the player names in the tab
file to a table of player names I have in a mysql database and pull
out the unique id for that player and replace the name with the ID,
before inserting into the scores table.

I want to have a preview screen before it is inserted in the table so
that i can see if there are any errors with matching the names. So
from left to right on the preview screen would be the position - total
score - player name - playerid (pulled from db) - R1 - R2 - R3 - R4
(round numbers dependant on what day it is - could be one round, two
rounds etc)

I envisage the player names, if an exact match, would be just shown on
the preview page as text wiht no option to change it, but if there is
not an exact match (due to wrong spelling, not in our db or whatever)
then the player name would be a drop down menu with a list of the
closest matches and I would then choose from that. If there was no
match then we would need to enter that player name into our players
table.

I would think perhaps that the player name has to be "exploded" and
matched upon each part of the name eg LIKE %% because some chinese
player names are reversed sometimes plus they may have a dash and
sometimes the dash is not in the name eg Jong-Duck Kim, Kim Jong-Duck,
Jong Duck Kim

So I need a php page with a browse button to choose the tab file, I
then need the php page with the preview (after querying our player
table) of the names with the ID's etc and scores ready to be dumped in
the db. Once everything is matched and okay, the submit button is
pressed and the "scores" table is filled with the relevant data.


Example Scores - Tab Delimited
(Position - Total Par - Player Name - Round 1 - Round 2 - Round 3 - Round 4)

If the scores were from round one then there is only one score at the
end, two scores for round two etc etc so it needs to handle any amount
of rounds from 1 to 10.

Round Two Example
1	-14	Kiyoshi Miyazato	68	70
T2	-13	Charlie Wi	65	69
T2	-13	Hideki Kase	68	68
T2	-13	Jeev Milkha Singh	67	62
T2	-13	Mamo Osanai	68	70
T2	-13	Masahiro Kuramoto	68	69
T2	-13	Scott Barr	67	68

Round Four Example
1	-14	Kiyoshi Miyazato	68	70	68	64
T2	-13	Charlie Wi	65	69	69	68
T2	-13	Hideki Kase	68	68	66	69
T2	-13	Jeev Milkha Singh	67	62	69	73
T2	-13	Mamo Osanai	68	70	66	67
T2	-13	Masahiro Kuramoto	68	69	67	67
T2	-13	Scott Barr	67	68	68	68
T8	-12	Jong-Duck Kim	67	72	65	68
T8	-12	Kaname Yokoo	71	66	69	66
T8	-12	Katsuyoshi Tomori	71	68	66	67
T8	-12	Scott Laycock	69	67	68	68



#
# Table structure for table `tour_players`
#

CREATE TABLE `tour_players` (
  `tp_id` int(11) NOT NULL auto_increment,
  `tp_fname` varchar(70) NOT NULL default '',
  `tp_lname` varchar(70) NOT NULL default '',
  `tp_state` varchar(5) NOT NULL default '',
  `tp_country` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`tp_id`),
  KEY `p_id` (`tp_id`)
) TYPE=MyISAM AUTO_INCREMENT=2779 ;

#
# Dumping data for table `tour_players`
#

INSERT INTO `tour_players` VALUES (1, 'Tommy', 'Aaron', '', 'USA');
INSERT INTO `tour_players` VALUES (2, 'Juan', 'Abbate', '', 'ARG');
INSERT INTO `tour_players` VALUES (3, 'Susumu', 'Abe', '', 'JPN');
INSERT INTO `tour_players` VALUES (4, 'Natalie', 'Aber', '', 'USA');
INSERT INTO `tour_players` VALUES (5, 'Anna', 'Acker-Macosko', '', 'USA');
INSERT INTO `tour_players` VALUES (6, 'Joe', 'Acosta Jr', '', '');
INSERT INTO `tour_players` VALUES (7, 'Kimberly', 'Adams', '', 'USA');
INSERT INTO `tour_players` VALUES (8, 'Lynn', 'Adams', '', '');
INSERT INTO `tour_players` VALUES (9, 'Natalie', 'Adams', '', 'ENG');
INSERT INTO `tour_players` VALUES (10, 'Jody', 'Adams-Hawkins', '', 'AUS');

Dear Sir,

Yes you are quite right, the names submitted in the TSV file would
have to be exploded in the following steps:

1. Explode at the space
2. Explode at the hypen
3. match for a %sub-name% and loop through each sub-name
4. check if the result of each of the above queries has the same name
being returned, else search for the largest number of returns with the
same name.
5. If no same names were returned accross the sub-names, then flag as
failed and show user the names drop down.

However the only catch with such a logic is that, if you have a
sizeable TSV file to upload the programs efficiency would deterioate
quite substantially.

On the other hand if it were possible to ensure that the program from
which you were generating the TSV were to ensure uniform naming
pattern the chances of the PHP program not being able to accurately
match would be far lesser.

Do consider the same.

Take care,
Paul Alapatt
BernardLabs -

Yes the file will be uniform everytime so no problem there at all and
the TSV file will only ever be 150 lines long so no real chance of
anything being overloaded at all.

It's a pretty simple one I think....