Help with arrays / sub arrays

PHP
1

I am looking at using arrays in PHP to return some results from a MySQL query.

The query looks like this:

SELECT 
lodges.Lodge
, lodge_activities.Activity
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

Gives me:

Lodge, Activity
Lodge A, Activity 1
Lodge A, Activity 2
Lodge B, Activity 1
Lodge B, Activity 3

But I would like it to return:

Lodge, Activity A, Activity B
Lodge A, Activity 1, Activity 2
Lodge B, Activity 1, Activity 3

Which I should be able to do with arrays, but I’m not sure where to start with that as I’m getting into uncharted PHP territory.

If anyone could help out or point me in the direction of a good tutorial I’d really appreciate it. Thank you!

2

What is “Activity B”? To me, it looks like you want “Categories” as the third column of your data result.

Scott

3

Hi Scott - sorry, I see what you mean. That should probably be more like this:

SELECT 
lodges.Lodge
, lodge_activity_categories.Category
, lodge_activities.Activity
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

Gives me:

Lodge, Category, Activity
Lion Camp, Country, Kenya
Lion Camp, Accommodation Type, Tented Camp
Elephant Lodge, Country, Botswana
Elephant Lodge, Accommodation Type, Safari Lodge

But I would like it to return:

Lodge, Country, Accommodation Type
Lion Camp, Kenya, Tented Camp
Elephant Lodge, Botswana, Safari Lodge

(I realise countries and accommodation types aren’t really activities, but its a historical naming convention, ‘keywords’ would be better probably.)

4

Puh. Hmm…

If the data result is always coming in with two rows per lodge, you should be able to split up that data accordingly to get what you want. Though, I’d do my best to fix the model (the naming), so it makes more sense, first.

Scott

5

But then again, you could also just take the first value as the “Lodge”, save it to a “Lodges” array and as long as the “Lodge” is the same, store the keys (i.e. “Country” and “Accommodation Type”, and whatever else there is, as the column titles and store the rest in the “Lodges” array. So, you should have two arrays - “Lodges” and “Column Names” for the display. The lodges array also uses the keys as keys to keep track of which column the value would need to be under.

Scott

6

There are more than two categories, but in this case would just want to display the Country and Accommodation Type categories.

I’m not really sure where to start with creating the arrays though - looking online seems to throw up several examples, but I can’t see how to adapt any for this - the exact syntax required always seems to tie me in knots.

If you know of any tutorials I could follow that would apply to this sort off example, I could have a stab at it.

7

It would be something like this.

$i = 0;
foreach ($results as $result) {
    Lodges[$i]['name'] = $result['Lodge'];
    if ($i % 2 == 0) {
        Lodges[$i]['country'] = $result['Activity'];
    } else {
        Lodges[$i]['accomodation_type'] = $result['Activity'];
    }
    $i++ 
}

Or something along those lines. :smile:

Edit: Nope. The first assignment won’t work. But I think you have something to start with.

8

Thanks for the start - I assume that would go before the table displaying the results? With a while statement after table?

9

Yes. It is the loop you were asking for, which will resort the data. You’d need a loop in your page to display the data in the new Lodges array.

Scott

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.