Mysql Select Code Doesn't work

I have my php/mysql SELECT code which doesn't return a result to what I have expected after execution of the code. Code: [Select]

$sql="SELECT *FROM advert 
       WHERE ad_Type = '%{$service}%' AND
     开发者_如何学Python       ad_Street_No like '%{$location}%' OR
            ad_Street_Name like '%{$location}%' OR
            ad_Suburb like '%{$location}%' OR
            ad_Postcode like '%{$location}%' OR
            ad_State like '%{$location}%'";

The user will search on location and the type of service available using parameters like street name,suburb, postcode and/or state in one textfield and then select a type of service from a dropdown menu. So for example : "Select records from table 'advert' where type of service = 'e.g. hauling,digging,' located at e.g. Cardiff NSW". " something like that.

I have 2 records right now on my database : 1. Service: Hauling, Location: Cardiff NSW 2. Service: Digging , Location: Cardiff NSW

Now the problem is:

you search for "Hauling" service in "Cardiff" , will return 2 records (#1 and #2). you search for "Digging" service in "Cardiff", will return 2 records (#1 and #2). you search for either of the services in " Cardiff NSW", will return 0 results.

I got no idea on how to fix this. Please help me guys. Thank you in advance. :( :( :(

Best regards

First off, you need some brackets:

$sql="SELECT *FROM advert 
       WHERE ad_Type like '%{$service}%' AND
           ( ad_Street_No like '%{$location}%' OR
             ad_Street_Name like '%{$location}%' OR
             ad_Suburb like '%{$location}%' OR
             ad_Postcode like '%{$location}%' OR
             ad_State like '%{$location}%'" );

That will fix your first case. But you also need to think about exactly what it is you're providing : for instance if you only provide a town then you don't want to include ad_Type in your query at all.

$sql = "SELECT * FROM advert
        WHERE ad_Type    like '%{$service}%' 
        AND (
          ad_Street_No   like '%{$location}%' 
          OR
          ad_Street_Name like '%{$location}%' 
          OR
          ad_Suburb      like '%{$location}%'
          OR
          ad_Postcode    like '%{$location}%' 
          OR
          ad_State       like '%{$location}%'
        )";