Mysql left join problems

[cgeisler515]

I am creating an MLS service for apartment communities.

 

The property information is stored in it's own table `property`

 

The individual unit information is stored in it's own table `floorplans`

 

the property table stores information about the individual apartment communities. Such as name,address,amenities, and contact info.

 

the floorplan table stores information about the individual units the complex has such available.

 

each property has an id and each floorplan has an identifyier field to match the property to the floorplan (`propid`).

 

my standard search function only searches the property information for area, or zipcode.

 

I have created an advanced search field to filter the properties that have floorplans matching the criteria from the advanced search box (#beds,#baths, price,etc.)

 

I used a left join select statement.

 

SELECT * FROM `property` AS p LEFT JOIN `floorplans` AS f ON f.propid=p.propid WHERE `zip` = '77075' AND f.bed = '3' AND f.bath = '2';

 

Each property has multiple floor plans and if more then one floorplan per property matches the search criteria (beds,baths) then each floorplan is pulled as a seperate result.

 

I want the results page to display each property one time as long as at least one of the property's floorplans matches the search criteria.

 

instead it displays each floorplan that matches per property so my results page displays the same property multiple times.

 

Any idea how to correct this without too much php coding possibly a simple addition to my MySql statment.

 

Any help would be appreciated.

 

 

 

 

 

[ChemicalBliss]

As far as I know this is the way mysql works (how it returns results using JOIN statements).

 

Your SQL is fine an will return everything you need. You can display floorplan under a single property in a single loop. Think of it like everytime you loop, you check if the property has already been made (echoed or stored in a variable), you could do this with a boolean var like: $prop_done = true; - If it hasn't been done yet then you do it at the start of the loop.

 

After the first part you can then just make each floor plan as if it was a simple result of each floorplan, rather than including the property each time.

 

so;

 

while($row=mysql...) {
   
   // Check if property has been made
   
   // Make Floorplan

}

 

hope this helps

[Maq]

Sounds like the problem has to do with the cardinality of your query results.  Can you show us your table structure?

[Maq]

(moving to MySQL section for now)

[cgeisler515]

unfortunately i cannot show the table structure I am under a NDA agreement with the company that gave us the mysql data. I found a temporary workaround using ChemicalBliss idea but its not the most efficient way to do it intact it added almost 100 lines of code to my files because I define each set of data as a class example class.property.php stores all info about a property result. also contains an array of floorplans as well. class.propertylist.php is a container class for a list of properties. At the time it seemed like the best way to do things but now I am not so sure it was the best idea. I appreciate the help and if anyone else knows how i can solve this more efficiently please let me know.

 

 

[ale8oneboy]

You could try grouping by property_id, beds, baths to get a unique set of matching properties/bed/bath list. I created a test environment with similar tables. Here's the query I used:

 

SELECT * FROM `properties` p LEFT JOIN floorplans f ON p.property_id = f.property_id WHERE p.zip_code = '77075' AND f.beds = 3 and f.baths = 2
GROUP BY p.property_id, f.beds, f.baths

 

Is this what you're looking for?

[fenway]

We can't possibly help you if you can't share enough of the informationh.