[FIXED] How to do OR search

Issue

I want to search for a partial first and last name match - for example in sql

f_name LIKE J%   OR  l_name LIKE S%   

would match John Smith or John Henry or Harry Smith .

I am assuming I may need to use the "$or" operator,

I have this so far that I believe is doing the LIKE % part properly, but I believe it is doing an "AND" search (meaning it searches for f_name LIKE J% AND l_name LIKE S% so it would only match John Smith):

$name1="J";
$name2="S";
$cursor = $this->clientCollection->find(array('f_name' => new MongoRegex('/^'.$name1.'/i'), 'l_name' => new MongoRegex('/^'.$name2.'/i') ));

Note: This will match containing as in %J%

MongoRegex('/'.$name1.'/i')

This will match starts with (notice the added ^) as in J%

MongoRegex('/^'.$name1.'/i')

Solution

$or takes an array of clauses, so you basically just need to wrap another array around your current query:

array('$or' => array(
    array('f_name' => new MongoRegex('/'.$name1.'/i')), 
    array('l_name' => new MongoRegex('/'.$name2.'/i'))
));

Edit: the previous example missed an inner set of array() calls.

The original, wrong, example that I posted looked like this:

array('$or' => array(
    'f_name' => new MongoRegex('/'.$name1.'/i'), 
    'l_name' => new MongoRegex('/'.$name2.'/i')
));

This is a valid query, but not a useful one. Essentially, the f_name and l_name query parts are still ANDed together, so the $or part is useless (it's only getting passed one query, so it's the same as just running that query by itself).

As for the alternative you mentioned in your comment, that one doesn't work because the outermost array in a query has to be an associative array. The confusion arises because Mongo's query syntax is JSON-like and uses a mixture of objects and arrays, but both of those structures are represented in PHP by arrays. The PHP Mongo driver basically converts PHP associative arrays to JSON objects ({ ... }), and "normal" PHP arrays to JSON arrays ([ ... ]).

The practical upshot is that "normal" PHP arrays are generally only valid when inside an associative array, like when specifying multiple values for a field. The following example from the PHP Mongo manual shows a valid usage of a "normal" array in a query:

$cursor = $collection->find(array("awards" => array('$in' => array("gold", "copper"))));

Answered By - John Flatness

Answer Checked By - Pedro (PHPFixing Volunteer)