SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Qyery Help

  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Qyery Help

    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?

    Code:
    $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";

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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:
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •