[eluser]Lockzi[/eluser]
Well, the code posted on postbin is the code I'm using now, and it indeed works flawlessly.
Except for the fact that it has 6 nested foreach loops, which when a lot of data comes along is gonna be inefficient.
The problem with using join was that:
If "Microsoft Office" doesn't have a downloadsite associated, if using JOIN, the whole result row (as in application, downloadsite, scraplocators, keys, categories everything) would be ignored.
It kind of is like when using JOIN, you are saying "if this then join AND if this then join AND if this then join" when using JOIN. If any of the join statements fail, the whole row is neglected.
If an application has all the fields the row is accepted.
Now, if in the other hand... You would use LEFT JOIN, and "Microsoft Office" has 2 downloadsites, the result would look something like:
Code:
["application_id"]=>
string(1) "123"
["application_description"]=>
string(359) "Microsoft office program"
["application_title"]=>
string(15) "Mozilla Firefox"
["application_searchableName"]=>
string(15) "mozilla firefox"
["application_versionfinder"]=>
string(0) ""
["applications_downloadsites_id"]=>
string(1) "1"
["downloadsite_id"]=>
string(1) "1"
["downloadsite_url"]=>
string(51) "http://www.microsoft.com"
And the following row, as in $row = $this->db->row_array() $row->next_row();
Code:
["applications_downloadsites_id"]=>
string(1) "3"
["downloadsite_id"]=>
string(1) "8"
["downloadsite_url"]=>
string(51) "http://www.download.com"
If the Application-Has-Multiple downloadsites, the first row will be complete, the second empty except for the additional downloadsite. Presenting such a result is no fun. The ideal would be if MySQL where able to return a result as a nested array, like what I got with my 6-7 foreach loops. Maybe I'm just coming about this all wrong? This amount of data scares me a bit.
