从单个MySQL查询中获得最大收益

Question

I have a website that lists apartments that are available for rent. I have a few different tables in my MySQL database. One table is for basic information about the Apartment building as a whole, including address, ammenities, photos, an ID (NID), etc.

Another table lists the units available for rent within each building. So each apartment building has multiple floorplans, with a few studios, a few 1 bedroom units, sometimes a few 2 bedroom units, etc.

Currently I query my 'unit' table and ask for all units in a given apartment building. My query looks something like SELECT * FROM node_unit WHERE nid='555'

This might return something like the following:

NID    Name       Rent    SqFT    Bedrooms    Bathrooms
555    Unit 1     $500    620      0            1
555    Unit 2     $550    680      0            1
555    Unit 3     $600    820      1            1
555    Unit 4     $650    920      1            1
555    Unit 5     $700    1220     2            1
555    Unit 6     $800    1420     2            2
555    Unit 7     $900    1500     3            2
555    Unit 8     $1100   1620     3            3

etc, etc

What I am then doing in my PHP is using an accordian to group the 1 bedrooms together, the 2 bedrooms together, etc.

Not all apartments have 2 bedrooms units, some only have 1 bedroom units, so I need to know within my PHP code if I should print another accordian.

Currently I am using multiple hits to the database to determine if a given building has any 2 bedroom units, if so print another row. Does this building have any 3 bedroom units? If so print another row, but I would like to stop hitting my database so much.

Finally, here is my question:

How can I store the results from my first DB call and somehow parse thru the data and and determine if a given NID has studios, 1 beds, 2 beds, etc? (I just started learning PHP/MySQL recently)

Answer 1

I would suggest a query like this:

SELECT * FROM node_unit WHERE nid='555'
ORDER BY Bedrooms ASC, Bathrooms ASC, Rent ASC

This would return your records ordered by # of bedrooms, # bathrooms, and rent amount (in that order).

You could easily store this in multidimensional array when reading from database like this (assuming mysqli use with result set stored in $result, but concept is same for other DB connection libraries)

$room_array = array();
while ($row = mysqli_fetch_assoc($result)) {
    $room_array[(int)$row['Bedrooms']][] = $row;
}

You now have a multidimensional array with number of bedrooms as first index. The array might look like this if you var_dump it:

Array (
    [0] => Array (
        [0] => Array (
            'NID' => '555',
            'Name' => 'Unit 1',
            'Rent' => '$500',
            'SqFt' => '620',
            'Bedrooms' => '0',
            'Bathrooms' => '1',
        )
        [1] => Array (
            'NID' => '555',
            'Name' => 'Unit 2',
            'Rent' => '$550',
            'SqFt' => '680',
            'Bedrooms' => '0',
            'Bathrooms' => '1',
        )
    )
    [1] => Array (
        [0] => Array (
            'NID' => '555',
            'Name' => 'Unit 3',
            'Rent' => '$600',
            'SqFt' => '820',
            'Bedrooms' => '1',
            'Bathrooms' => '1',
        )
        [1] => Array (
            'NID' => '555',
            'Name' => 'Unit 4',
            'Rent' => '$650',
            'SqFt' => '920',
            'Bedrooms' => '1',
            'Bathrooms' => '1',
        )
    [2] => Array (
        ...
    )
    [3] => Array (
        ...
    )
)

This makes it really easy to iterate over the number of bedrooms values in an outer loop, which creates your accordions, and then iterate the individual rooms in an inner loop.

foreach ($room_array as $num_bedrooms => $rooms) {
    // start accordion
    foreach ($rooms as $room) {
        // output room details
    }
    // end accordion
}

All this would only require the single query.

Also make sure you have indexes on bedrooms, bathrooms, rent (or whichever you use in the sort) as well as on nid since it is used as the filter.