mongoDB 를 PHP 에서 사용하기! _Mysql 구문과 비교 하면서 알아보자!

 

 

* Sources : http://www.jbistory.net/

 

mongoDB를 PHP에서 사용하는 방법을 알아보자!

본인의 경우 mysql 만 써오다보니 mongoDB 사용법이 별거 아니었음에도 불구하고 한참이나 헤멨었기에.. 본인이 기억하기 위해서라도 간단하게 포스팅을 해본다.

 

위에 링크를 기재하였듯이 원문은 Sources : http://www.jbistory.net/ 에서 가져온 것이다.

 

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”.

 

대략 해당 튜토리얼은 단순히 mongoDB나 NoSQL에 관한것이 아니라 다른 스타일의 튜토리얼이라고 말하고 있는듯 하다. 확실히 해당 튜토리얼은 설명이 난무한 그런 안타까운 튜토리얼이 아닌 실제 코드들로 비교하고 있는,

 

참 아름다운 튜토리얼이다. 본인 생각에는 말이다!

 

그럼 차근차근 DB 연결부터 데이터의 삽입, 갱신, 삭제를 거쳐 DB 연결해제까지 MySQL 코드와 비교해가면서 살펴보도록 하자. MySQL 을 써본 사람이라면 정말 쉽게! 바로 이해하고 사용할 수 있을 것이다.

 

 

1. Connecting DB

 

[MySQL]

 

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

 

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.

 

 

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

 

 

3. Create the Table(Collection)

 

[MySQL]

 

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

 

 $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

 

 

4. Insert a row(document) into the table(Collection)

 

[MySQL]

 

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

 

[MongoDB]

 

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

 

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

 

 

5. Get All row(Document)

 

[MySQL]

 

// Get all row

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

 

[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.

 

 

6. Updating 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>

 

[MongoDB]

 

// 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?

 

* ->update(검색조건, 업데이트내용 , true) 로 써줄 경우,

update 수행 시 update 할 내용이 존재하면 update, 없을 경우에는 insert 기능 수행

 

 

7. 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′);
    $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.

 

8. 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>’;
}

 

 

 

9. 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>’;
}

 

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

 

 

10. 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.

 

 

 

11. Deleting a Row (Document)

 

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

 

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.

 

 

 

12. Deleting a Table (Collection)

 

[MySQL]

 

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

 

[MongoDB]

 

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

 

 

13. Deleting a Database

 

[MySQL]

 

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

 

[MongoDB]

 

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

 

 

14. Disconnect

 

[MySQL]

 

// Disconnect
mysql_close($link);

 

[MongoDB]

 

// Disconnect
$link->close();

 

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

 

 

 

이정도 튜토리얼을 따라해봤다면 mysql 쓰듯이 나름 다양하고 편리하게 mongoDB를 사용할 수 있지 않을 까 생각된다!

NoSQL 이 트랜드인 요즘인 만큼!(좀 트랜드가 된지 오래된 듯도 하지만..)

한번쯤 써보는 것도 나쁘진 않을 듯하다!!

 

* SQL 과 MongoDB 의 Mapping 에 대해 좀더 보고 싶다면! 이곳을 참고하자!

=> SQL to Mongo Mapping Chart

 

그럼, 해피코딩!!