fumble Posted July 12, 2013 Share Posted July 12, 2013 Hi all. I have designed a forum ( with great help from this book - thanks Larry ) which seems to be working well. One thing still bugs me though. I have a table for forum threads which contains among others, the following two colums: thread_originator_id and last_reply_poster_name. Ideally I would like to have both columns contain each forum user's ID ( numeric ). However, I can't write the Query ( believe me, I tried - for days! ) which returns both users names ( using a join between two tables - users and threads_forum ). CONCATing one user is easy, but two ?? As a temp fix, I turned the last_reply_poster_name column into a varchar column and stored the person's name, which still gives me sleepless nights - horrible programming! My code is like this. $q3 = "SELECT CONCAT(first_name,' ',last_name) AS thread_starter_name, thread_title, thread_main_forum_id, DATE_FORMAT(date_thread_started, '%a, %e %b %y, %H:%i') AS date_thread_started, DATE_FORMAT(time_last_reply, '%a, %e %b %y, %H:%i ') AS time_last_reply, num_replies, thread_originator_id, last_reply_member_id, last_reply_poster_name, last_reply_post_id FROM threads_forum, users WHERE forum_sections_id = '$sid' AND group_id = '$gid' AND user_id = thread_originator_id ORDER BY last_reply_post_id DESC "; So, I would like to be able to write a query which CONCATS both names using the two user IDs - thread_originator_id and last_post_reply_user_id. ( There are other ways in which to solve the problem - redesigning the tables for one, but I'm interested in a properly written query to the above problem. ) Many thanks in advance. Link to comment Share on other sites More sharing options...
Antonio Conte Posted July 12, 2013 Share Posted July 12, 2013 What you need to do is joining the tables several times. I'll illustrate: SELECT threads.*, originator.user_id AS originator_id, originator.name AS originator_name, last_poster.user_id AS last_id, last_poster.name AS last_name FROM threads_forum AS threads INNER JOIN users AS orginator ON (threads.thread_originator_id = orginator.user_id) INNER JOIN users AS last_poster ON (threads.last_reply_member_id = last_poster.user_id) I might have used wrong column names, but that's the logic you have to use. Notice the aliasing on the joins. That's the key on how to solve such problems. 1 Link to comment Share on other sites More sharing options...
fumble Posted July 12, 2013 Author Share Posted July 12, 2013 Thank you Antonio. I will have to revisit my understanding - or lack thereof ! - of joins. I have managed to date using the AND expression to join tables, but your example is the way forward. I'll have a go at rewriting my query. Really appreciate the help. Link to comment Share on other sites More sharing options...
Recommended Posts