PHP & MySQL - Making a Filterable Search (example provided)

I'm putting together a practice site utilizing PHP and MySQL to replicate (or closely resemble) this site. As of right now my searchable fields include: Rent (min-max), Type of rental (any, apartment, house), Number of Bedrooms (any, 1+, 2+, 3+, 4+)

I'm not having trouble with putting together the code thus far, I'm just worried about scaling issues and want to know what is considered best practice. Also, any advice for how to replicate the 'Sidebar filter search' that is on the site I linked (eBay also has something very similar) would be awesome.

Here is where I've hit a fork in the road: Do I individually process each field filter in a MySQL query or do I call all items in a table and then filter them in PHP?

    $sql = "SELECT * FROM properties ";

$properties = Property::find_by_sql($sql);
$matched_properties = array();
foreach($properties as $property):
    if ($min_rent <= $property-&g开发者_如何学JAVAt;rent && $property->rent <= $max_rent) {
        $matched_properties[] = $property;
    }
endforeach;

The code above pulls everything from 'properties' table in MySQL and makes a Property classes (basic CRUD class) array.

Apologies if I sound jumbled, but my main question is How can I replicate a filtered search such as that on this site OR eBay.com? And am I on the right path?

Thank you

---

Generally speaking, the less data you have to pull from your database to process in PHP, the better. Less code you have to write, less resulting bugs, less data to transfer through the software stack, etc. etc. You may need to read up on how to write more complex SQL queries. The following would be a much more efficient version of the code snippet you gave:

$sql = "SELECT * FROM properties WHERE rent >= $min_rent AND rent <= $max_rent";
$matched_properties = Property::find_by_sql($sql);

For security though, you'd need to make sure $min_rent and $max_rent are validated as real numbers. (Hint: pass the inputs through is_numeric() and throw an error if this function returns false.) Otherwise a hacker could perform what's known as a Code Injection, entering other snippets of SQL query strings in place of "min_rent" or "max_rent" to make your query do lots of undesirable things.

As for implementing a filtering mechanism like that U of Iowa site, it's a matter of having links or a form in the sidebar that refreshes the page when clicked. You can do this several ways. The coolest way is probably using AJAX to automatically update the main content window. The U of Iowa site doesn't seem to be doing this, because when I click various filter links in the sidebar, the entire page refreshes instead of just the main content area.

jQuery is a really helpful JavaScript library that can help you do AJAX with minimal coding. Check it out. Basically, you'll want to build your filter sidebar so that each link clicked causes an AJAX query to your server, which pulls in an updated data set based on the user's selected filters. When the result comes back from the AJAX call, you can use JavaScript and/or jQuery methods to inject the data as needed into your page.

Good luck!