Like this:

I needed to convert my mySQL database to mongodb collections. So I’ve written a PHP script to make this dirty work. It’s very simple to convert and if you need something like that, just copy the script below and run it. Pay attention if you have memory to get all entries in MySQL, if you don’t have just implement a new code to paginate the query to get these entries. Im my case I just used 500.000 entries and stopped here.

<?php
function getMyTables( $dbname ) {
 $tables = array();
 $sql = mysql_query("SHOW TABLES FROM $dbname ") or die("Error getting tables from $dbname");

 if( mysql_num_rows( $sql ) > 0 ) {
   while( $table = mysql_fetch_array( $sql ) ) {
     $explain = explainMyTable( $table[0] ); 
     $tables[$table[0]] = $explain;
    }
 }
 return $tables;
}

function explainMyTable( $tbname ) {
  $explain = array();
  $sql = mysql_query("EXPLAIN $tbname") or die("Error getting table structure");
  $i = 0;

  while( $get = mysql_fetch_array( $sql ) ) {
     array_push( $explain, $get[0] ); 
     $i++;
  }
  return $explain;
}

function checkEncode($string) {
  if( !mb_check_encoding($string,'UTF-8')) {
    return mb_convert_encoding($string,'UTF-8','ISO-8859-1');
  } else {
    return $string;
  }
}

$mydb   = "YOUR_MYSQL_DB_NAME";
$myconn = mysql_connect('MYSQL_HOST','MYSQL_USER','MYSQL_PASS');
$setmydb   = mysql_select_db( $mydb );
$mytables = getMyTables( $mydb );

$modb   = "YOUR_MONGO_DB_NAME";

try {
  $moconn = new Mongo();
  $modb = $moconn->selectDB( $modb );
} catch(MongoConnectionException $e) {
  die("Problem during mongodb initialization. Please start mongodb server.");
}

foreach( $mytables as $table => $struct ) {
  $sql = mysql_query("SELECT * FROM $table LIMIT 0 , 500000") or die( mysql_error() );
  $count = mysql_num_rows( $sql );

  // Starts new collection on mongodb
  $collection = $modb->$table;

  // If it has content insert all content
  if( $count > 0 ) {
    while( $info = mysql_fetch_array( $sql, MYSQL_NUM )) {
      $infosize = count( $info );
      $mosql = array();

      for( $i=0; $i < $infosize; $i++ ) {
        $mosql[$struct[$i]] = checkEncode($info[$i]);
  }

  $collection->insert($mosql);
  }
// Only create a new entry empty
} else {
  for( $i=0; $i < $infosize; $i++ ) {
    $mosql[$struct[$i]] = '';
  }

  $collection->insert($mosql);
  }
}

echo "Done! Please, check your mongodb collection and have fun!";
?>

Advertisement