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?
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.