Carl Carl - 5 months ago 7
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

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.

Comments