BJcomm

Yet another MongoDB and PHP tutorial, say it ain’t so! Well yes, but this tutorial aims to be more complete than the others out there. OK I’m going to assume you know what MongoDB is, so I’m not going to go over “what mongoDB is” or “what NoSQL is”. I’m going to do this series in a little different styling than my other tutorial series have been, so let just jump right in.

Here’s a couple links to get you up to speed:
Installing Mongo
MongoDB PHP Extension

If you have issues with MongoD not starting – something about “/data/db” missing, then you should create the directory. In *Nix: “mkdir /data/db”, in Win “mkdir c:data” then “mkdir c:datadb”.

Connecting
MySQL

[php]
<?php
$host = ‘localhost’;
$user = ‘root’;
$pass = “”;
$link = mysql_connect($host, $user, $pass, 1) or die(“Could not connect to: {$host}. Check your settings and try again.”);
[/php]

MongoDB

[php]
<?php
try
{
$link = new Mongo();
}
catch(MongoConnectionException $e)
{
die(‘Could not connect. Check to make sure MongoDB is running.’);
}
[/php]

As you can see, Mongo DB automatically will connect to the local host and default port. Yes, MySQL will do the same if you have the directives in your “php.ini” setup to do this. IE. “mysql.default_host”, “mysql.default_user” and “mysql.default_password”. You might notice that by default MongoDB doesn’t have a user name or password setup. I will go over how to set that up in part 2.

Creating and using a DB
MySQL

[php]$db = ‘testdb’;
$sql = “CREATE DATABASE `$db`”;

mysql_query($sql, $link);
mysql_select_db($db, $link);
[/php]

MongoDB

[php]$db = $link->testdb;
[/php]

In one line of code, MongoDB will create a DB automatically if it doesn’t already exists and select (use) it.

Create a Table / Collection
MySQL

[php]
// Create the Table
$tbl = ‘user’;
$sql = “CREATE TABLE `$tbl` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `login` VARCHAR (24)NOT NULL, `password` CHAR(32) NOT NULL, `email` VARCHAR(255)) TYPE=innodb;”;
mysql_query($sql, $link);
[/php]

MongoDB

[php]
// Create the collection (AKA Table)
$col = $db->user;
[/php]

Once again, MongoDB will automatically create something for us if it doesn’t exist. In this example, it create our “user” table. You might notice we didn’t define how the table is formatted, this is because MongoDB is schema-less and doesn’t need column definitions. “Tables” in MongoDB are called “collections”.

Insert Data
MySQL

[php]
// Insert a row into the table
$sql = “INSERT INTO `$tbl` SET `login` = ‘jsmith’, `password` = ’5f4dcc3b5aa765d61d8327deb882cf99′, `email` = ‘jsmith@example.com’”;
mysql_query($sql);

$sql = “INSERT INTO `$tbl` SET `login` = ‘psmith’, `password` = ’5f4dcc3b5aa765d61d8327deb882cf99′, `email` = ‘psmith@example.com’”;
mysql_query($sql);

// Get the ID of last insert
$id = mysql_insert_id($link);
[/php]

MongoDB

[php]
// Insert a document (row) into the collection (table)
$doc = array(‘login’ => ‘jsmith’, ‘password’ => ‘ 5f4dcc3b5aa765d61d8327deb882cf99′, ‘email’ => ‘jsmith@example.com’);
$col->insert($doc, true);

$doc = array(‘login’ => ‘psmith’, ‘password’ => ‘ 5f4dcc3b5aa765d61d8327deb882cf99′, ‘email’ => ‘psmith@example.com’);
$col->insert($doc, true);

// Get the id of last insert
$id = $doc['_id'];
[/php]

You might have noticed that we used an array to define our “row” of data, which are called “documents”. On the insert method, the second argument will set the query to do a “safe insert”. This will allow us to find out if the query executed properly. If not set, it will not get that information. So I would set it to true if you want to be able to debug your queries. If you are wondering about the password, it’s the MD5 hash for “password”.

Querying Data
MySQL

[php]
// Get all rows
$sql = “SELECT * FROM `$tbl`”;
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo ‘All rows:<br/>’;

if($cnt > 0)
{
do
{
$row = mysql_fetch_assoc($qry);

echo ‘<pre>’;
print_r($row);
echo ‘</pre>’
}
}

// Query for the row matching the last insert ID
$sql = “SELECT * FROM `$tbl` WHERE `id` = {$id}”;
$qry = mysql_query($sql, $link);
$row = mysql_fetch_assoc($qry);

echo ‘Single row (id = $id):<br/><pre>’;
print_r($row);
echo ‘</pre>
[/php]

MongoDB

[php]
// Get all documents
$res = $col->find();

echo ‘All documents:<br/>’;

foreach($res as $doc)
{
echo ‘<pre>’;
print_r($doc);
echo ‘</pre>’;
}

// Query for the document matching the last insert ID
$doc = $col->findone(array(‘_id’ => $id));

echo ‘Single document (_id = $id):<br/><pre>’;
print_r($doc);
[/php]

The MongoDB support in PHP has function to pull a single document, or all the documents. Once again, we use arrays to define something, in this example, we define our constraint.

Upading Data
MySQL

[php]
$sql = “UPDATE `$tbl` SET `password` = ‘b497dd1a701a33026f7211533620780d’ WHERE `id` = {$id}”;
$qry = mysql_query($sql, $link);

$sql = “SELECT * FROM `$tbl` WHERE `id` = {$id}”;
$qry = mysql_query($sql, $link);
$row = mysql_fetch_assoc($qry);

echo ‘Updated row (id = $id):<br/><pre>’;
print_r($row);
echo ‘</pre>
[/php]

MogoDB

[php]
// Update a document
$col->update(array(‘_id’ => $id), array(‘$set’ => array(‘password’ => ‘b497dd1a701a33026f7211533620780d’)));

// Query the updated docuemnt
$doc = $col->findone(array(‘_id’ => $id));

echo ‘Updated docuement:<br/><pre>’;
print_r($doc);
echo ‘</pre>’;
[/php]

The MongoDB extension has a function to perform updates. The first argument is an array of constraints, IE “WHERE {expression}”. The second is the data we want to update and what we want to update it with. Pretty simple, right?

Indexing data
MySQL

[php]
// Create a unique index
$sql = “ALTER TABLE `$db`.`$tbl` ADD UNIQUE `login` (`login`)”;
$qry = mysql_query($sql, $link);
[/php]

MongoDB

[php]
// Create a unique index
$col->ensureIndex(array(“login” => 1), array(“unique” => true, “dropDups” => true));
[/php]

MongoDB has method for creating index name “ensureIndex”. The first parameter is an array of what we want to index and the value is ether 1 or -1. 1 means that the index will sort the data for this in an ascending manor, while -1 will sort it in a descending manor. The second parameter are extra options. In this example I tell it to make what ever I’m indexing as a unique index, that way I don’t end up with multiple “jsmith” entries. Also, I’ve told it to drop any documents that have duplicates of the data in the “login” “column”. Another note is that “columns” in MongoDB should be called “elements”, since we are really dealing with arrays.

Lets make sure our index is working shall we?
MongoDb

[php]
// Test our unique index
try
{
$doc = array(‘login’ => ‘jsmith’, ‘password’ => ‘ 5f4dcc3b5aa765d61d8327deb882cf99′, ‘email’ => ‘jsmith@example.com’);
$col->insert($doc, true);
}
catch(MongoCursorException $e)
{
echo ‘Could not insert document. Double check values.<br />’;
}