[SOLVED] PHP/MySQL Searchable Table Question

[weaksauce1]

<?php
include 'scripts.php';				//Include common scripts
$db = dbConnect();					//Connect to database

$handicapped = 'Yes';
$city = '';
$type_of_rental = '';

//Get all listings matching criteria
    if  ( empty ( $handicapped ) || empty ( $city ) || empty ( $type_of_rental ) ) 
        $query = "SELECT * from Listings";
    else
        $query = "SELECT * FROM Listings WHERE handicapped = '$handicapped' AND city = '$city' AND type_of_rental = '$type_of_rental'";
    
    $result = mysql_query($query) or die($query . '<br />' . mysql_error());   
?>

 

Basically In the table I have about 8 different surrounding cities, 4 different types of housing, and only yes/no for handicapped. I want to make a way for people to search for housing around our college campus.

 

So someone could say they want (handicapped = doesnt matter) (city = smock) (type of rental = doesnt matter) and it will display only the results in the city of smock and will pull both handicapped options and all 4 types of rental property. In the current code if you dont select all 3 options you get all results, not matter if you filled 1 field out or 2. You still get all of them.

 

I have a hard time explaining this but I can go into further detail if needed?

 

thanks in advance...

 

[samona]

you get all results when you don't select all three because in your query

 

if  ( empty ( $handicapped ) || empty ( $city ) || empty ( $type_of_rental ) ) 
        $query = "SELECT * from Listings";
    else
        $query = "SELECT * FROM Listings WHERE handicapped = '$handicapped' AND city = '$city' AND type_of_rental = '$type_of_rental'";
    

 

 

If one field is empty, then

"SELECT * from Listings";

 

which means list everything.

[weaksauce1]

So There is no way to fix this? Even if I change the first if statement to

if  ( empty ( $handicapped ) && empty ( $city ) && empty ( $type_of_rental ) ) 

It prints

 

SELECT * FROM Listings WHERE handicapped = 'Yes' AND city = '' AND type_of_rental = ''

which displays nothing at all because the '' arent in my db

[samona]

first check whether they are all empty.  If they are then display all.

 

If not, then check to see which ones are empty.

 

The way I see it is that you will need a few if/else statements.

 

For example,

if  ( empty ( $handicapped ) && empty ( $city ) && empty ( $type_of_rental ) ) 
display everything

elseif ( !empty ( $handicapped ) && !empty ( $city ))
display where the city = $city and where handicapped = $handicapped;

elseif (!empty ( $handicapped ) && !empty ( $type_of_rental ))
display where handicapped = $handicapped and type_of_rental = $type_of_rental;

and keep going.  I think you should have about 6 if/else statements. 

 

[samona]

Sorry, one thing has to be adjusted to the code

if  ( empty ( $handicapped ) && empty ( $city ) && empty ( $type_of_rental ) ) 
display everything

elseif ( !empty ( $handicapped ) && !empty ( $city ) && empty ($type_of_rental))
display where the city = $city and where handicapped = $handicapped;

elseif (!empty ( $handicapped ) && !empty ( $type_of_rental ) && empty($city))
display where handicapped = $handicapped and type_of_rental = $type_of_rental;

 

 

Sorry for that.

[weaksauce1]

<?php
include 'scripts.php';				//Include common scripts
$db = dbConnect();					//Connect to database

$handicapped = '';
$city = '';
$type_of_rental = '';
$parking = '';
$laundry = '';
$appliances = '';
$furnishings = '';
$garbage = '';
$gas = '';
$water = '' ;
$sewage = '';
$electricity = '';
$smoking = '';
$num_bedrooms = '';
$num_bathrooms = '';
$wated_sublease = '';
$lease_term = '';

if(!isset($_GET['sort_by'])) {
	$sort_by = 'type_of_rental';
} else {
	$sort_by = mysql_real_escape_string($_GET['sort_by']);
}

function addComma($first) {
	if(!$first) {
		echo ', ';
	}
}	


//Get all listings matching criteria
if  ( empty($handicapped) && empty($city) && empty($type_of_rental) && empty($parking) && empty($laundry) && empty ($appliances) && empty($furnishings) && empty($garbage) && empty($gas) && empty($water) && empty($sewage)&& empty ($electricity)&& empty($smoking) && empty($num_bedrooms) && empty($num_bathrooms) && empty($wated_sublease) && empty($lease_term)) 
        $query = "SELECT * From Listings ORDER BY $sort_by DESC";
    else
    {
        $where = array();
        if ( !empty ( $handicapped ) ) $where[] = "`handicapped`='" . $handicapped . "'";
        if ( !empty ( $city ) ) $where[] = "`city`='" . $city . "'";
        if ( !empty ( $type_of_rental ) ) $where[] = "`type_of_rental`='" . $type_of_rental . "'";
	if ( !empty ( $parking ) ) $where[] = "`parking`='" . $parking . "'";
	if ( !empty ( $laundry ) ) $where[] = "`laundry`='" . $laundry . "'";
	if ( !empty ( $appliances ) ) $where[] = "`applicances`='" . $appliances . "'";
	if ( !empty ( $furnishings ) ) $where[] = "`furnishings`='" . $furnishings . "'";
	if ( !empty ( $garbage ) ) $where[] = "`garbage`='" . $garbage . "'";
	if ( !empty ( $gas ) ) $where[] = "`gas`='" . $gas . "'";
	if ( !empty ( $water ) ) $where[] = "`water`='" . $water . "'";
	if ( !empty ( $sewage ) ) $where[] = "`sewage`='" . $sewage . "'";
	if ( !empty ( $electricity ) ) $where[] = "`electricity`='" . $electricity . "'";
	if ( !empty ( $smoking ) ) $where[] = "`smoking`='" . $smoking . "'";
	if ( !empty ( $num_bedrooms ) ) $where[] = "`num_bedrooms`='" . $num_bedrooms . "'";
	if ( !empty ( $num_bathrooms ) ) $where[] = "`num_bathrooms`='" . $num_bathrooms . "'";
	if ( !empty ( $wanted_sublease ) ) $where[] = "`wanted_sublease`='" . $wanted_sublease . "'";
	if ( !empty ( $lease_term ) ) $where[] = "`lease_term`='" . $lease_term . "'";
        $where = implode(' AND ', $where);
        $query = "SELECT * FROM Listings WHERE " . $where . " ORDER BY $sort_by DESC";
    }

    $result = mysql_query($query) or die($query . '<br />' . mysql_error()); 
?>

 

This may not be the most simplified way to code this but it works just fine.