Array joining using an id as reference

Question

I have 2 tables. The first table is an equipment table. A query from this table looks like this:

id    name      user_id
-----------------------
1     equip1    1001
2     equip2    1002

The seconde table is an users table. A query from this table looks like this:

id     username
--------------
1001   user1
1002   user2

I want to achieve something like this:

id    name      user_id    username
-----------------------------------
1     equip1    1001       user1
2     equip2    1002       user2

Is there a way to join both arrays like doing a join query? I can't use JOIN in my query, because the tables are on different databases (I know there is a way to do JOIN on different databases, but I'm not allowed to use that).

EDIT:

I'm adding the structure of these arrays.

$equipment = array( 
                [0] => array( 
                    ['id'] => 1, 
                    ['name'] => 'equip1', 
                    ['user_id'] => 1001 
                ),
                [1] => array( 
                    ['id'] => 2, 
                    ['name'] => 'equip2', 
                    ['user_id'] => 1002
                ) 
             );

$users= array( 
                [0] => array( 
                    ['id'] => 1001, 
                    ['username'] => 'user1'
                ),
                [1] => array( 
                    ['id'] => 1002, 
                    ['username'] => 'user2'
                ) 
             );

Answer 1

You would likely have to join the queries yourself. I don't believe there is a built in function (not counting walk or map with a callback). This is what I would do

//empty array for indexing users under their id for faster loopups
$users = array();

//loop over the users result
foreach($usersResult as $row){
    //index users under their id.
    $users[$row['id']] = $row['username'];
}

//now loop over the equipment to join the arrays together
foreach($equipmentResult as $key=>$row){
    //add the username column
    $row['username'] = isset($users[$row['user_id']])?$users[$row['user_id']]:null;

    //save back into the equipment row
    $equipmentResult[$key] = $row;
}

//display
print_r($equipmentResult);

This could easily be turned into a function where you pass arguments that would build the "ON" portion for the column names.

Edit: Made it a function.

<?php

/**
 * Joins two arrays as if they were joined in a query
 * @param  Array   $arrayA   The base (left) array to join into
 * @param  Array   $arrayB   The right array to join into A
 * @param  String  $colA     The column name to join on for arrayA
 * @param  String  $colB     [optional] The column name to join on for arrayB. If 
 *                           blank, then it is assumed the same column name as colA
 * @param  boolean $leftJoin [optional] Should this be a left join and include rows
 *                           from A where no value exists in B?
 * @return void
 */
function array_join($arrayA, $arrayB, $colA, $colB=null, $leftJoin=false){
    //if no value was passed for colB, assume it is the same value as colA
    if(is_null($colB)){
        $colB = $colA;
    }

    //output data
    $out = array();

    //create an index for array B for faster lookups
    $idxB = array();
    $colsB = array();
    foreach($arrayB as $row){
        //get the value from B
        $valB = $row[$colB];
        //if the column doesn't exist in the index, add it
        if(!isset($idxB[$colB])){
            $idxB[$colB] = array();
        }
        //index the value
        $idxB[$valB][] = $row;

        //store the known column to an array for use below
        if(empty($colsB)){
            $colsB = array_keys($row);
        }
    }

    //loop over array A
    foreach($arrayA as $rowA){
        //get the value for the column
        $valA = $rowA[$colA];

        //does the value from A exist in B
        $rowB = isset($idxB[$valA])?$idxB[$valA]:null;

        //join the rows
        //add blank columns if left join
        if($leftJoin && is_null($rowB)){
            $rowBJoin = array_combine($colsB, array_fill(0, count($colsB), null));
            //add the row to our output
            $out[] = $rowA + $rowBJoin;
        } else {
            //inner join or value is not null
            //loop over all the rows from the B index that we are joining on
            foreach($rowB as $rowBJoin){
                //add the row to our output
                $out[] = $rowA + $rowBJoin;
            }
        }
    }

    return $out;
}