高级搜索3个字段，需要查询

I have an advanced search form with three fields. The user can use anyone of the fields or any two or all fields to begin a search. so I want to check whether the user entered a value for a particular field and write queries according to the values presented through a series of if loop. But I'm curious to know whether is there any equvailent technique in mysql to avoid the if loops and achive the search in single query.?

the following is the sample of checking user input.

if($school!="" & $district1!="" & $scl_type!="")
{
    echo "3 fields available";
}
else if($school=="" & $district1!="" & $scl_type!="")
{
    echo "school empty, district,schooltype available";
}
else if($school!="" & $district1=="" & $scl_type!="")
{
    echo "school,school type available district empty";
}
else if($school!="" & $district1!="" & $scl_type=="")
{
    echo "school,district only present,school type is empty";
}

.....

so I write different queries in each if statement. Is there any shortcut?

<----UPDATE:---> I got this query, it works fine. but when two schools available in the same district it returns only one. it returns only one result even many result is available, what to do?

SELECT * FROM `register` WHERE 
    (schoolname IS NULL OR schoolname LIKE '%national%') 
            AND 
    (schooltype IS NULL OR schooltype LIKE '%state board%') 
            AND 
    (district IS NULL OR district LIKE '%thiruvarur%');

Your parameters - @school,@schooltype,@district

select items from yourtable where 
    (school like @school) 
        and 
    (schooltype like @schooltype) 
        and 
    (district like @district)

while passing parameters if any field is empty, make corresponding parameter = '%%' , for any entered fields, make corresponding parameter = "'" +enterdfieldtext +"'"

example : in your second condition, the query will look like

select * from table where  
    (school like '%%') 
        and 
    (schooltype like 'givenschooltype') 
        and 
    (district like 'givendistrict')

Put condition variable like

if($school!="" & $district1!="" & $scl_type!="")
{
    $condition = "your condition";
}
else if($school=="" & $district1!="" & $scl_type!="")
{
    $condition = "your condition";
}
else if($school!="" & $district1=="" & $scl_type!="")
{
    $condition = "your condition";
}
else if($school!="" & $district1!="" & $scl_type=="")
{
    $condition = "your condition";
}

Write your single Query here

you try this

$where = array();
if($school)
{
    $where[] =  "school where condition";
}
if($district1)
{
     $where[] =  "district1 where condition";
}
if($scl_type)
{
    $where[] =  "scl_type where condition";
}
$where = implode(' and ',$where);

$query = "select * from tablename where ".$where;

You may be interested in ternary statements:

    $condition1=$school===""?"1=1":"school=$school";
    $condition2=$district===""?"1=1":"district=$district";
    ...Etc

    SELECT * FROM database WHERE $condition1 AND $condition2 AND $etc...

Bascially, the ternary statement is equal to this:

   if($school===""){
       $condition1="1=1";
   } else {
       $condition1="school=$school";
   }

But in a single line. You could loop through each of your conditions and set $condition_X_ variables accordingly, then AND them all together in the query, and make a single query.

You will, of COURSE, want to implement some sort of injection protection before you query - like escaping the variables $school and so forth when you assign them.

Thanks guys I got the answer..

SELECT * FROM `register` WHERE 
        (schoolname IS NULL OR schoolname LIKE '%national%') 
                AND 
        (schooltype IS NULL OR schooltype LIKE '%state board%') 
                AND 
        (district IS NULL OR district LIKE '%thiruvarur%');

Query depends on your database structure, but script can look like this:

$data = filter_input_array(
    INPUT_GET,
    array(
        'street' => FILTER_SANITIZE_FULL_SPECIAL_CHARS,
        'district' => FILTER_SANITIZE_FULL_SPECIAL_CHARS,
        'scl_type' => FILTER_SANITIZE_FULL_SPECIAL_CHARS
    )
);

if($data){
    $condition = array();
    foreach($data as $key => $value){
        $condition[] = sprintf("`%s` = '%s'", $key, $value);
    }

    $SQL_QUERY = sprintf('SELECT * FROM database_table WHERE %s', implode(' AND ', $condition));
}