CODEWITHSUNDEEP
phpmysql
Arvind
Arvind

Reputation: 19

Search query not working

I have coded a mysql query for multiple fields, and result should match all the fields. Below is my code, 

SELECT e.es_id, e.es_sex, e.service_type, e.working_name,  d.es_age, d.es_city, d.es_regional_city
FROM escorts AS e 
INNER JOIN escorts_details AS d ON e.es_id = d.es_id 
WHERE  es_sex LIKE '%" . $es_sex . "%' 
AND category LIKE '%" . $category  ."%' 
AND es_city LIKE '%" . $es_city  ."%' 
AND es_regional_city LIKE '%" . $es_regional_city  ."%'";

and when I m executing this code after filling all the fields..

SELECT e.es_id, e.es_sex, e.service_type, e.working_name, d.es_age, d.es_city, d.es_regional_city
FROM escorts AS e 
INNER JOIN escorts_details AS d 
ON e.es_id = d.es_id 
WHERE es_sex LIKE '%male%' 
AND category LIKE '%waitresses%' 
AND es_city LIKE '%7%' 
AND es_regional_city LIKE '%%'

Its still showing female details, if you will see the excuted query, i have filled "male" (es_sex LIKE '%male%').

I don't know what is I m doing wrong.

Please help me.

Thanks.

Upvotes: 1

Views: 51

Answers (3)

Kathirmalan
Kathirmalan

Reputation: 66

I believe you would face syntax error near where condition.

try by select the table name along with '.' leaded with field name (table-name.field-name) like given below

SELECT e.es_id, e.es_sex, e.service_type, e.working_name, d.es_age, d.es_city, d.es_regional_city FROM escorts AS e INNER JOIN escorts_details AS d ON e.es_id = d.es_id WHERE e.es_sex LIKE '%male%' AND e.category LIKE '%waitresses%' AND e.es_city LIKE '%7%' AND e.es_regional_city LIKE '%%'

i have included escort table name as e.field-names, kindly refer the field name corresponding to the table name.

I hope you will get the correct answer. give me your feedback. happy coding.

Upvotes: 0

jobinrjohnson
jobinrjohnson

Reputation: 558

First you need to understand LIKE and = are not equal. LIKE in this case (with % on both sides) returns true when the key is found anywhere in the specifics column. That is %male% can come true for the value female also as the word male is present in it.

The % symbol represents that there can be any character or group of characters.

You have to use es_sex='male' in this case.

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

It's because % represents any string of characters. So in your case LIKE %male% actually could mean 'female', where 'fe' is being put in place of the first %. Just remove % and say es_sex = 'male', like this:

SELECT e.es_id, e.es_sex, e.service_type, e.working_name, d.es_age, d.es_city, d.es_regional_city
FROM escorts AS e 
INNER JOIN escorts_details AS d 
ON e.es_id = d.es_id 
WHERE es_sex ='male' 
AND category LIKE '%waitresses%' 
AND es_city LIKE '%7%' 
AND es_regional_city LIKE '%%'

Upvotes: 2

Related Questions