Wamp and MongoDB - II

In our previous article, we have seen installation of MongoDB on our C: drive (Windows Environment) at path "c:\mongodb\" and installed it as a Win32 Service. Now let's try to work with a bit. Let's do some basic Database stuffs.

Open command prompt and set path to the "c:\mongodb\bin" directory as shown below :

path=%path%;c:\mongodb\bin\

%path% refers to "path" environment variable; We are just appending new path to it. 

Next, we type "mongo" to start the Mongo Shell where we can run certain commands to do specific database tasks. See below what happens when we start the Mongo shell : 

MongoDB uses "Collections" which is similar to "Tables" in any R/DBMS. It also uses JSON like name/field value pair and such pairs are called "Documents". Here, JSON field value pairs are in BSON (Binary JSON) format which have additional type information. 

Next, we would try some commands at the prompt. 
   
1. db :: Will show you current Database
2. show dbs :: will show list of Databases
3. use mytestdb :: (without quote), Switch to a Database "mytestdb" even though it may not exist.

Now, we'll try CRUD (Create, Read, Update, Delete) operations. Let's insert some data inside a table called 'students' in 'myDatabase' Database.

1. use myDatabase    
2. db.myDatabase.find()    
3. db.myDatabase.insert({'fname':'Atal','lname':'Singh','roll':1})
4. db.myDatabase.insert({'fname':'Arin','lname':'Sen','roll':2})
5. db.myDatabase.insert({'fname':'Avik','lname':'Roy','roll':3})
6. db.myDatabase.insert({'fname':'Anik','lname':'Paul','roll':4})
7. db.myDatabase.insert({'fname':'Amal','lname':'Sen','roll':5})
8. db.myDatabase.insert({'fname':'Ajay','lname':'Dev','roll':6})
9. db.myDatabase.find()  
10. show collections  

The first command selects our database myDatabase.
The second command issues a SELECT SQL command with no parameter specified. So it returns all the record in the collection 'myDatabase'.
The 3 to 8 commands inserts individual records into that collection.
The 9th command again returns/shows all the records in that collection. 
The 10th command shows all the tables/collections within current selected database

Now we will try to filter the return records, i.e add "WHERE" clause to "SELECT" command. 

Say, we require names of all the students whose last name is 'Sen'. So we need issue a command like this :
db.myDatabase.find({ 'lname':'Sen' })

Here we wanted an exact match of lastname 'Sen'. However for a LIKE type query, we need issue following command.
db.myDatabase.find({ 'lname': /Sen/ })

We just used a REGEXP to specify a LIKE Query.

The find() accepts 2 parameters, first parameter is for filtering (WHERE clause) and second parameter is for selected/returned fields/columns.

Some more SELECT Query examples :: 

1) Select * from myDatabase where roll < 3 ORDER by roll DESC LIMIT 2
   db.myDatabase.find({roll:{$lt:3}}).limit(2).sort({'roll':-1})

  See that,  after the field roll, we used a sub-document { $lt : 3 } where $lt is an operator and it takes 3 as argument. 

   If we need to look for those records where roll < 10 and roll > 5 then we need to write this way: 
   db.myDatabase.find({roll:{$lt:10, $gt:5}})

2) Select fname from myDatabase where lname like '%Sen%' and status = 1
   db.myDatabase.find({'status':1, 'lname':/Sen/}, {'fname':1})
   
3) Select fname, lname from myDatabase where ( status = 0 OR status = 1) AND lname like "%Sen%"
   db.myDatabase.find( 
      { 'lname':/Sen/, 
 $or:[{'status':1}, {'status' : 0}] 
  }, {'fname':1, 'lname':1})

4) Select * from myDatabase where ( roll > 5 and roll <> 8 ) OR lname like "%Sen%"
       db.myDatabase.find(
     {  $or:[ {'lname':/Sen/}, 
      {$and   : [{'roll':{$ne:8}}, {'roll':{$gt:5}}]}
    ]
     },{'_id':0,'roll':1})
   
   db.myDatabase.find(
     { 
     $or:[ {'lname':/Sen/}, {'roll':{$ne:8, $gt:5}}] 
  },{'_id':0,'roll':1})
   
   Any of the above solution would work. 
   Note that '$ne' in MongoDB means '<>' in SQL, '$gt' in MongoDB means '>' in SQL.
   Also '$gte' in MongoDB means '>=' in SQL.

5) Select * from myDatabase order by fname, lname DESC limit 10
   db.myDatabase.find().sort({'fname':1, 'lname':-1}).limit(10);
   
6) Select count(*) from myDatabase
        db.myDatabase.find().count()
  db.myDatabase.count()

Any of the above commands will work.

7) Select distinct(fname) from myDatabase
    db.myDatabase.distinct('fname')

Some UPDATE Query examples :: 
   
1) Update myDatabase SET status = 5 where age > 16
   db.myDatabase.update(
       {age:{$gt:16}}, 
       {$set : {'status':5}}, {multi:true})

2) UPDATE myDatabase set roll = roll + 3 where age < 15   
   db.myDatabase.update(
       {age:{$lt:15}}, 
       {$inc : {'roll':3}}, {multi:true})

Some DELETE Query examples ::    

1) DELETE from myDatabase 
   db.myDatabase.remove()

