Reputation: 2275
I have two database tables I am trying to have work together. They are called users and forun_topics
In forum_topics I have a column called topic_creator. This will always be an integer.
In users I have a column called id.
The id from the users table is what the topic_creator integer is.
So What I am trying to accomplish is to associate the topic_creator with the id in the users table and then with that find another column I have in my users table called username.
So to make this very simple I am trying to output the user name from the person who made a post.
As of now this is my query I have that displays the topic_creator, which I can figure out how to output everything once the SQL is working, but I can't figure out how to add another database table to this query and then find the username field. The largest part I am in question is how to get the username field after.
$query2 = mysqli_query($con,"SELECT t.*, c.id AS cid FROM forum_topics AS t, forum_categories AS c ORDER BY topic_reply_date DESC LIMIT 3")
How can I do this?
UPDATE to show more code
$query2 = mysqli_query($con,"SELECT t.*, c.id AS cid
FROM forum_topics AS t
INNER JOIN forum_categories AS c
on t.categories.ID = c.ID
INNER JOIN users u
on u.id = t.topic_creator
ORDER BY topic_reply_date DESC LIMIT 3")
or die ("Query2 failed: %s\n".($query2->error));
$numrows2 = mysqli_num_rows($query2);
if($numrows2 > 0){
$topics .= "<table class='top_posts_table'>";
//Change link once discussion page is made
$topics .= "<tr><th class='top_posts_th'>Topic Title</th><th class='top_posts_th'>Replies</th><th class='top_posts_th'>Views</th></tr>";
$topics .= "<tr><td colspan='3'><hr /></td></tr>";
while($row2 = mysqli_fetch_assoc($query2)){
$cid = $row2['cid'];
$tid = $row2['id'];
$title = $row2['topic_title'];
$views = $row2['topic_views'];
$date = $row2['topic_date'];
$date = fixDate($date);
$creator = $row2['username'];
$topics .= "<tr><td class='top_posts_td'><a href='forum_view_topic.php?cid=".$cid."&tid=".$tid."'>".$title."</a><br /><span class='post_info'>Posted
by: ".$creator."<br> on ".$date."</span></td><td class='top_posts_td'>0</td><td align='center'>".$views."</td></tr>";
$topics .= "<tr><td colspan='3'><hr /></td></tr>";
}
Upvotes: 0
Views: 93
Reputation: 35353
Something like... but without knowing how form_topics relates to forum_categories, this is likely wrong.
It also assumes that you want only records appearing in all 3 tables and when no match exists, records would be excluded.
SELECT t.*, c.id AS cid, u.*
FROM forum_topics AS t
INNER JOIN forum_categories AS c
ON t.category_id = c.id
INNER JOIN users u
on u.id = t.topic_Creator
ORDER BY topic_reply_date DESC LIMIT 3
Your current approach is doing a cross join between forum_topics and forum_categories. This is not likely what you really want.
Upvotes: 2
Reputation: 103
I'm not sure what your SQL level is, but this is something usually of basic level. I suggest before continuing with your project, stop for a second and go read something about it, at least to have a basic understanding of the topic. It's one of the most common operations, and having a grasp of how this works will always be better than some anwser on StackOverflow, especially because it's something you'll likely encounter a lot.
What you're looking for is something like this:
SELECT * FROM forum_topics
INNER JOIN users
ON forum_topics.topic_creator = users.id
ORDER BY forum_topics.topic_reply_date DESC
LIMIT 3
This can be improved in a number of ways, for instance giving aliases to table names and specifying the actual columns you want (where I left a *).
If I may, try to be consistent with naming. In your example, "topic_creator" could become "user_id", which in these situations is the norm. In general, where you have a 1:N relationship between two tables, try to name the foreign key something like "user_id" (associated to users.id), "topic_id" (topics.id) etc.
Upvotes: 1