Using a LEFT JOIN I am retrieving the username associated with an ID to display a forum thread's author which is (at least now) simple enough. The tricky part is I'd like to do this with the last poster's ID and have it returned as a key with a name I specific myself. I think from previous posts I would use MySQL's AS command though it clearly does not seem to be in the same context that it was originally used. Right now the MySQL syntax returns the last post author's username as both names when it shouldn't be returning it as also the thread's original post author username.
So here is the MySQL I currently have...
Code: Select all
SELECT user_username, user_username_base, forum_thread_id, forum_thread_count_posts, forum_thread_count_replies, forum_thread_count_views, forum_thread_name FROM forum_threads LEFT JOIN user_accounts ON forum_thread_author_id AND forum_thread_author_id_last = user_idWHERE forum_thread_forum_id = '".$forum_id."' LIMIT 0 , 30So to make sense of it right now the SELECT user_username, user_username_base is using the table's LEFT JOIN which results in the forum_thread_author_id_last equivalent username and username_base of the user ID.[0] => webmaster
[user_username] => webmaster
[1] => webmaster
[user_username_base] => webmaster
[2] => 1
[forum_thread_id] => 1
[3] => 1
[forum_thread_count_posts] => 1
[4] => 0
[forum_thread_count_replies] => 0
[5] => 12
[forum_thread_count_views] => 12
[6] => First Test Thread!
[forum_thread_name] => First Test Thread!
)
Instead I'd like to have it output if possible something like...
...this is where I think MySQL's AS command may come in useful thought "AS" isn't exactly a search engine friendly term at a staggering length of two letters and I'm not seeing anything about it on tutorial sites like w3schools. I could also be wrong about AS and there may be something entirely different that I should use so I'm open to suggestions right now. I've also tried combining AS in a single LEFT JOIN of the ID's and as two separate LEFT JOIN's for the ID's without success.[user_username] => JAB Creations
[user_username_base] => JAB Creations
[user_username_last] => webmaster
[user_username_last] => webmaster