Help with PHP arrays to store SQL data
This is probably a paid support ticket - I'm happy to do that, but will post here what I'm looking to do.
If I have a query:
SELECT lodges.LodgeID
, lodges.Lodge
, lodge_activities.Activity
, lodges.subscription_end_date
, (SELECT Count(DISTINCT UserIDPL, LodgeIDPL)
FROM product_likes
WHERE LodgeIDPL = lodges.LodgeID)
AS connections
FROM lodges
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
INNER JOIN lodge_activity_categories
ON lodge_activities.Category_ID = lodge_activity_categories.CategoryID
It gives me results in the form:
connections, Lodge, Category, Activity
5, Lion Camp, Country, Kenya
5, Lion Camp, Accommodation Type, Tented Camp
10, Elephant Lodge, Country, Botswana
10, Elephant Lodge, Accommodation Type, Safari Lodge
But I would like it to return:
connections, Lodge, Country, Accommodation Type
5, Lion Camp, Kenya, Tented Camp
10, Elephant Lodge, Botswana, Safari Lodge
Looking for solutions online, two options have cropped up - pivot tables, and PHP arrays. The former is completely new to me, and the later sounds like the best solution, but arrays are getting into PHP territory beyond me. (I get the principle, but the syntax always ties me in knots.)
Some more details and page in progress attached below.