I have a php script that is running fine that allows users to search on 4 fields

country
area
type
capacity

each of these fields have various options that can be selected. Selecting one option from each of them does complete a correct search BUT this is what I can not figure it out...

I would like for each field to be able to search for "ANY" country/area/type/capacity or a mixture of each but I can not work out how to add this into my current html/php coding.

Could some help please ..


Thanks

Yarni

yarni, you didn't mention the table, so I'll makeup one, let's call it FDATA and let's call the user's search term $input. Given that, try this SQL:

SELECT * FROM FDATA WHERE country LIKE '%$input%' OR area LIKE '%$input%' OR type LIKE '%$input%' OR capacity LIKE '%$input%'

Hi again

Thx for the reply Kyrnt but I am not sure this helps me ?

the 4 fields are
$property_country
$property_area
$property_type
$sleeps

The table name is 'home'

and some people may want to search in any country or any area etc or a mixture of all 4.

Correct me if i am wrong but doesnt the 'LIKE' command look for a comparable string ?

I cant seem to get the coding right to let them choose any combination of search criteria.

thx again

Yarni

Yes, the LIKE does look for a comparable string. By using %% on either side of the user input, you are telling the database to look anywhere inside the value for that string. So if the user input 'vill', it would return Greenville or Village.

Now, let me clarify. A user may input one or more values into any of these variables, right? And if a user has something in $property_area, then you want to check the column area for matching properties. My question would be this: If a user put in an area and 'sleeps', do you want the results to be a set only of homes meeting BOTH criteria or EITHER criteria. It matters when writing the routine. Let me know and I'll show you how to solve it.

the answer to your question would be : the search should fulfill all 4 criteria : i.e

the user puts in the following :

property_country = any
property_area = any
property_type = villa
sleeps = any

or any combination of course.

The results I need back would be a villa that sleeps any amount of people anywhere in the world

cheers
Yarni

oh and in addition to the previous question ....

how do i get the forms fields to update depending on what answer they first click.

i.e. if they clicked on America (property_country), could the next field (area) show only american states ?

Thanks

Yarni

Try this, but I have no debugged it.

PHP Code:

$sql = "SELECT * FROM home WHERE ";
$bSubsequent = false;

if( $property_county != "any" )
{
    $sql .= " country LIKE '%$property_country%'";
    $bSubsequent = true;
}
if( $property_area != "any" )
{
    if( $bSubsequent )  $sql .= " AND";
    $sql .= " area LIKE '%$property_area%'";
    $bSubsequent = true;
}
if( $property_type != "any" )
{
    if( $bSubsequent )  $sql .= " AND";
    $sql .= " type LIKE '%$property_type%'";
    $bSubsequent = true;
}
if( $sleeps != "any" )
{
    if( $bSubsequent )  $sql .= " AND";
    $sql .= " capacity >= $sleeps";
    $bSubsequent = true;
}

if( ! $bSubsequent )   $sql .= " 1"; 


Originally Posted by yarni

oh and in addition to the previous question ....

how do i get the forms fields to update depending on what answer they first click.

i.e. if they clicked on America (property_country), could the next field (area) show only american states ?

Thanks

Yarni

This questions is more of a workflow problem. You will need to go back to the server and reload the page based on what option(s) the user clicked. For example, if the user were to selected "United States" from country, you will need to go back to the server and repopulate the next combo box with a drop-down of the states. This is not a trivial task and you will need to do a lot of trial and error followed by testing.

MANY many thanks for your help Kyrnt


much appreciated - I will let you know how i get on

Yarni

Thanks -- I am interested in seeing how this one turns out, Yarni.