Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Threaded Emails

  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Threaded Emails

    Hello all,

    I want to make a messaging system where emails sent are linked, like the way google/yahoo do theirs. So i send an email and then you reply, i want the email i sent and the one you sent to be linked so that I am able to make them into a thread.

    Is this design correct? Any more i can do?

    http://img157.imageshack.us/my.php?i...bdesignxa1.png

    Thanks all

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's a convo?

    those tables look too similar to be separate tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's a convo?

    those tables look too similar to be separate tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Hi r937,

    Apologies for my terrible slang! Convo is supposed to be short for conversation.

    My thinkng behind this is that I have a table (message) which holds all the first messages and then another table (convo) which holds all the other messages which are linked to the message table by the msgID.

    Actually thinking about this again, how will i know the order of the conversation? I can compute it by the date field but is there another way i can do this naturally through a good db design?

    Thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure, there's another way, just have the reply link to the email it's replying to using its id

    and they can both be in the same table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Oh cool! Thanks for that. It will work like that but there is still a fair bit of coding to determine which message is linked to which reply.

    I mean if we picked any message (say 1) then i would look in the replyfield to see if it had any replies, if it did then go find that mesage and do the same again. No question this will work, but if the table gets really large then it will be really inefficient right?

    We could break it down, but the only way i thought was the above and it was a bit silly.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is a suggestion

    read the following article, and where is says "category" i want you to think email, and where it says "subcategory" i want you to think reply

    other than these names, it is exactly the same database structure, and you will be using the exact same queries

    --> Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    I am not ignoring you, that was a great article, looks like its exactly what i need. I have also been reading some of the resources after the article, they spoke about alternatives but I didn't understand them!

    Writing the SQL seems complicated even though I have an example, but I am learning about LEFT OUTER JOINS now so that hopefully it becomes easier.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't forget that when you try it out, and if you have problems, you can always come back here for help

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Table test

    | msgID | msg | reply ID |
    --------------------------
    | 1 | m | 0 |
    --------------------------
    | 2 | m | 1 |
    --------------------------
    | 3 | m | 2 |
    --------------------------
    | 4 | m | 3 |
    --------------------------
    | 5 | m | 0 |
    --------------------------
    | 6 | m | 5 |

    Code:
    SELECT tester.msgID as msg_ID,
    up1.msgID as up1_msgID,
    up2.msgID as up2_msgID,
    up3.msgID as up3_msgID
    
    from test as tester
    
    left outer
    join test as up1
    on up1.msgID = tester.replyID
    
    left outer
    join test as up2
    on up2.msgID = up1.replyID
    
    left outer
    join test as up3
    on up3.msgID = up2.replyID
    where tester.msgID = 2
    order by msg_ID
    After reading joins i attempted to customise the sql under the
    "The path to the root: the breadcrumb trail" section. It works great.

    But I want the above to able to search both up and down so that all messages
    retrieved no matter which msg the user clicks on.

    I would be very grateful for more help.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by compsci
    But I want the above to able to search both up and down so that all messages
    retrieved no matter which msg the user clicks on.
    could you explain how both up and down would be used together in a real situation?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    could you explain how both up and down would be used together in a real situation?
    It is most likely that my understanding is flawed.

    But for example say you have a messaging system: A B C D E - this is the series of the convo (letters are people and their email) - say that person C clicked on a old notification that an email was sent to them. I need to be able to get the past messages and the messages that followed after that - this is why i need an up and down search to get complete list.

    On my previous post, where i drew the table - if I used php to call the msgID 3 and all related messages - i need to get messages 1,2,3,4.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT columns FROM (
    query going up
    UNION ALL
    query going down
    ) AS both
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    The above looks good to me, but before i even reached the stage of using it, I got stuck in trying to do the statement for going down.

    Referring back to your great article - It says I have to specify the number of levels to go down (Under the section: "Displaying all categories and subcategories: site maps and navigation bars"), but it is variable - so how do I make the SQL understand this?

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Before I even try the concept I - my sql is crap! Is there a syntax error here?

    Code:
    SELECT msgID, msg, replyID FROM (
    
     SELECT tester.msgID as msg_ID,
    up1.msgID as up1_msgID,
    up2.msgID as up2_msgID,
    up3.msgID as up3_msgID
    
    from test as tester
    
    left outer
    join test as up1
    on up1.msgID = tester.replyID
    
    left outer
    join test as up2
    on up2.msgID = up1.replyID
    
    left outer
    join test as up3
    on up3.msgID = up2.replyID
    
    UNION ALL
    
    select root.msgID  as root_msgID
         , down1.msgID as down1_msgID
         , down2.msgID as down2_msgID
         , down3.msgID as down3_msgID
      from test as root
    left outer
      join test as down1
        on down1.replyID = root.msgID
    left outer
      join test as down2
        on down2.replyID = down1.msgID
    left outer
      join test as down3
        on down3.replyID = down2.msgID
     where root.replyID is null
    
    
    ) AS both

Posting Permissions

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