PHP: What is the best method to SQL query an array? (if you can)

Question

Consider this:

One mySQL database that has tables and rows and data within it.

One array that has the same data.

Now normally, I would have a query like this for mySQL SELECT * FROM 'table' WHERE name LIKE '%abc%'

But I want to perform that same query on the array, without having to insert it yet. The only method that comes to mind is using array_search, but the syntax is different and the method is convoluted.

Is there a shortcut here?

Answer 1

No there is no shortcut. You can't query an array with SQL. If your array contains the same data as your database table then it will be multidimensional as well which means that array_search is of no use. You will have to loop manually through the array and perform the like operation yourself, with preg_match for example.

$data = array(
  array('name' => 'foo abc bar', 'lorem' => 'ipsum'),
  array('name' => 'etc', 'lorem' => 'dolor')
);
$matches = array();
foreach ($data as $row) {
  if (preg_match('/^.*abc.*$/', $row['name']) {
    $matches[] = $row;
  }
}

Answer 2

You can't use SQL with arrays, but one way you could do your example is using array_filter():

function like_abc($v) {
    return strstr($v['name'], 'abc') !== false;
}

$filtered = array_filter($yourArray, 'like_abc');

or if you are using PHP >= 5.3.0

$filtered = array_filter($yourArray, 
    function($v) { return strstr($v['name'], 'abc') !== false;});

See it in action on ideone

---

EDIT:

You can also try PHPLinq:

// UNTESTED CODE!
$yourArray = array (
    array('name' => 'abcd', 'age' => 20),
    array('name' => 'dacb', 'age' => 45),
    array('name' => 'aadd', 'age' => 32),
    array('name' => 'babc', 'age' => 11),
    array('name' => 'afgb', 'age' => 17),
);

$result = from('$people')->in($yourArray)
            ->where('$people["name"] => strstr($people["name"], "abc") !== false')
            ->select('$people'); 

Answer 3

CakePHP has a Set::extract method that uses XPath query strings to find information. It's quite good and I think it shouldn't be too hard to use it without the rest of the CakePHP project.

Answer 4

There is an sql4array class that allows you to use SQL to retrieve data from a PHP array, although I've never used it and can't comment on how good it is. The developers do acknowledge that it is slow though, and doesn't support the entirety of SQL syntax.

There is also, as has been mentioned, PHPLinq

Personally, I'd be inclined to use array_filter()