Difficulty with join 3 tables in a query in php

Question

My database has 3 tables i wish to access in the select query but I cannot seem to get it to work. Selecting from 2 tables works fine so I know everything else is working apart from my code for selecting from 3 tables. My database has been created on PHPmyadmin

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

This is the code I have tried to use and shows the fields I wish to select:

 $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.topic_id,forum_topics.topic_title, forum_topics.topic_date FROM forum_replies JOIN forum_topics ON forum_replies.topic_id = forum_topics.topic_id JOIN users ON forum_replies.user_id = users.user_id "; $result = mysql_query($queryreply) or die (mysql_error()); $row = mysql_fetch_array($result);

Example in code would be appreciated. Thanks

cannot seem to get it to work... what is your expected result? also, stop using deprecated mysql_* functions; use PDO / MySQLi instead. last, make good use of table alias. — Raptor
– Raptor, Commented Apr 23, 2015 at 2:35
Yeah I understand it's deprecated but my university I'm studying at has told me to do so. I won't use in the future. I am at this moment just trying to echo out the $row to show all the replies to a topic on my forum website. With user details showing who posted them — phpdrivesmemad
– phpdrivesmemad, Commented Apr 23, 2015 at 2:39
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.topic_id,forum_topics.topic_title, forum_topics.topic_date FROM forum_' at line 3 — phpdrivesmemad
– phpdrivesmemad, Commented Apr 23, 2015 at 2:42
You are missing a comma , between users.username forum_topics.topic_id — Sean
– Sean, Commented Apr 23, 2015 at 2:43

Raptor · Accepted Answer · 2015-04-23 04:24:11Z

0

Use this query:

SELECT a.reply_text, a.reply_date, b.topic_title, c.username 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 // apply WHERE, ORDER, GROUP if needed

Apart from syntax errors, you should use LEFT JOIN and table alias in your case.

To show also the topic creator's username, you can adjust the query to the following:

SELECT 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 // apply WHERE, ORDER, GROUP if needed

edited Apr 23, 2015 at 4:24

answered Apr 23, 2015 at 2:43

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sean Over a year ago

ironically you have a syntax error in a,reply_text

phpdrivesmemad Over a year ago

Thanks for your help, may I ask why left join instead of join?

Raptor Over a year ago

You can refer to this: Difference between JOIN (default is INNER JOIN) and LEFT JOIN. In your case, topic replies are the key information you want to obtain. If you use INNER JOIN, if topic / user is missing somehow, the replies won't be shown.

phpdrivesmemad Over a year ago

Thanks I understand it now, I was wondering if you could help me with this new problem i'm facing. My forum page currently shows the forum title and all the replies to that specific forum (As well as the usernames of those who have replied) but i am also trying to show the username of the person who created the forum title, but I can't seem to figure it out.

phpdrivesmemad Over a year ago

Thank you so much, I'm new to coding so thank you for putting up with me, I really appreciate it.

|

Bla... · Accepted Answer · 2015-04-23 02:43:40Z

You miss the , after users.username..

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.topic_id,forum_topics.topic_title, forum_topics.topic_date

Collectives™ on Stack Overflow

Difficulty with join 3 tables in a query in php

2 Answers 2

9 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Linked

Related