How to make a search form with multiple search options in PHP & MySQL

How to make a search form with multiple search options in PHP & MySQL is a PHP & MySQL tutorial that will show you that it is possible to process a search form which has multiple criteria with a single line of SQL statement.

So the idea is to build something like shown in the image below

The Multiple Options Search Form

You can click here https://www.makesenseproperties.com/ to go to the page to see what the multiple search criteria form looks like and also test how it works.

Multiple Option Search Form Processing

As earlier noted, one line of SQL statement can process this kind of multi option search form.

The way this form functions is that apart from the form fields: –House or Land?, –Rent or Buy?, –Select State– and –In Which LGA– which are compulsory, every other field isn’t compulsory.

So the question is: How do we combine the sql to cater for both compulsory fields and optional fields?. Note that user 1 apart from the compulsory fields may decide to include only –Select Area/Proximity– in his/her search; user 2 apart from the compulsory fields may decide to include only –Select Area/Proximity– and Apartment Type as his/her own search criteria; user 3 apart from the compulsory fields may decide to include only Apartment Type as his/her own search criteria; while user 4 apart from the compulsory fields may decide to include all search criteria as his/her own search criteria.

Receive Form Post

//if search form is posted
If (isset($_POST['searchForm'])) {
     //Receive search options posted
     $propertyFor = $_POST['House_or_Land'];//compulsory field
     $propertyType = $_POST['Rent_or_Buy'];//compulsory field
     $stateName = $_POST['State'];//compulsory field
     $selLGA = $_POST['LGA'];//compulsory field
     $selectArea = $_POST['Area'];//optional field
     $fromPrice = $_POST['Price'];//optional field
     $toPrice = $_POST['toPrice'];//optional field
     $apartmentTypes = $_POST['apartmentType'];//optional field
     $Bedrooms = $_POST['Bedrooms'];//optional field
}

The Main MySQL Statement

Here is the main mysql statement that makes the multiple options search possible in this particular search form:

SELECT * from property where Property_is_for = :Property_is_for AND Property_type = :Property_type AND State_of_location = :State_of_location AND LGA_of_location = :LGA_of_location AND (:Area_of_location = ” OR Area_of_location = :Area_of_location) AND ((:minAmount = ” AND (AMOUNT <= :maxAmount OR :maxAmount = ”)) OR (:maxAmount = ” AND (AMOUNT >= :minAmount OR :minAmount = ”)) OR (:minAmount != ” AND :maxAmount != ” AND (AMOUNT BETWEEN :minAmount AND :maxAmount))) AND (:Apartment_Type = ” OR it_is_a = :Apartment_Type) AND (:No_of_bedrooms = ” OR No_of_bedrooms = :No_of_bedrooms)

Please note that i am using PDO statements, so you should look up PDO if you are not familiar with it before continuing with this.

try {
        $my_prop_query = "SELECT * from property where Property_is_for = :Property_is_for AND Property_type = :Property_type AND State_of_location = :State_of_location AND LGA_of_location = :LGA_of_location AND (:Area_of_location = '' OR Area_of_location = :Area_of_location) AND ((:minAmount = '' AND (AMOUNT <= :maxAmount OR :maxAmount = '')) OR (:maxAmount = '' AND (AMOUNT >= :minAmount OR :minAmount = '')) OR (:minAmount != '' AND :maxAmount != '' AND (AMOUNT BETWEEN :minAmount AND :maxAmount))) AND (:Apartment_Type = '' OR it_is_a = :Apartment_Type) AND (:No_of_bedrooms = '' OR No_of_bedrooms = :No_of_bedrooms)";
        $q = $conn->prepare($my_prop_query);
        $q->execute(array(':Property_is_for' => $propertyFor, ':Property_type' => $propertyType, ':State_of_location' => $stateName, ':LGA_of_location' => $selLGA, ':Area_of_location' => $selectArea, ':minAmount' => $fromPrice, ':maxAmount' => $toPrice, ':Apartment_Type' => $apartmentTypeString, ':No_of_bedrooms' => $Bedrooms));
        $q->bindColumn('column_name1', $column_name1);
        //continue binding more columns as needed
        while ($q->fetch()) {//while there is at least one record
            //echo resultset here
        }
    } catch (PDOException $e) {
        //handle the exception here
    }

Dissecting The MySQL Statement

So we already know that after the first four fields in the form under survey (please go here to see form https://www.makesenseproperties.com/), the rest of the fields are optional. There are no rules set for the application users’ as to how to combine the optional fields in any particular one search, therefore we must handle it using the MySQL statement presented above.

The first four compulsory fields are itemized in the query first and i use the SQL logical operator AND to show that the must be supplied at all times.

Once we get to Area_of_location which is not compulsory, i use AND (:Area_of_location = ‘ ‘ OR Area_of_location = :Area_of_location). This means either Area_of_location is chosen or not chosen.

For Price Range fields (i.e minimun price and maximum price), there are 3 possible combinations.

  • Either min amount not chosen and max amount chosen OR min amount not chosen and max amount not also chosen. (:minAmount = ‘ ‘ AND (AMOUNT <= :maxAmount OR :maxAmount = ‘ ‘))

OR

  • Either max amount not chosen and min amount chosen OR max amount not chosen and min amount not also chosen. (:maxAmount = ‘ ‘ AND (AMOUNT >= :minAmount OR :minAmount = ‘ ‘))

OR

  • both min amount and max amount chosen and of course AMOUNT is not lower than minAmount and not greater than maxAmount. (:minAmount != ‘ ‘ AND :maxAmount != ‘ ‘ AND (AMOUNT BETWEEN :minAmount AND :maxAmount))

Please note that AMOUNT is a column in our property table.

Apartment_Type field and No_of_bedrooms field both have same explanation as Area field already explained above. The code affecting both simple states: either chosen to be part of the search options or not chosen. SIMPLE.

Conclusion

When all of each part of the sql explained in the section: Dissecting The MySQL Statement above is fused together, it allows you to build a flexible multiple option search form where a user can query both for compulsory and any number of optional items as part of his/her search criteria.

You should take the pattern shown here to implement in your own project.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

You May Also Like