Qyery Help

[ma201dq]

Hi,

i am not sure how best to explain this, so apologies in advance if i sound stupid. My query below never seems to pick up the 'OR' conditions correctly. it works ok if the first attribute is set (Jobtitle) or both(jobtitle and jobtype) or all three (jobtitle, jobtype and Location_1), but returns all results (not matched) if second attribute is set on its own (jobtype) or third attribute on its own (Location_1). can somebody plz help?

$query="SELECT 
					consultant.idConsultant AS idCon,
					concat(consultant.FName, consultant.SName) AS Name,
					consultant.DateCreated,
					consultant.EmailMe,
					consultant.EmailCVIn,
					consultant.EmailDaxtra,
					adverts.idAdvert AS IDAdvert, 
					adverts.idBondAdapt, 
					adverts.DateCreated, 
					adverts.JobTitle,
					adverts.JobType,
					adverts.StartDate,
					adverts.JobDescription,
					adverts.SalaryFrom,
					adverts.SalaryTo,
					adverts.SalaryPer,
					adverts.SalaryBenefits,
					adverts.Location_1,
					adverts.Location_2,
					adverts.idConsultant,
					advertstatus.idAdvertStatus,
					advertstatus.AdvertStatus,
					advertstatus.AdvertRefresh,
					advertstatus.DateRefreshed AS AdStatusDateRefreshed,
					advertstatus.idAdvert AS AdvertStatusID
			FROM
					consultant, adverts, advertstatus
			WHERE
					advertstatus.AdvertStatus='Live'
			AND(			
					JobTitle LIKE '%".$Jobtitle_Escape."'
			OR
					JobType LIKE '%".$Jobtype_Escape."'
			OR
					Location_1 LIKE '%".$Locations_Escape."'
			)
			
			AND
					consultant.idConsultant=adverts.idConsultant
			AND
					adverts.idAdvert=advertstatus.idAdvert
								
			ORDER BY 
					(AdStatusDateRefreshed) DESC 
			LIMIT	
					$set_limit, $limit";

[IBazz]

Asking a question never made anyone stupid. Not asking questions can be a problem.

I am not sure I understand the question fully but, if you are having problems with the OR's when the JobTitle is not set (condition fails), then try putting the OR's outside the brackets. I see a logic in having them there but, maybe they would work outside.

(I off to check now on aliasing coz I was going to suggest that you alias your retrieved columns but I see you are using the table name in a similar way.)

hth

bazz

[ma201dq]

Asking a question never made anyone stupid. Not asking questions can be a problem.

I am not sure I understand the question fully but, if you are having problems with the OR's when the JobTitle is not set (condition fails), then try putting the OR's outside the brackets. I see a logic in having them there but, maybe they would work outside.

(I off to check now on aliasing coz I was going to suggest that you alias your retrieved columns but I see you are using the table name in a similar way.)

hth

bazz

ok, i'll try again: the OR's are only correctly returning a result if the OR's are set in order as they appear in my query. for example:
if Jobtitle is set then it returns a correct result,
if Jobtitle OR Jobtype is set then it also returns a correct result,
if Jobtitle OR Jobtype OR Location_1 is set it also returns correct results.

It fails only (returns all results):

if Jobtile is not set, but only Jobtype is
if Jobtitle is not set, but only Jobtype OR Location_1 is

[r937]

you're using a programming language to build your query, so you should really actually use the programming language to determine which variables have been set and which haven't, and adjust the SQL accordingly

in other words, the reason you're getting all rows is because of what happens when a variable isn't set -- you end up generating this into the query:

OR JobType LIKE '%'

which of course returns all rows

so if you simply removed those parts of the query whenever the variables aren't set, then bob's your uncle