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

Question

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%');

Answer 1

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')