Joining arrays in PHP (like MySQL join)

Question

I would like to join two arrays in PHP, the same as I could do with MySQL join left. I have two arrays:

• $db_products from database (it has id and sku fields)
• $csv_prices from a CSV file (it has sku and price fields)

$db_products: (it has 4000 items)

Array
(
    [0] => Array
        (
            [id] => 1012
            [sku] => asd123
    )
    [N] => Array
    (
            [id] => ...
            [sku] => ...
    )
)

$csv_prices: (it has 8000 items)

Array
(
    [0] => Array
        (
            [sku] => asd123
            [price] => 15.50
    )
    [N] => Array
    (
            [sku] => ...
            [price] => ...
    )
)

The join is $db_products[$key][sku] = $csv_prices[$key][sku]. To find the matching pair, I am doing loops in loops which result in 4000 * 8000 check for match. It consumes a lot of energy and time, and I want to be more efficient.

I could lower by 10% the used time, by unset()-ing the used $csv_prices[$key], but I want to be more efficient.

Answer 1

If you loop through the $csv_products array once and set the SKU as the array key then you won't have to exponentially loop through that array each time you have a new product to find its match.

Instead, you just loop through the product array and use isset() to see if it exists or not.

This way you'll only need to do 1x count($db_products) and 1x count($csv_prices) repetitions, instead of count($db_products) * count($csv_prices) reps (12 thousand vs 32 million).

The concept could be considered to be similar to indexing in databases - you use the key you want to look up as the array key/index, meaning you don't have to loop through the array every time to find the record you want.

Example:

// Reindex the CSV array to use SKU as the key
$reindexed = array();
foreach ($csv_prices as $values) {
    $reindexed[$values['sku']] = $values;
}
unset($csv_prices);

// Join the prices
foreach ($db_products as &$product) {
    $sku = $product['sku'];
    $product['price'] = isset($reindexed[$sku]) ? $reindexed[$sku]['price'] : '';
}

Of course, you won't see a remarkable difference in performance until you start using large data sets - the change would be noticeable exponentially as the data sets grow.