Import data from Mysql into MongoDB with PHP

OVERVIEW

This is meant to be a simple introduction on how to do basic database operations using MongoDB via PHP. 99% of PHP developers use Mysql so I have provided a sample which enables them to see both the types of database operations in code.

MongoDB, for those who are not very familiar, is a high performance NoSQL database which is optimized for document storage. Contrary to popular misconception, NoSQL databases are not exactly a replacement for RDBMS. MongoDB is best where inserts and updates are very frequent or where the data is meant for fast retrieval, but it does not support JOINS or foreign keys to maintain data integrity, so its not good for complex database queries like reporting or analysis.

To find more about MongoDb, check out this link

 

BASIC TERMS AND CONCEPTS

• A Database in Mysql is a Database or a set of Collections in MongoDB
• A table in Mysql is a Collection in MongoDB
• A row in Mysql is a Document in MongoDB

As you will see in the sample code, all data is in the form of associative arrays. So you can use arrays for all CRUD operations.

 

INSTALLATION PREREQUISITES

It is assumed you have MongoDB installed in your system. If not then the links below provide information on installing it.

How to install in Windows

How to install in all the major Linux distros

How to install in FreeBSD Unix

 

Apart from MongoDB , you will need to make sure the MongoDB extension for PHP is enabled in php.ini. Instructions are given here.

When you run phpinfo() the Mongo extension should show as in the screenshot below:

 

 

 

 

 

 

 

 

 

 

 

 

 

THE CODE

The sample code below reads data from a table called countries in a mysql database called test and then adds the data into a collection called countries in a Mongodb database called testdb.

<?php 
error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING ^ E_DEPRECATED);

///
// Import a countries table from mysql into mongodb
// (C) Amit Sengupta, Sep.2015
///

    //mysql connection details
    $g_connServer = "localhost";
    $g_connUserId = "root";
    $g_connPwd = "master";
    $g_connDBName = "test";


    // setup mysql PDO
    $dsn = "mysql:host=" . $g_connServer .";dbname=" . $g_connDBName . ";";
    $options = array(
		PDO::ATTR_PERSISTENT=>true,
		PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
		PDO::MYSQL_ATTR_INIT_COMMAND=>"SET NAMES utf8"
    );
    $db = null;

    // setup mongo db
    $mongo = new Mongo("localhost");
    $mongodb = $mongo->testdb;
    $ccountries = $mongodb->countries;

    // open mysql table for reading
    try {
	$db = new PDO($dsn, $g_connUserId, $g_connPwd, $options);
    

	$sql = "select * from countries";
	$stmt = $db->prepare($sql);
	$stmt->execute();
	$rst = $stmt->fetchAll(PDO::FETCH_ASSOC);
	if (is_array($rst)) {
	    foreach($rst as $row) {
		// get column data from mysql
		$countryCode = $row["countryCode"];
		$countryName = $row["countryName"];
		$currencyCode = $row["currencyCode"];
		$population = $row["population"];
		$areaInSqKm = $row["areaInSqKm"];

		print $countryCode . "," . $countryName . "," . $currencyCode . "," . $population . 
		    "," . $areaInSqKm . "<br>";

		// only insert if country is not already present
		$cursor = $ccountries->findOne(array("countryName"=>$countryName));
		if ($cursor == null) {
		    // insert into mongodb collection
		    $collection = array("countrycode"=>$countryCode,
			    "countryName"=>$countryName,
			    "currencyCode"=>$currencyCode,
			    "population"=>$population,
			    "areaInSqKm"=>$areaInSqKm);
		    $ccountries->insert($collection);
		    echo("Added with id=" . $collection["_id"] . "<br>");
		}		    

	    }
	}	    

    } catch (PDOException $pdex) {
	exit($pdex->getMessage());
    }


    echo("<hr><table border=1 cellspacing=2 cellpadding=2>");
    echo("<tr><td>Code</td><td>Name</td><td>Currency</td><td>Population</td><td>Area (sq.km)</td></tr>");
    
    // browse through all the rows in mongodb
    foreach($ccountries->find() as $collection) {
	$id = $collection["_id"];
	$countryCode = $collection["countrycode"];
	$countryName = $collection["countryName"];
	$currencyCode = $collection["currencyCode"];
	$population = $collection["population"];
	$areaInSqKm = $collection["areaInSqKm"];

	echo("<tr><td>" . $countryCode . "</td><td>" . $countryName . "</td><td>" . 
	     $currencyCode . "</td><td>" . $population . "</td><td>" . $areaInSqKm . "</td></tr>");
		
    }
    echo("</table>");
    $mongo->close();
    
    $db = null;
    

?>

The output of the code is shown below: