PHP/SQL Grouping query results into array

Question

I am working on a website for property rentals.

Info about the database object/table relations:

One property can have multiple units (Example: A hotel is a property and the hotel rooms are the units).

Each unit has multiple prices for different periods (summer, winter etc.), in this case I want to select only the max and min price.

OneProperty->ManyUnits->ManyPrices (min and max price only in query)

After I do the query, I want to end up with an array in the same manner, where the units would be grouped by their properties instead of having one property for each of it's unit.

property1->unit1->min_price
 ->max_price
 ->unit2->min_price
 ->max_price
property2->unit1->min_price
 ->max_price
 ->unit2->min_price
 ->max_price

INSTEAD OF

property1->unit1->min_price
 ->max_price
property1->unit2->min_price
 ->max_price
property2->unit1->min_price
 ->max_price
property2->unit2->min_price
 ->max_price

What would be the proper query (or perhaps queries) and how to store the result/s into an array in this order with this info? Thank you in advance for any help.

EDIT: This is a simplified example of logic I used in the query:

SELECT 
property.id as pid,unit.id as uid, min(prices.price) as min_price, max(prices.price) as max_price
FROM unit
JOIN property
ON property.id = unit.property_id
JOIN prices
ON unit.id = prices.unit_id

• larsAnders: First you have to try to create the query and the array script yourself, and if it doesn't work, then bring it to SO.
• Bralemili: @IarsAnders I know, but I'm completely lost and not very good in sql. I can't get to the correct logic. What I would do is just join the tables and get a mess.
• larsAnders: exactly! Make a mess. Join us in the trenches.
• Don't Panic: You should be able to do this with one query. The grouping can be accomplished as you fetch results from your query by using the appropriate columns as array keys for different levels of the array. I think without seeing a little bit of query/output, this is somewhat too broad to truly answer.
• Bralemili: @Don'tPanic I have edited the post and added the logic I used for the query.

Answer 1

If you use property ID and unit ID as array keys, you should be able to construct the multidimensional array you're going for like this:

while ($row = $result->someFetchArrayMethod()) {
 $result[$row['pid']][$row['uid']] = array(
 'min_price' => $row['min_price'],
 'max_price' => $row['max_price']);
}

• Bralemili: This will solve it. I will try it out now and let you know. Thanks