• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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!
0
lamerhooDJV
Asked:
lamerhooDJV
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now