I have a list of street names and I want to select all that start with "Al". In my MySQL I would do something like
SELECT * FROM streets WHERE "street_name" LIKE "Al%"
How about MongoDB using PHP?
Add a comment
|
7 Answers
Use a regular expression:
db.streets.find( { street_name : /^Al/i } );
or:
db.streets.find( { street_name : { $regex : '^Al', $options: 'i' } } );
http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-RegularExpressions
Turning this into PHP:
$regex = new MongoRegex("/^Al/i");
$collection->find(array('street_name' => $regex));
3 Comments
jM2.me
Yes but how about in php? $database->streets->find(array("street" => "/^Al/i"));
Daniel
As a side question, how does this compare in terms of raw speed? The thought of using regular expressions to filter a data set makes me cringe a little... Complete newbie at mongodb though.
ceejayoz
As long as the regex starts with ^ (i.e. match against the beginning of the string), indexes will be used.
See: http://www.mongodb.org/display/DOCS/SQL+to+Mongo+Mapping+Chart
Also, highly recommend just using the native mongodb connector from PHP instead of a wrapper. It's way faster than any wrapper.
Comments
here is my working example:
<?php
use MongoDB\BSON\Regex;
$collection = $yourMongoClient->yourDatabase->yourCollection;
$regex = new Regex($text, 's');
$where = ['your_field_for_search' => $regex];
$cursor = $collection->find($where);
//Lets iterate through collection
Comments
$collection.find({"name": /.*Al.*/})
or, similar,
$collection.find({"name": /Al/})
You're looking for something that contains "Al" somewhere (SQL's '%' operator is equivalent to regexps' '.*'), not something that has "Al" anchored to the beginning of the string.
Comments
MongoRegex has been deprecated.
Use MongoDB\BSON\Regex
$regex = new MongoDB\BSON\Regex ( '^A1');
$cursor = $collection->find(array('street_name' => $regex));
//iterate through the cursor
Comments
<?php
$mongoObj = new MongoClient();
$where = array("name" => new MongoRegex("^/AI/i"));
$mongoObj->dbName->collectionName->find($where);
?>
Comments
You can also do something like this
['key' => ['$regex' => '(?i)value']]
