current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

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

share|improve this question
1  
Store user details in another array and if needed - take value from it. – u_mulder Sep 12 at 10:46
    
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. – VolkerK Sep 12 at 10:50

2 Answers 2

up vote 1 down vote

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

?>
share|improve this answer
up vote 0 down vote accepted

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']];
                }
            }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.