June 18th, 2013, 04:25 PM
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).
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?
June 18th, 2013, 06:12 PM
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()
June 18th, 2013, 06:21 PM
June 18th, 2013, 06:33 PM
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?
June 18th, 2013, 06:40 PM
Yes i see, i am using MyBB forum software so some of those questions i can not answer of the top of my head.
Originally Posted by r937
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
June 18th, 2013, 07:09 PM
so you want the thread last poster and not the forum last poster, right?
, 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
JOIN ".TABLE_PREFIX."forums as f
ON f.fid = t.fid
JOIN ".TABLE_PREFIX."users AS author
ON author.uid = t.uid
JOIN ".TABLE_PREFIX."users AS lastposter
ON lastposter.uid = t.lastposteruid
WHERE t.visible = '1'
BY t.lastpost DESC
June 18th, 2013, 07:24 PM
YOU ARE A LIFE SAVER. Thank you for the help and support.
Originally Posted by r937