CODEWITHSUNDEEP
phpmysqlsqljoin
Becky
Becky

Reputation: 2275

Using id's between two tables to output wanted data

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

Answers (2)

xQbert
xQbert

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

Davide Borsatto
Davide Borsatto

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

Related Questions