CODEWITHSUNDEEP
phpmysqlsql
Daniel Kellaway
Daniel Kellaway

Reputation: 189

How to show all results if search is empty

I have the code below where I am trying to search by Country. On the home page there is a dropdown box to select the Country from. My problem is that it shows the results for the selected Country but if nothing is selected then there are no results.

I want it to show ALL Country results if there is no selection.

With this code in the mysql search if country_select was empty then there are zero results:

and bam.country='".mysql_real_escape_string(trim($_POST['country_select']))."'

Here is the full code:

$searchsql = "SELECT pp_id, appmt_id, price, max( stay_from )
                        FROM (
                        SELECT bp.pp_id, bam.appmt_id, bam.appmt_name, bp.price, bp.stay_from
                        FROM bsi_apartment_master AS bam, bsi_appmt_features AS baf, bsi_priceplan AS bp
                        WHERE bam.status=true and baf.appmt_id = bam.appmt_id".$addquery." AND baf.bedroom >=".$this->bedroom." AND baf.bathroom >=".$this->bathroom."
                        and bam.country='".mysql_real_escape_string(trim($_POST['country_select']))."'
                        AND bam.appmt_id NOT IN (SELECT appmt_id FROM bsi_bookings WHERE is_deleted = FALSE AND (( '".$this->mysqlCheckInDate."'
                        BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                        OR ( DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                        OR ( checkin_date BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )
                        OR ( checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )))
                        AND bam.appmt_id = bp.appmt_id
                        AND if( ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) = true, 
                        ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) AND bp.default_pp !=0, 
                        ( ".$staycount." BETWEEN bp.stay_from AND bp.stay_to ) )
                        order by bp.stay_from desc
                        ) AS t1
                        GROUP BY appmt_id
                        order by price ".$this->sorting;

Upvotes: 1

Views: 153

Answers (5)

Sadikhasan
Sadikhasan

Reputation: 18600

Add like instead of = with % sign before and after searching parameter

and bam.country like '%".mysql_real_escape_string(trim($_POST['country_select']))."%'

Upvotes: 1

Rubin Porwal
Rubin Porwal

Reputation: 3845

$searchsql = "SELECT pp_id, appmt_id, price, max( stay_from )
                    FROM (
                    SELECT bp.pp_id, bam.appmt_id, bam.appmt_name, bp.price, bp.stay_from
                    FROM bsi_apartment_master AS bam, bsi_appmt_features AS baf, bsi_priceplan AS bp
                    WHERE bam.status=true and baf.appmt_id = bam.appmt_id".$addquery." AND baf.bedroom >=".$this->bedroom." AND baf.bathroom >=".$this->bathroom;

  if(!empty($_POST['country_select']))
  {
     $searchsql .=" and bam.country='".mysql_real_escape_string(trim($_POST['country_select']))."'";
   }
                    $searchsql.="AND bam.appmt_id NOT IN (SELECT appmt_id FROM bsi_bookings WHERE is_deleted = FALSE AND (( '".$this->mysqlCheckInDate."'
                    BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                    OR ( DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                    OR ( checkin_date BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )
                    OR ( checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )))
                    AND bam.appmt_id = bp.appmt_id
                    AND if( ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) = true, 
                    ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) AND bp.default_pp !=0, 
                    ( ".$staycount." BETWEEN bp.stay_from AND bp.stay_to ) )
                    order by bp.stay_from desc
                    ) AS t1
                    GROUP BY appmt_id
                    order by price ".$this->sorting;

Upvotes: 2

Anthony
Anthony

Reputation: 37065

You can set up the WHERE clause to work for either a value or if empty like:

AND (bam.country = $country_value OR $country_value = '')

This would make it so that not setting a value would return all possible values. However if you had rows with country actually blank, there would not be a way to filter for only those rows.

Upvotes: 1

Shrayas
Shrayas

Reputation: 6994

If there is no country selection then the country_select key would be empty in $_POST right? so how about you keep that part of the query outside. So your huge query would look something like 

query = "";
queryAllCountries = "SELECT FOO, BAR FROM TABLE WHERE FOO=SOMETHING"; 
if ($_POST[country_select] != null) {
    query = queryAllCountries + " AND COUNTRY = $_POST[country_select]";
} else {
    query = queryAllCountries;
}

mysql_query(query)

That way you append the WHERE clause ONLY if the country_select parameter is POSTED.

ps: Its been really long since i touched PHP and MySQL so the syntax might be wrong. Excuse that part. I hope you get the logic though?

Upvotes: 1

Dinesh Babu
Dinesh Babu

Reputation: 456

You can use the following code,

$country_select  = $_POST['country_select'];

//To intialize search condition
$search_condition = '';

//Search condition for selected country
$search_condition .= $country_select!=""?"bam.country = '$country_select'":"";

$searchsql = "SELECT pp_id, appmt_id, price, max( stay_from )
                        FROM (
                        SELECT bp.pp_id, bam.appmt_id, bam.appmt_name, bp.price, bp.stay_from
                        FROM bsi_apartment_master AS bam, bsi_appmt_features AS baf, bsi_priceplan AS bp
                        WHERE bam.status=true and baf.appmt_id = bam.appmt_id".$addquery." AND baf.bedroom >=".$this->bedroom." AND baf.bathroom >=".$this->bathroom." $search_condition
                        AND bam.appmt_id NOT IN (SELECT appmt_id FROM bsi_bookings WHERE is_deleted = FALSE AND (( '".$this->mysqlCheckInDate."'
                        BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                        OR ( DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) BETWEEN checkin_date AND checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) )
                        OR ( checkin_date BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )
                        OR ( checkin_date AND DATE_SUB(checkout_date, INTERVAL 1 DAY) BETWEEN '".$this->mysqlCheckInDate."' AND DATE_SUB('".$this->mysqlCheckOutDate."', INTERVAL 1 DAY) )))
                        AND bam.appmt_id = bp.appmt_id
                        AND if( ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) = true, 
                        ( ".$staycount." NOT BETWEEN bp.stay_from AND bp.stay_to ) AND bp.default_pp !=0, 
                        ( ".$staycount." BETWEEN bp.stay_from AND bp.stay_to ) )
                        order by bp.stay_from desc
                        ) AS t1
                        GROUP BY appmt_id
                        order by price ".$this->sorting;

Upvotes: 1

Related Questions