Mysql Select Code Doesn't work

Question

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

Ever considered using prepared statements (php.net/mysqli.prepare or php.net/pdo.prepare)? — KingCrunch
– KingCrunch, Commented Jul 21, 2011 at 13:01
did you realize that you are using = in the ad_Type, and not like? is it correct to use % in the ad_Type if it's not pattern? — Maxim Krizhanovsky
– Maxim Krizhanovsky, Commented Jul 21, 2011 at 13:03
it was a mistake on my part, I remove the (=) use like but still doesn't return a desirable result. — php_newbie
– php_newbie, Commented Jul 21, 2011 at 13:06
Probably just a typo in the question, but there's no space after your asterisk: SELECT *FROM should be SELECT * FROM. — Mike
– Mike, Commented Jul 21, 2011 at 13:16

Sam Holloway · Accepted Answer · 2011-07-21 14:10:04Z

3

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.

edited Jul 21, 2011 at 14:10

answered Jul 21, 2011 at 13:05

Sam Holloway

2,01915 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

php_newbie Over a year ago

:-), Woow! Great Sir Sam ,it works!.problem 1 and 2 are now solved. Another thing Sir is when I tried to search for "Cardiff NSW", no record found, but if only I will search on "cardiff" or "NSW", that will return something desirable.

php_newbie Over a year ago

there was a typo mistake on my part when i post this question, ad_Type = '%{$service}%' , which should be ad_Type like '%{$service}%' however after I applied your tip,it works. :-). Thanks a lot.

Fam Khan · Accepted Answer · 2011-07-21 13:33:26Z

0

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

answered Jul 21, 2011 at 13:33

Fam Khan

511 silver badge3 bronze badges

1 Comment

php_newbie Over a year ago

thanks sir fam khan. it works, but how can i solve problem #3 - "you search for either of the services in "Cardiff NSW", will return 0 results". ?

Collectives™ on Stack Overflow

Mysql Select Code Doesn't work

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related