Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder che_anj's Avatar
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    157
    Thanks
    12
    Thanked 3 Times in 3 Posts

    anyone critics my query code

    anyone critics my query code or what do u think is the advantage of this query code. tnx
    PHP Code:
    SELECT DISTINCT e.sdatee.edatee.endtimee.interviewdatee.interviewtimerc.employer_idrct.contact_idrj.vacancy_ide.commentse.action_typerc.employer_namerj.jobtitlerct.confirstnamerct.conlastnamercd.firstnamercd.lastnamee.orasrcd.firstnamercd.lastname
    FROM rec_candidates 
    AS rcdrec_client AS rcrec_jobs AS rjevent_historyrec_contacts AS rcteventss AS e
    WHERE rcd
    .candidate_id event_history.applicant_id
    AND rc.employer_id event_history.client_id
    AND rj.vacancy_id event_history.job_id
    AND e.event_id event_history.event_id
    AND rct.contact_id event_history.contact_id
    AND (
    event_history.applicant_id '$candid'
    AND e.event_id '$events')
    OR (
    event_history.client_id '$empid' AND e.event_id '$events')
    OR (
    event_history.job_id '$jobid' AND e.event_id '$events'
    )GROUP BY event_history.event_id 

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Selecting DISTINCT with a GROUP BY on the same query? Wow. You probably should know that DISTINCT is probably useless, as it applies to the entire list of columns. And with the GROUP BY you can't depend on any column you select that isn't part of the GROUP BY or isn't a column function (sum(), avg(), count(), etc).

    But hey if you're happy with it then more power to ya.

  • #3
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    does the query do what you want it to? Those ANDs and ORs look a little odd...

    you don't seem to be escaping your variables, you probably want to:
    Code:
    event_history.applicant_id = '".mysql_real_escape_string($candid)."'
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #4
    New Coder
    Join Date
    Aug 2007
    Posts
    26
    Thanks
    0
    Thanked 4 Times in 4 Posts
    try
    PHP Code:
    SELECT 
    e
    .sdatee.edatee.endtimee.interviewdatee.interviewtimee.commentse.action_typee.oras,   
    rc.employer_idrc.employer_name,  
    rct.contact_id
    rj.vacancy_idrj.jobtitle
    rct.confirstnamerct.conlastname
    rcd.firstnamercd.lastnamercd.firstnamercd.lastname

    FROM 
    rec_candidates 
    AS rcd
    rec_client AS rc
    rec_jobs AS rj
    event_history
    rec_contacts AS rct
    eventss AS e

    WHERE 
    rcd
    .candidate_id event_history.applicant_id
    AND rc.employer_id event_history.client_id
    AND rj.vacancy_id event_history.job_id
    AND e.event_id event_history.event_id
    AND rct.contact_id event_history.contact_id
    AND event_history.applicant_id '$candid' 
    AND e.event_id '$events'
    AND (
            (
    event_history.client_id '$empid' AND e.event_id '$events'
        OR  (
    event_history.job_id '$jobid' AND e.event_id '$events')
    )    
    GROUP BY event_history.event_id 

  • #5
    Regular Coder che_anj's Avatar
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    157
    Thanks
    12
    Thanked 3 Times in 3 Posts
    this query display the type, startdate, enddate and comments, but the problem is, it displays the same types which is wrong. what should i do so that the rows will not display the same data..


    SELECT e.action_type, a.descriptions, e.sdate, e.comments,e.edate
    FROM actiontypes AS a, eventss AS e
    WHERE e.action_type = a.type_id AND e.sdate >= CURDATE( )
    OR edate >= CURDATE( ) AND e.updatedby = '$id' GROUP BY comments

    tnx

  • #6
    Regular Coder che_anj's Avatar
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    157
    Thanks
    12
    Thanked 3 Times in 3 Posts
    I query at actiontypes table because the description of eventcode can be found at actionptypes table.. thats why I the result will display all the list of descriptions with the same date, and comments.

  • #7
    New Coder
    Join Date
    Aug 2007
    Posts
    26
    Thanks
    0
    Thanked 4 Times in 4 Posts
    ???

    please give full query and place values instead of variables for me to better understand

    ie
    select * from table where field = '$data'

    should be

    select * from table where field = '0020'

  • #8
    Regular Coder che_anj's Avatar
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    157
    Thanks
    12
    Thanked 3 Times in 3 Posts
    ok...

    in table action types has the following datas.

    code description
    1 1st Interview
    2 2nd interview
    3 CV Sent
    4 Offer

    table events has the following fields

    code sdate edate comments updatedby
    1 08/31/2007 09/04/2007 Mr. M has interview 1
    2 09/01/2007 09/07/2007 Mr. M invited for 2nd 1
    4 09/09/2007 09/12/2007 Mr M was offered 1


    now I want to display this following datas in events table, but the result must be

    code sdate edate comments updatedby
    1st Interview 08/31/2007 09/04/2007 Mr. M has interview 1
    2nd Interview 09/01/2007 09/07/2007 Mr. M invited for 2nd 1
    Offer 09/09/2007 09/12/2007 Mr M was offered 1

    SELECT e.code, e.sdate, e.comments,e.edate,a.descriptions
    FROM eventss AS e, actiontypes as a
    WHERE e.sdate >= CURDATE( )
    OR edate >= CURDATE( ) AND e.updatedby = '1'

    thats its..

    tnx
    Last edited by che_anj; 09-03-2007 at 03:03 PM. Reason: cl

  • #9
    New Coder
    Join Date
    Aug 2007
    Posts
    26
    Thanks
    0
    Thanked 4 Times in 4 Posts
    PHP Code:
    SELECT a.descriptions AS codee.sdatee.edatee.commentse.updatedby
    FROM actiontypes a
    LEFT JOIN eventss e ON e
    .action_type a.type_id
    WHERE e
    .updatedby '2'
    AND (
        
    e.sdate >= '2007-08-15'
        
    OR e.edate >= CURDATE()


  • #10
    Regular Coder che_anj's Avatar
    Join Date
    Aug 2006
    Location
    Philippines
    Posts
    157
    Thanks
    12
    Thanked 3 Times in 3 Posts
    brilliant.. thanks Bahamut..

  • #11
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Way ayo!!!

    Che_anj next time u should ask harder question !!


  •  

    Posting Permissions

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