CSC220 fillKmlData.php

--D. Thiebaut 15:31, 28 October 2010 (UTC)

---

<?php
// fillKmlData.php
// D. Thiebaut
// Reads the contents of several kml files in the directory
// /Users/classes/220a/abvio/*.kml and stores them in 
// a mysql table called kmldata, with the following structure
//
//CREATE TABLE IF NOT EXISTS `kmldata` (
//  `Id` int(1) NOT NULL auto_increment,
//  `userId` int(1) default NULL,
//  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
//  `data` mediumtext,
//  PRIMARY KEY  (`Id`),
//  KEY `userId` (`userId`,`date`)
//) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
//
// Note: the userId and date fields of the table are not initialized.
//         by default the date is set to the current date and time if not 
//         set to a specific value.
//
include 'accessinfo.php';

function insertUser( $newUser, $link ) {
  $query = sprintf( "INSERT INTO `users` (`userName`) VALUE ('%s')", $newUser );
  //print $query . "\n";
  $result = mysql_query( $query, $link );
  if ( ! $result ) 
    printf( "Insertion of user %s unsuccessful\n", $newUser );
}

//--- display the contents of a table, given its name ---
//--- the names of the fields are not displayed        ---
function displayTable( $table, $link ) {
  $query = sprintf( "SELECT * from `%s`", $table );

  printf( "\n-- table %s --\n", $table );
  $result = mysql_query( $query, $link );
  if ( ! $result ) 
    die( "Invalid query (" . $query . "): " . mysql_error() );
  
  while ( $row = mysql_fetch_array( $result, MYSQL_NUM ) ) {
    for ( $i=0; $i<count( $row ); $i++) 
      printf( "%s\t", $row[$i] );
    print "\n";
  }
  printf( "\n\n" );  
}

//--- connect to mysql server ---
$link = mysql_connect( $hostName, $userName, $password );
if ( ! $link ) 
  die( "Could not connect to server: " . $mysql_error() );

//--- select database ---
$database = "220a_hw6";
$db = mysql_select_db( $database, $link );
if ( ! $db ) 
  die( "Could not connect to database " . $database . ": "
       . mysql_error() );

//-------------------------------------------
//--- array of all the kml file names  ---
$kmlFiles = array(
		  "Cyclemeter-Cycle-20101011-1527.kml",
		  "Cyclemeter-Cycle-20101011-1544.kml",
		  "Cyclemeter-Cycle-20101011-1550.kml",
		  "Cyclemeter-Cycle-20101011-1556.kml",
		  "Cyclemeter-Cycle-20101011-1610.kml",
		  "Walkmeter-Walk-20101011-1527.kml",
		  "Walkmeter-Walk-20101011-1535.kml",
		  "Walkmeter-Walk-20101011-1551.kml",
		  "Walkmeter-Walk-20101011-1601.kml",
		  "Walkmeter-Walk-20101011-1610.kml",
		  "Walkmeter-Walk-20101011-1638.kml",
		  "Walkmeter-Walk-20101011-1646.kml" );

foreach ( $kmlFiles as $file ) {
  printf( "\nUploading file %s\n------------------------------\n", $file );
  $kml = file_get_contents( "/Users/classes/220a/abvio/" . $file );
  $query = "INSERT INTO `kmldata` ( `data` ) VALUE ( '%s' )";
  $query = sprintf( $query, addslashes( $kml ) );
  $result = mysql_query( $query, $link );
  if ( ! $result ) 
    printf( ( "Inserting  %s unsuccessful" % $kml ) . mysql_error() . "\n\n" );

}

//--- display contents of table (just to check successful input ) ---
displayTable( "kmldata", $link );

//--- close database ---
mysql_close( $link );

?>