PHP & MongoDB Sitting in a Tree: Part 1

Posted by ellisgl on May 12, 2010

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:\data\db”.

Connecting
MySQL

<?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.");

MongoDB

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

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

$db  = 'testdb';
$sql = "CREATE DATABASE `$db`";

mysql_query($sql, $link);
mysql_select_db($db, $link);

MongoDB

$db   = $link->testdb;

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

// 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);

MongoDB

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

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

// 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);

MongoDB

// 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'];

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

// 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):&lt;br/&gt;&lt;pre&gt;';
print_r($row);
echo '</pre>

MongoDB

// 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);

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

$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>

MogoDB

// 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>';

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

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

MongoDB

// Create a unique index
$col->ensureIndex(array("login" => 1), array("unique" => true, "dropDups" => true));

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

// 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 />';
 }

If all works, we should get an error message.

Limiting Results
MySQL

// Limits
$sql = "SELECT * FROM `$tbl` LIMIT 1";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All rows - Limit by 1:<br/>';

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

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

MongoDB

// Limits
$res = $col->find()->limit(1);

echo 'All documents - Limited to 1:<br/>';

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

Offsetting
MySQL

// Limits
$sql = "SELECT * FROM `$tbl` OFFSET 1 LIMIT 1";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All rows - Limit by 1 - Offset by 1:<br/>';

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

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

MongoDB

// Offsets
$res = $col->find()->skip(1)->limit(1);

echo 'All documents - Limited to 1, Offset by 1:<br/ >';

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

To offset a your query start point, we used the method “skip” with an integer value to create the offset.

Sorting
MySQL

// Sorting
$sql = "SELECT * FROM `$tbl` ORDERBY `login` DESC";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All rows -  Sorted by login descending:<br/>';

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

       echo '<pre>';
       print_r($row);
       echo '</pre>'
     }while(--$cnt > 0);
 }

MongoDB

// Sorting
$res = $col->find()->sort(array('login' => -1));

echo 'All documents - Sorted by login descending:<br/>';

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

Here we used the sort method, told it what element to sort on and which direction, -1 being descending. The record for “psmith” should show up first. A word of caution here, there is a 4MB limit if sorting on non-indexed element.

Advanced Queries
MySQL

// Advanced queries
// Add new column
$sql = "ALTER `$db`.`$tbl` ADD COLUMN `age` INT UNSIGNED";
mysql_query($sql, $link);

// Add values
$sql = "UPDATE `$tbl` SET `age` = 40 WHERE `login` = 'jsmith'";
mysql_query($sql, $link);

$sql = "UPDATE `$tbl` SET `age` = 24 WHERE `login` = 'psmith'";
mysql_query($sql, $link);

// Query for anyone under 30
$sql = "SELECT * FROM `$tbl` WHERE `age` < 30";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone under 30:<br/>';

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

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

// Query for anyone over 30
$sql = "SELECT * FROM `$tbl` WHERE `age` > 30";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone over 30:<br/>';

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

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

// Query from anyone 25 and over
$sql = "SELECT * FROM `$tbl` WHERE `age` >= 25";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone 25 and over:<br/>';

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

       echo '<pre>';
       print_r($row);
       echo '</pre>';
     }
 }
// Query for anyone 25 and under
$sql = "SELECT * FROM `$tbl` WHERE `age` <= 25";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone 25 and under:<br/>';

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

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

// Query for anyone not 40
$sql = "SELECT * FROM `$tbl` WHERE `age` != 40";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone not 30:<br/>';

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

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

// Query for anyone 20-50
$sql = "SELECT * FROM `$tbl` WHERE `age` >= 20 AND `age` <= 50";
$qry = mysql_query($sql, $link);
$cnt = mysql_num_rows($qry);

echo 'All documents - Anyone 20-50:<br/>';

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

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

MongoDB

// Advanced queries
// Add a new element and value
$col->update(array('login' => 'jsmith'), array('$set' => array('age' => 40)));
$col->update(array('login' => 'psmith'), array('$set' => array('age' => 24)));

// Confirm
$res = $col->find();

echo 'All documents - Confirming age element:<br/>';

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

// Query for anyone under 30
$res = $col->find(array('age' => array('$lt' => 30)));

echo 'All documents - Anyone under 30:<br/>';

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

// Query for anyone over 30
$res = $col->find(array('age' => array('$gt' => 30)));

echo 'All documents - Anyone over 30:<br/>';

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

// Query from anyone 25 or over
$res = $col->find(array('age' => array('$gte' => 25)));

echo 'All documents - Anyone 25 or older:<br/>';

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

// Query for anyone 25 and under
$res = $col->find(array('age' => array('$lte' => 25)));

echo 'All documents - Anyone 25 or younger:<br/>';

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

// Query for anyone not 40
$res = $col->find(array('age' => array('$ne' => 40)));

echo 'All documents - Anyone not 40:<br/>';

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

// Query for anyone 20-50
$res = $col->find(array('age' => array('$gte' => 20, '$lte' => 50)));

echo 'All documents - Anyone 20-50:<br/>';

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

With MongoDB you can do “<,<=, >, >=” conditional operator based queries. Only thing is that you can’t just use “<,<=, >, >=” in your constraints, you have to use MongoDB’s version. Those are “$lt” (less than), “$lte” (less than or equal), “$gt” (greater than), “$gte” (greater than or equal). Yes, you need the dollar sign. Other conditional operators are “$ne” (not equal to), “$in” (like MySQL’s IN statement), “$nin” (NOT IN), “$mod” (Modulo operation), “$all” (like in, but all values much be matched to a document), “$size” (Match of number of elements), “$exists” (element exists), “$type” (matches element type – int, string, etc) and there are couple more.

Deleting a Row / Docuement
MySQL

// Delete a row
$sql = "DELETE FROM `$tbl` WHERE `login` = 'psmith'"
mysql_query($sql, $link);

MongoDB

// Delete a document
$col->remove(array('age' => 24), array('justOne' => true, 'safe' => true));

// Confirm delete
$res = $col->find();

echo 'All documents:<br/>';

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

Here we use the “remove” method. First argument has the constraints and the second tell it to only remove one item and also to run a “safe” query.

Dropping a Table / Collection
MySQL

// Drop table
$sql = "DROP TABLE `$db`.`$tbl`";
mysql_query($sql, $link);

MongoDB

// Drop collection
$col->drop();

Dropping a database
MySQL

// Drop DB
$sql = "DROP DATABASE `$db`";
mysql_query($sql, $link);

MongoDB

// Drop DB
$db->drop();

Disconnect
MySQL

// Disconnect
mysql_close($link);

MongoDB

// Disconnect
$link->close();

Until next time, when I will go over more stuff, happy coding.

If you liked this tutorial, please check out our tech deals and Computer related coupons.
Sources:
PHP.net, MongoDB

Part 2>>
Short Link: http://tk2.us/mongodb-1