#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    57
    Rep Power
    2

    MySQL HELP Needed (Joins, Unions, etc..)


    Ok, so i am working on a plugin for a site and i am having issues with getting all the correct data from the DB. (Recent Posts)

    The main issue that i am having is trying to get the Author's Username style as well as the LastPoster's username style. (So the author's username style is that his username is blue, while the last posters username style is his username being red)

    A few things are going bad:
    1) The member's user style are coming up false. What is happening is when the last poster post then instead of showing the correct user style for the last poster AND the author, it is showing the last poster's user style for the author as well.

    Example: The Authors US is Blue, and the Last Posters US is Red. Instead of display the Blue username for the Author, it will display a red username (AKA the last posters username color).

    2) The username colors aren't always showing up correctly. Since i am using a table to display the recent posts, EVERYTHING MUST BE THE SAME. But what is happening is that the Last Poster's Username might come up correctly for the first topic, but might come up a different color (or default color) for the next topic.

    Example: The LP Username color was RED for the first 3 recent post, then it will go to a gray color.

    What i tried:
    1) The default script. But the default script didn't query out the Author of the thread as well as his username style.

    2) Add in a reference to get the Author's Username and the Author's ID. However, problem 1 and 2 was happening.

    3) Added a new query to get just get the Author's username, Author's ID and the main stuff(Thread ID, Forum ID, etc...), and run it with the default query. Well the table is ONLY getting the data from the new query (and the default query is not functioning). So doing the opposite. It's displaying all the correct information BUT the last poster's username color is the same as the author's color.

    Example: The author's username is blue, so the last poster's username is blue. (It should be red).

    My thoughts:
    1) I am no sql master at all, so i assume that there's a way to get the data that i want. To me it seems like the query is only getting the userstyle for either the Author's username or the Last Poster username. But it should be getting both.

    I do not like sharing my code because i feel like it could ruin a good feature for the site, but **** it.



    -As you can see, the username style is changed based on the "t.uid = u.uid." (Shows the Author's username)

    -But if i replace it with "t.lastposteruid= u.uid." (Shows the Last Poster's Username)

    -Is there a way for me to get BOTH the username styles?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    please provide the results of SHOW CREATE TABLE statements for all three tables

    curious as to why you thought you had to use GROUP BY in your query, as you don`t have any aggregate functions like COUNT() or SUM()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    57
    Rep Power
    2
    *EDIT FOR PROTECTION
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    how do you identify the author of a thread versus the last poster? what is the firstpost column for? why do both thread and forum tables have last poster columns? can you see why i am confused?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    57
    Rep Power
    2
    Originally Posted by r937
    how do you identify the author of a thread versus the last poster? what is the firstpost column for? why do both thread and forum tables have last poster columns? can you see why i am confused?
    Yes i see, i am using MyBB forum software so some of those questions i can not answer of the top of my head.

    However, let me try too.
    1) To identify the author i use the username in the threads. So t.username

    2) To identify the last poster i use last poster. So t.lastposter

    3) The thread have last poster so it can see who posted last in the select topic, while forum have last poster to see who posted last in the whole entire forum rather than the thread

    4)
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    so you want the thread last poster and not the forum last poster, right?
    Code:
    SELECT t.tid
         , t.fid
         , t.subject
         , t.lastpost
         , t.lastposter
         , t.username
         , t.views
         , t.replies
         , t.lastposteruid
         , t.uid
         , f.name
         , author.usergroup        AS author_usergroup         
         , author.displaygroup     AS author_displaygroup      
         , lastposter.usergroup    AS lastposter_usergroup     
         , lastposter.displaygroup AS lastposter_displaygroup   
      FROM ".TABLE_PREFIX."threads AS t  
    INNER 
      JOIN ".TABLE_PREFIX."forums as f  
        ON f.fid = t.fid  
    INNER
      JOIN ".TABLE_PREFIX."users AS author   
        ON author.uid = t.uid
    INNER
      JOIN ".TABLE_PREFIX."users AS lastposter   
        ON lastposter.uid = t.lastposteruid    
     WHERE t.visible = '1'  
    ORDER 
        BY t.lastpost DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    57
    Rep Power
    2
    Originally Posted by r937
    so you want the thread last poster and not the forum last poster, right?
    Code:
    SELECT t.tid
         , t.fid
         , t.subject
         , t.lastpost
         , t.lastposter
         , t.username
         , t.views
         , t.replies
         , t.lastposteruid
         , t.uid
         , f.name
         , author.usergroup        AS author_usergroup         
         , author.displaygroup     AS author_displaygroup      
         , lastposter.usergroup    AS lastposter_usergroup     
         , lastposter.displaygroup AS lastposter_displaygroup   
      FROM ".TABLE_PREFIX."threads AS t  
    INNER 
      JOIN ".TABLE_PREFIX."forums as f  
        ON f.fid = t.fid  
    INNER
      JOIN ".TABLE_PREFIX."users AS author   
        ON author.uid = t.uid
    INNER
      JOIN ".TABLE_PREFIX."users AS lastposter   
        ON lastposter.uid = t.lastposteruid    
     WHERE t.visible = '1'  
    ORDER 
        BY t.lastpost DESC
    YOU ARE A LIFE SAVER. Thank you for the help and support.

IMN logo majestic logo threadwatch logo seochat tools logo