<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title>SQL to Mongo Mapping Chart</title> </head> <body><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mongo.writeconcerns.html">Write Concerns</a></div> <div class="next" style="text-align: right; float: right;"><a href="mongo.connecting.html">Connecting</a></div> <div class="up"><a href="mongo.manual.html">Manual</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div><hr /><div id="mongo.sqltomongo" class="chapter"> <h1>SQL to Mongo Mapping Chart</h1> <p class="para"> This is a PHP-specific version of the <a href="http://docs.mongodb.org/manual/reference/sql-comparison/" class="link external">» SQL to Mongo</a> mapping chart in the main docs. </p> <p class="para"> <table class="doctable informaltable"> <thead> <tr> <th>SQL Statement</th> <th>Mongo Query Language Statement</th> </tr> </thead> <tbody class="tbody"> <tr> <td> <em>CREATE TABLE USERS (a Number, b Number)</em> </td> <td> Implicit or use <span class="function"><a href="mongodb.createcollection.html" class="function">MongoDB::createCollection()</a></span>. </td> </tr> <tr> <td> <em>INSERT INTO USERS VALUES(1,1)</em> </td> <td> <em>$db->users->insert(array("a" => 1, "b" => 1));</em> </td> </tr> <tr> <td> <em>SELECT a,b FROM users</em> </td> <td> <em>$db->users->find(array(), array("a" => 1, "b" => 1));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE age=33</em> </td> <td> <em>$db->users->find(array("age" => 33));</em> </td> </tr> <tr> <td> <em>SELECT a,b FROM users WHERE age=33</em> </td> <td> <em>$db->users->find(array("age" => 33), array("a" => 1, "b" => 1));</em> </td> </tr> <tr> <td> <em>SELECT a,b FROM users WHERE age=33 ORDER BY name</em> </td> <td> <em>$db->users->find(array("age" => 33), array("a" => 1, "b" => 1))->sort(array("name" => 1));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE age>33</em> </td> <td> <em>$db->users->find(array("age" => array('$gt' => 33)));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE age<33</em> </td> <td> <em>$db->users->find(array("age" => array('$lt' => 33)));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE name LIKE "%Joe%"</em> </td> <td> <em>$db->users->find(array("name" => new MongoRegex("/Joe/")));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE name LIKE "Joe%"</em> </td> <td> <em>$db->users->find(array("name" => new MongoRegex("/^Joe/")));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE age>33 AND age<=40</em> </td> <td> <em>$db->users->find(array("age" => array('$gt' => 33, '$lte' => 40)));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users ORDER BY name DESC</em> </td> <td> <em>$db->users->find()->sort(array("name" => -1));</em> </td> </tr> <tr> <td> <em>CREATE INDEX myindexname ON users(name)</em> </td> <td> <em>$db->users->ensureIndex(array("name" => 1));</em> </td> </tr> <tr> <td> <em>CREATE INDEX myindexname ON users(name,ts DESC)</em> </td> <td> <em>$db->users->ensureIndex(array("name" => 1, "ts" => -1));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE a=1 and b='q'</em> </td> <td> <em>$db->users->find(array("a" => 1, "b" => "q"));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users LIMIT 20, 10</em> </td> <td> <em>$db->users->find()->limit(10)->skip(20);</em> </td> </tr> <tr> <td> <em>SELECT * FROM users WHERE a=1 or b=2</em> </td> <td> <em>$db->users->find(array('$or' => array(array("a" => 1), array("b" => 2))));</em> </td> </tr> <tr> <td> <em>SELECT * FROM users LIMIT 1</em> </td> <td> <em>$db->users->find()->limit(1);</em> </td> </tr> <tr> <td> <em>EXPLAIN SELECT * FROM users WHERE z=3</em> </td> <td> <em>$db->users->find(array("z" => 3))->explain()</em> </td> </tr> <tr> <td> <em>SELECT DISTINCT last_name FROM users</em> </td> <td> <em>$db->command(array("distinct" => "users", "key" => "last_name"));</em> </td> </tr> <tr> <td> <em>SELECT COUNT(*y) FROM users</em> </td> <td> <em>$db->users->count();</em> </td> </tr> <tr> <td> <em>SELECT COUNT(*y) FROM users where AGE > 30</em> </td> <td> <em>$db->users->find(array("age" => array('$gt' => 30)))->count();</em> </td> </tr> <tr> <td> <em>SELECT COUNT(AGE) from users</em> </td> <td> <em>$db->users->find(array("age" => array('$exists' => true)))->count();</em> </td> </tr> <tr> <td> <em>UPDATE users SET a=1 WHERE b='q'</em> </td> <td> <em>$db->users->update(array("b" => "q"), array('$set' => array("a" => 1)));</em> </td> </tr> <tr> <td> <em>UPDATE users SET a=a+2 WHERE b='q'</em> </td> <td> <em>$db->users->update(array("b" => "q"), array('$inc' => array("a" => 2)));</em> </td> </tr> <tr> <td> <em>DELETE FROM users WHERE z="abc"</em> </td> <td> <em>$db->users->remove(array("z" => "abc"));</em> </td> </tr> </tbody> </table> </p> </div> <hr /><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mongo.writeconcerns.html">Write Concerns</a></div> <div class="next" style="text-align: right; float: right;"><a href="mongo.connecting.html">Connecting</a></div> <div class="up"><a href="mongo.manual.html">Manual</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div></body></html>