1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

determin if there is a record or not

Discussion in 'Programming' started by unitedlocalbands, Sep 13, 2007.

  1. #1
    I have two tables that i want to retireve data from. The first table contains information like (message, subject, datesent, touserid, sentuserid)

    The second table contians the default picture of the person who sent the message.

    In some cases the person who sent the message will not have a record in the "defaultpic table"

    The SQL goes as follows:

    
    <cfquery name="messages" datasource="#application.datasource#">
    SELECT USER_MAIL.MAILID, USER_MAIL.TOUSERID, USER_MAIL.TOGROUPID, USER_MAIL.SENTUSERID, USER_MAIL.DATE_TIME, USER_MAIL.SUBJECT, USER_MAIL.MESSAGES, USER_DEFAULTPIC.PICTUREURL, USER_DEFAULTPIC.GROUPID
    FROM USER_MAIL, USER_DEFAULTPIC
    WHERE USER_MAIL.TOUSERID = '#client.userid#' AND USER_MAIL.SENTUSERID = USER_DEFAULTPIC.GROUPID 
    </cfquery>
    
    Code (markup):
    The problem I'm having: if there is no record in the defaultpic table then this SQL statment will not output the message from the message table.

    How can I check to see if the user who sent the message has a defaultpic or not. And if they dont then use a <cfif> statement to use a different image

    I'm not even sure if this makes sense to me?

    Thanks for your help!
     
    unitedlocalbands, Sep 13, 2007 IP
  2. twalters84

    twalters84 Peon

    Messages:
    514
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Greetings,

    You would need to do something like this..

    <cfset myPic = ''>

    <cfif IsDefined("messages.PICTUREURL") AND #messages.PICTUREURL# NEQ "">

    <cfset myPic = '#messages.PICTUREURL#'>

    <cfelse>

    <cfset myPic = 'somePhotoURL.jpg'>

    </cfif>

    Have fun!

    Sincerely,
    Travis Walters
     
    twalters84, Sep 13, 2007 IP
  3. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #3
    Although It dosent create any errors, it still dosent work.

    What happens is that it will look at the defaultpic table and see if there is a record for the "sentuserid". But for whatever reason it thinks that no one has a record for a defaultpic in the defaultpic table. So it just sets "mypic" = "somePhotoURL.jpg"

    But on the other hand it will display all the messages from the message table. With out the defaultpic, and just the "somePhotoURL.jpg" in its place.

    If I figure it out I'll let you know.

    Thank you for your help.
     
    unitedlocalbands, Sep 13, 2007 IP
  4. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You could use a LEFT JOIN to return all messages, even if there is no matching record in the defaultpic table. Then use COALESCE() to return a different value if the default image is NULL.


    
    SELECT
     
    USER_MAIL.MAILID, 
    USER_MAIL.TOUSERID, 
    USER_MAIL.TOGROUPID, 
    USER_MAIL.SENTUSERID, 
    USER_MAIL.DATE_TIME, 
    USER_MAIL.SUBJECT, 
    USER_MAIL.MESSAGES, 
    COALESCE(USER_DEFAULTPIC.PICTUREURL, 'someOtherPicture.jpg') AS PictureURL
    USER_DEFAULTPIC.GROUPID
    
    FROM 
            USER_MAIL LEFT JOIN USER_DEFAULTPIC
            ON USER_MAIL.SENTUSERID = USER_DEFAULTPIC.GROUPID 
    
    WHERE 
               USER_MAIL.TOUSERID = '#client.userid#' 
    
    Code (markup):
     
    cfStarlight, Sep 13, 2007 IP
    unitedlocalbands likes this.
  5. unitedlocalbands

    unitedlocalbands Well-Known Member

    Messages:
    246
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    128
    #5
    This works exactly like it should.

    You have put an end to a two week long mission.

    Thank you very much for your help, once again you have opened another door for me in my quest to develope United Local Bands.com
     
    unitedlocalbands, Sep 13, 2007 IP
  6. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    You're welcome. Its also nice to read about and hear how your site is progressing :)
     
    cfStarlight, Sep 14, 2007 IP