How to combine two arrays in PHP based on a shared key?

Question

I am trying to combine two associative arrays based on the entry_id key. Both arrays come from separate database resources, the first entries in store headers, the second store entry authors, key => pairs are as follows:

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

I am trying to create an array structure like:

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

I currently solved the problem by quoting each array and formatting the array the way I want, but I think it is a bit of memory.

 $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 would like to know if there is a simpler, less memory intensive way to do this?

Answer 1

I changed part of my code to allow a single SQL query that looks like this:

 $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 pretty well, although I am making another SQL call. Thanks for trying.

Answer 2

Is it possible that you can make a JOIN in SQL, which is used to extract information from a database, and not to collect data in multiple queries? It would be much faster and more accurate to do this at the database level.

Depending on the structure of your database, you might 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 for each type of connection.

Otherwise, restructuring the first array may be the best option, so this is an entry_id map for the header

So:

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

It would be:

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

This would mean that the code needed to combine arrays is simplified:

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

Answer 3

In response to your comment on a Yacoby post, will this SQL not produce the result 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" 

Each entry in exp_weblog_data, where field_id_53 = "% S" will be connected to any comparable authors in exp_weblog_titles, if the entry has more than one author, two or more lines will be returned.

Answer 4

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