Import issue with large file

I read about LOAD FILE in the mysql documentation and since my php solution totally broke I'm in desperate need of some clarification on the usage of it.  Basically my situation is more of a "special import" where is everything is linked by internal numbers in hiercal order (country, state, city) in that order.  So I've successfully loaded both country and state, but am having a major issue with City (because of the contents size, 250mb+)

Here's the export of the table structure and I'll go through them all to explain.

--
-- Table structure for table `dt_cities`
--

CREATE TABLE `dt_cities` (
  `id` int(11) NOT NULL auto_increment,
  `country_id` int(11) default '0',
  `state_id` int(11) default '0',
  `name` varchar(255) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=100988 ;

-- --------------------------------------------------------

--
-- Table structure for table `dt_countries`
--

CREATE TABLE `dt_countries` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `states` tinyint(1) default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=247 ;

-- --------------------------------------------------------

--
-- Table structure for table `dt_states`
--

CREATE TABLE `dt_states` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3653 ;

-- --------------------------------------------------------

--
-- Table structure for table `fp_countries`
--

CREATE TABLE `fp_countries` (
  `id` int(11) NOT NULL auto_increment,
  `countrycode` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Country Codes...' AUTO_INCREMENT=247 ;

-- --------------------------------------------------------

--
-- Table structure for table `fp_states`
--

CREATE TABLE `fp_states` (
  `id` int(11) NOT NULL auto_increment,
  `statecode` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3653 ;

The fp is a temp table that basically contains refrence data (EG: each share by codes like countrycode for countries and statecode for states) so that they can link up properly.

Ignore both country and state tables for now they are just containing proper refrence data and also the names and states (easy)  What I'm more interested in is getting both of their reference data to work with the cities table.

So I basically need within my mysql import query a way to query these two other tables with information that the cities text file contains (EG: countrycode -> fp_countries, statecode -> fp_states) or I'm going to need some viable solution that can fill this cities table properly.  

FILES: country.txt, states.txt, cities.txt

If you need more info, let me know.  Thank you in advance!
LVL 1
lamerhooDJVAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WilliamFrantzCommented:
So you've already loaded the country and state tables and now you have generated an ID number for each country and each state.  Next you plan to load a bunch of cities and rather than use a statecode and contrycode in the cities table, you want to use the generated state ID and country ID.  As each city is added to the table, you need to look up the corresponding state and country IDs to fill in the state_id and country_id fields.

Right?

You could write a PHP script to do such a job.  If the state and contry files are reasonably sized, I'd read them into a giant associative array using the codes as keys and the IDs as values.  Then, as you add each city, use the arrays to translate the codes into IDs.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.