CODEWITHSUNDEEP
phpmysqlarrays
Talvi Watia
Talvi Watia

Reputation: 1080

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

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?

Upvotes: 6

Views: 10648

Answers (4)

NullUserException
NullUserException

Reputation: 85468

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

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

Upvotes: 7

Mark Baker
Mark Baker

Reputation: 212412

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()

Upvotes: 1

pr1001
pr1001

Reputation: 21962

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.

Upvotes: 1

Raoul Duke
Raoul Duke

Reputation: 4311

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;
  }
}

Upvotes: 0

Related Questions