Carl Carl - 1 year ago 75
PHP Question

Lookup values from an array using values from another array

This is my first array where I have the id's of the words I want an array of:

$needlearray = (0 => 12421, 1 => 58902, 2 => 912, 3 => 42);


Then the second array contains all the words with word data:

$haystackarray = (
0 => array('id' => 42, 'word' => "hello", 'otherdata' => "other"),
1 => array('id' => 12421, 'word' => "good", 'otherdata' => "other"),
2 => array('id' => 58902, 'word' => "hello", 'otherdata' => "other"),
3 => array('id' => 5222, 'word' => "hello", 'otherdata' => "other"),
4 => array('id' => 912, 'word' => "hello", 'otherdata' => "other"),
5 => array('id' => 43290, 'word' => "hello", 'otherdata' => "other"),
6 => array('id' => 2312, 'word' => "hello", 'otherdata' => "other")
);


I want to output in the fastest way possible, using the
$needlearray
value to look up from
$haystackarray
as the 'id'. e.g. the output I would want using example above is:

$result = (
0 => array('id' => 12421, 'word' => "good", 'otherdata' => "other"),
1 => array('id' => 58902, 'word' => "hello", 'otherdata' => "other"),
2 => array('id' => 912, 'word' => "hello", 'otherdata' => "other"),
3 => array('id' => 42, 'word' => "hello", 'otherdata' => "other"),
);


Notes:


  • Currently I am using a
    foreach
    loop with array search. It is very slow.

  • The original order of
    $needlearray
    must be maintained.

  • The actual data can contain several thousand values in
    $needlearray
    , and also several thousand arrays in
    $haystackarray
    .

  • Speed is very important as this is part of a large process.


Answer Source

You really want to do this in the database. You would do something like this:

$list  = implode(',', $needlearray);

$query = "SELECT id, word, otherdata FROM table
              WHERE id IN($list)
              ORDER BY FIELD(id, $list)";
  • Get the rows where id is IN the list
  • Order by id in list order

If you need to do it in PHP then this may or may not be faster than a foreach():

$result = array_intersect_key(array_column($haystackarray, null, 'id'),
                              array_flip($needlearray));
  • Index haystack by id
  • Flip needle to get the value as index
  • Find the intersection (common keys)

The order won't be maintained so then you would need to sort haystack by needle, so the database is the way to go.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download