Select fields using Foreign keys in relational table

Question

I am trying to display topic create (username) but have to go through a relation table to retrieve it. I have already created a query that displays the creator (username) of a reply, i believe i need a sub-query but have never used one before.

What i am basically trying to do is use the foreign keys to retrieve username, i hope below explains it:

Forum_replies.topic_id >>>>> forum_topics.topic_id and forum_topics.user_id >>>> users.user_id.

The Tables are as follows:

forum_replies

reply_id
topic_id
user_id
reply_text
reply date

forum_topics

topic_id
category_id
user_id
topic_title
topic_description
topic_date

users

user_id
username

Here is my code which currently displays forum_topics.Topic_title, forum_replies.reply_date, forum_replies.user_id (shows username of reply creator), forum_replies.reply_text.

    $queryreply = "SELECT forum_replies.reply_id, forum_replies.topic_id, forum_replies.user_id,
                       forum_replies.reply_text, forum_replies.reply_date, users.user_id, users.username, forum_topics.user_id,
                       forum_topics.topic_id,forum_topics.topic_title, forum_topics.topic_date
                       FROM forum_replies
                       LEFT JOIN forum_topics
                       ON forum_replies.topic_id = forum_topics.topic_id
                       LEFT JOIN users
                       ON forum_replies.user_id = users.user_id

                       ";


        $result = mysql_query($queryreply) or die (mysql_error());
        $row = mysql_fetch_array($result); 

          if(empty($row['topic_id'])){
            echo "No replies have been posted in this Topic, be the first to have your say using form below.";} ?>
        <table id="categorytable">

                <tr><td><?php echo '<b>'.$row['topic_title'].'</b>';?></b><br><br></td></tr>
                <tr><td><?php echo $row['reply_date'].' - '.$row['username'].' Replied with: ';?><br><br></td></tr>
                <tr><td><?php echo $row['reply_text'];?></td></tr>

I know mysql_* functions are deprecated but i have been asked to use them by uni staff. I would be greatful for any help. Thanks

Another stackoverflow user sent me the code and it's working perfectly now, thank you guys for your help though. I'm very grateful for your advice. Here is the code if your interested: $queryreply = "SELECT a.reply_id,a.reply_text, a.reply_date, b.topic_title, c.username AS reply_user, (SELECT username FROM users WHERE user_id=b.user_id) AS topic_creator FROM forum_replies a LEFT JOIN forum_topics b ON a.topic_id=b.topic_id LEFT JOIN users c ON a.user_id=c.user_id "; — phpdrivesmemad, Apr 23 '15 at 06:42

niyou · Answer 1 · 2015-04-23T06:00:56.537

0

add another JOIN:

SELECT forum_replies.reply_id, forum_replies.topic_id, forum_replies.user_id,
    forum_replies.reply_text, forum_replies.reply_date, users.user_id, users.username, forum_topics.user_id,
    forum_topics.topic_id,forum_topics.topic_title, forum_topics.topic_date, 
    users.user_id as topic_user_id, users.username as topic_username, 
FROM forum_replies
    LEFT JOIN forum_topics ON forum_replies.topic_id = forum_topics.topic_id
    LEFT JOIN users ON forum_replies.user_id = users.user_id
    LEFT JOIN users u2 ON forum_topics.user_id = u2.user_id

...and as mentioned in your other question: use aliases

edited Apr 23 '15 at 06:00

answered Apr 23 '15 at 05:54

niyou

875
1
11
23

Thanks for your help, i added another join using the aliases but when I try to echo out the username of the topic creator, it instead displays the reply creator. Any ideas what code I should be using? – phpdrivesmemad Apr 23 '15 at 06:29
of course you have to use `echo $row['topic_username'];` the name i used in the statement. – niyou Apr 23 '15 at 06:31
btw - you should edit your question and add what you have done now – niyou Apr 23 '15 at 06:33

score -1 · Answer 2 · answered Apr 23 '15 at 06:14

-1

You can join a table as many times as you need. So you can join table users ones more, but with different ON statement.

SELECT 
  forum_replies.reply_id, forum_replies.topic_id, forum_replies.user_id, forum_replies.reply_text,
  forum_replies.reply_date, users.user_id, users.username, forum_topics.user_id,
  forum_topics.topic_id,forum_topics.topic_title, forum_topics.topic_date, 
  topic_creators.username
FROM forum_replies 
LEFT JOIN forum_topics ON forum_replies.topic_id = forum_topics.topic_id 
LEFT JOIN users ON forum_replies.user_id = users.user_id 
LEFT JOIN users as topic_creators ON forum_topics.user_id = topic_creators.user_id;

answered Apr 23 '15 at 06:14

phoenix.mstu

164
9

won't work - you have two columns named username – niyou Apr 23 '15 at 06:25
I've tested it, it works great. In your answer you've got two columns named user_id. So do I, if to be clear) – phoenix.mstu Apr 23 '15 at 06:30
i have aliases for both columns. – niyou Apr 23 '15 at 06:35
Another stackoverflow user sent me the code and it's working perfectly now, thank you guys for your help though. I'm very grateful for your advice. – phpdrivesmemad Apr 23 '15 at 06:41

score -1 · Answer 3 · answered Apr 23 '15 at 06:44

Another stackoverflow user sent me the code and it's working perfectly now, thank you guys for your help though. I'm very grateful for your advice. Here is the code if your interested:

     $queryreply = "SELECT a.reply_id,a.reply_text, a.reply_date, b.topic_title, c.username AS reply_user, (SELECT username FROM users 
        WHERE user_id=b.user_id) AS topic_creator FROM forum_replies a
        LEFT JOIN forum_topics b ON a.topic_id=b.topic_id
        LEFT JOIN users c ON a.user_id=c.user_id

                       ";

Select fields using Foreign keys in relational table

3 Answers3

Linked