MySQL data from file

[hellouthere]

This is an interessting question, please hear me out...

 

i have a file full of data which i need to eventually find its way into my mysql database...

 

the file is in the format...

 

AYGA:GKA:GOROKA:GOROKA:PAPUA NEW GUINEA:06:04:54:S:145:23:30:E:5282

 

This is one record... each record is stored on a seperate line....

 

this is an explanation of the format "from the hoses mouth";

 

    * Field 01 - ICAO Code: 4 character ICAO code

    * Field 02 - IATA Code: 3 character IATA code

    * Field 03 - Airport Name: string of varying length

    * Field 04 - City,Town or Suburb: string of varying length

    * Field 05 - Country: string of varying length

    * Field 06 - Latitude Degrees: 2 ASCII characters representing one numeric value

    * Field 07 - Latitude Minutes: 2 ASCII characters representing one numeric value

    * Field 08 - Latitude Seconds: 2 ASCII characters representing one numeric value

    * Field 09 - Latitude Direction: 1 ASCII character either N or S representing compass direction

    * Field 10 - Longitude Degrees: 2 ASCII characters representing one numeric value

    * Field 11 - Longitude Minutes: 2 ASCII characters representing one numeric value

    * Field 12 - Longitude Seconds: 2 ASCII characters representing one numeric value

    * Field 13 - Longitude Direction: 1 ASCII character either E or W representing compass direction

    * Field 14 - Altitude: varying sequence of ASCII characters representing a numeric value corresponding to the airport's altitude from mean sea level (ie: "123" or "-123")

 

Basically i need the file split and sorted in such a way that i can put it into a mysql database table...

 

I have attempted this myself using file() and explode() but i just cant work it out...

 

any help/walkthrough/finished script would be greatly appriciated...

 

if i havent given enough info or you would like to know more please reply or PM me...

 

THANKS

[hitman6003]

Once you create your table, you need to insert the values in the correct order.  Aside from that, just use the file function to read the file, then use explode to get the separate fields...

 

$file = file("./path/to/file.txt");

foreach ($file as $line) {
  $line = explode(":", trim($line));

  //do any field preparation here...

  //form your query...
  $query = "INSERT INTO table_name (ICAO, IATA, Airport_Code, City, Country, etc, etc, etc) VALUES " .
               "('" . $line[0] . "', '" . $line[1] . "', '" . $line[2] . "', etc, etc, etc)";

  mysql_query($query) or die(mysql_error());
}

 

The only advantage you have of doing it that way is you are able to manipulate the fields before they are entered into the database.  An easier way would be to use MySQL's "LOAD DATA INFILE" function...

 

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

 

If you don't want to use the command line utility, use the graphical MySQL Administrator...

 

http://dev.mysql.com/doc/administrator/en/mysql-administrator-main-window-introduction.html