PHP optimize nested loop

Question

I have an array of matches, each match has an ID and an array of users (from 2 to 4), each user is uniquely identified by it's own user_id. Example of the array:

array (size=2)
 0 => 
 array (size=2)
 'id' => int 11268
 'users' => 
 array (size=2)
 0 => 
 array (size=1)
 'user_id' => int 960781
 1 => 
 array (size=1)
 'user_id' => int 960786
 1 => 
 array (size=2)
 'id' => int 11267
 'users' => 
 array (size=2)
 0 => 
 array (size=1)
 'user_id' => int 960783
 1 => 
 array (size=1)
 'user_id' => int 902177

Now I want to add users details to the above array, so I do a query on the DB and I have this: (the row with id=n contains the details of user with user_id=n)

if ($res = $stmt->get_result()) { // it gets user details
 while($row=$res->fetch_assoc()) {
 foreach ($matches as &$match) {
 foreach ($match['users'] as &$user) {
 if($user['user_id']==$row['id']) {
 $user['details']=$row;
 }
 }
 }
 }
}

This is working fine, but it's not the best way, because for each row I walk all the array. Do you have any idea how can I optimize this?

Thanks very much

• u_mulder: Store user details in another array and if needed - take value from it.
• VolkerK: It all boils down to when and where to build a lookup table to reduce the "costs" from iterating over the arrays to a (hopefully) cheaper hashtable access. I guess you have to iterate that "matches"-array first to know what user details you have to query? If so, you could build a lookup then and there.

Answer 1

You can simplify the problem by indexing the user arrays by userid. The code becomes a bit more complex now, but the computational complexity class is lower. If the new solution is really faster depends on the actual sizes of the individual arrays, so you'd have to measure both solutions with real-life-production-data to see what the fastest solution actually is.

<?php
function index_array_by($array, $key) {
 $result = array();
 foreach($array as &$value) {
 $new_key = $value[$key];
 $result[$new_key] = &$value;
 }
 return $result;
}
foreach($matches as &$match) {
 $match['users'] = index_array_by($match['users'], "user_id");
}
if ($res = $stmt->get_result()) { // it gets user details
 while($row=$res->fetch_assoc()) {
 foreach ($matches as &$match) {
 $user_id = $row['id'];
 $match['users'][$user_id]['details'] = $row;
 }
 }
}
?>

Answer 2

I found this solution that require to scan the array only once, but I guess it uses more memory since I'm saving the rows into an array:

//Save the results in an array to later add to the matches array
 if ($res = $stmt->get_result()) { //second get: it gets user details
 while($row=$res->fetch_assoc()) {
 $rows[$row['id']]=$row;
 }
 }
 mysqli_free_result($res);
 //Add the user details to the matches array
 foreach ($matches as &$match) {
 foreach ($match['users'] as &$user) {
 $user['details']=$rows[$user['user_id']];
 }
 }