How do I combine two arrays in PHP based on a common key?

Question

Hi, I'm trying to join two associative arrays together based on an entry_id key. Both arrays come from individual database resources, the first stores entry titles, the second stores entry authors, the key=>value pairs are as follows:

array (
    'entry_id' => 1,
    'title' => 'Test Entry'
)   

array (
    'entry_id' => 1,
    'author_id' => 2

I'm trying to achieve an array structure like:

array (
    'entry_id' => 1,
    'author_id' => 2,
    'title' => 'Test Entry'
)

Currently, I've solved the problem by looping through each array and formatting the array the way I want, but I think this is a bit of a memory hog.

$entriesArray = array();
foreach ($entryNames as $names) {
    foreach ($entryAuthors as $authors) {
        if ($names['entry_id'] === $authors['entry_id']) {
            $entriesArray[] = array(
                'id' => $names['entry_id'],
                'title' => $names['title'],
                'author_id' => $authors['author_id']
            );                          
        }
    }
}

I'd like to know is there an easier, less memory intensive method of doing this?

Answer 1

Is it possible you can do a JOIN in the SQL used to retrieve the information from the database rather than fetching the data in multiple queries? It would be much faster and neater to do it at the database level.

Depending on your database structure you may want to use something similar to

SELECT entry_id, title, author_id
FROM exp_weblog_data
INNER JOIN exp_weblog_titles
ON exp_weblog_data.entry_id = exp_weblog_titles.entry_id
WHERE field_id_53 = "%s" AND WHERE entry_id IN ("%s")

Wikipedia has a bit on each type of join

Otherwise the best option may be to restructure the first array so that it is a map of the entry_id to the title

So:

array(
    array(
       'entry_id' => 1,
       'title' => 'Test Entry 1',
    ),
    array(
       'entry_id' => 3,
       'title' => 'Test Entry 2',
    ),
)

Would become:

array(
    1 => 'Test Entry 1',
    3 => 'Test Entry 2',
)

Which would mean the code required to merge the arrays is simplified to this:

$entriesArray = array();
foreach ($entryAuthors as $authors) {
    $entriesArray[] = array(
        'id' => $authors['entry_id'],
        'title' => $entryNames[$authors['entry_id']],
        'author_id' => $authors['author_id']
    );                           
}

Answer 2

see http://php.net/manual/en/function.array-merge.php

Answer 3

In response to your comment on Yacoby's post, will this SQL not give the output you are after?

SELECT exp_weblog_data.entry_id, exp_weblog_data.field_id_5 AS title_ie, exp_weblog_titles.author_id 
FROM exp_weblog_data LEFT JOIN exp_weblog_titles 
ON exp_weblog_data.entry_id = exp_weblog_titles.entry_id 
WHERE exp_weblog_data.field_id_53 = "%S"

Every entry in exp_weblog_data where field_id_53 = "%S" will be joined with any matching authors in exp_weblog_titles, if a an entry has more than one author, two or more rows will be returned.

Answer 4

I've rearranged some of my code to allow for a single SQL query, which looks like:

$sql = sprintf('SELECT DISTINCT wd.field_id_5, wd.entry_id, mb.email, mb.screen_name 
            FROM `exp_weblog_data` wd
            INNER JOIN `exp_weblog_titles` wt
            ON wt.entry_id=wd.entry_id
            INNER JOIN `exp_members` mb
            ON mb.member_id=wt.author_id
            WHERE mb.member_id IN ("%s")
            AND wd.entry_id IN ("%s")',
            join('","', array_unique($authors)),
            join('","', array_unique($ids))
);

This solves my problem quite nicely, even though I'm making another SQL call. Thanks for trying.