2) DELETE from myDatabase where roll > 100
   db.myDatabase.remove({roll:{$gt:100}})   
   
3) DELETE from myDatabase where roll > 200 and status = 1
   db.myDatabase.remove({$and:[{roll:{$gt:200}}, {status:1} ]})      
   
In our next article, we'll write PHP code to do the above CRUD operations using MongoDB classes.   

Wamp and MongoDB - I

MongoDB is catching up faster. Before we connect it to our WAMP, let me share some important facts about MongoDB.

1.  MongoDB is a Document DATABASE. These documents are equivalent to rows in a RDBMS table. These documents are in JSON format.

2. MongoDB supports scaling feature. The MongoDB database can spread over multiple servers.  It scales OUT to multiple machine whereas Relational model follow a Scale UP pattern so that data can be served from a single server.

3. MongoDB supports neither joins nor transactions. "Table JOINS" are handled by "embedded documents and linking"

4. MongoDB does not support JOINS - This is mostly true, There is a feature MongoDB 3.2 called $lookup, that does provide some limited join functionality.

5. MongoDB features secondary indexes, an expressive query language. MongoDB provides atomic operations on a single document.

6. Instead of Tables, MongoDB stores documents in Collections.

7. In MongoDB, the primary key is automatically set to the "_id" field.

8. it has Dynamic Schemas

9. MongoDB is NOT for systems that require SQL, joins, and multi-object transactions.

10. Two Storage Engines work, MMAPv1 and WiredTiger

11. While MongoDB writes to journal files promptly, MongoDB writes to the data files lazily. 

12. Written in c++, MongoDB follows AGILE methodologies

In this article, I am going to show how we can connect PHP (Wamp in Windows environment) and MongoDB. I am using Wamp Server version 2.5 with PHP 5.5 and Apache 2.4. 

A. We need to download MongoDB Community Edition from here :: 
      https://www.mongodb.org/downloads#production ( Windows MSI installation )
      https://www.mongodb.org/dl/win32/x86_64-2008plus-ssl ( ZIP archive )
   
      We need to install/unzip it in location like c:\mongodb. The location can be anything of our choice. 
   
B. Download MongoDB driver for PHP php_mongo.dll from                                                             http://pecl.php.net/package/mongo/1.5.5/windows 
      We need to be very careful while selecting the DLL version here. I would suggest a 32 bit download as 64 bit version did not work for me though my WorkStation was a 64 bit one. 
   
      Now, we need to put this  php_mongo.dll file in our Wamp installation folder as shown below : 
      C:\wamp\bin\php\php5.5.12\ext\
   
     "C:\wamp" is the location where Wamp is installed on my work station. And the above is the location where all the PHP extensions are kept. 

C.  Now, let's do a little home work for setting up our MongoDB.
      Create 3 more folders inside "mongodb" folder namely "conf", "data" and "logs". Also create a folder called "db" inside the "data" folder.
   
   Check the folder structure as shown below : 
   
                                     

   
D. Now create a configuration file called "mongodb.conf" or anything of your choice in the "conf" folder. This configuration will be loaded when the MongoDB starts running. We can have following content within that configuration file.

       # where to log
   logpath = C:\mongodb\logs\mlogger.log
   
   # New Logs will be appended
   logappend = true

   # only run on localhost for development
   bind_ip = 127.0.0.1                                                             
   # Server PORT
   port = 27017
   
   # Enable REST interface
   rest = true
   
   # data lives here
   dbpath = C:\mongodb\data
   
   The above configuration has comments (lines starting with a hash) to understand what it means.
   Log Path, DB path have been defined here. Port no. 27017 has also been provided here. So when we finish MongoDb setup, we would be able to run http://127.0.0.1:27017. The Log file name can be anything and it should exist.
   
E. Our next step will be installing MongoDB as an Windows Service. For this we have to run Command Terminal ... 

        mongod.exe --config Path-to-Config --install

Check the screenshot below

    So, now if we check the Windows Services by running "services.msc", we'll see that a service called "MongoDB" has been successfully installed. If it is not automatically running, select it and click on "Start Service" button as shown in the screenshot below.`

So, now MongoDB is installed and is available from Browser at http://127.0.0.1:27017. Check the screenshot below.

The port number specified in MongoDB configuration file could have been anything else as well. 

Now, with php_mongo.dll copied to the "ext" folder we just need to start Wamp. 

When Wamp icon turns green in the System Tray, click on it. You can see "php_mongo" being listed there. Check the screenshot below. 

As Wamp has started, our Web Server is available at http://127.0.0.1, hence if we run http://127.0.0.1?phpinfo=1 We can see that MongoDB library is successfully loaded. Check the screenshot below.

So far, we have started MongoDB Win32 service, and we made MongoDB available to our Wamp (Apache Server). So, if we run PHP codes for MongoDB (just the way we do mysql_query() for MySQL DB), it would smoothly run.

The process is almost similar in Xampp. We need to download the correct version of php_mongo.dll and keep it C:\xampp\php\ext\ folder. Next, we need to mention this mongoDB driver php_mongo.dll inside the php.ini file. And then restart the Apache server within Xampp. Check the screenshot below .. 

In our next article, we would create a New Database and access it from within PHP itself.