Jump to content

Archived

This topic is now archived and is closed to further replies.

Wintergreen

Read from two tables in one query? (SOLVED)

Recommended Posts

I have to read a few things from two different tables and show the values mixed together, is it possible to do two table reads in one query, like "SELECT title FROM posts WHERE post_type = '0' ORDER BY DESC, SELECT avatar FROM users"?

Share this post


Link to post
Share on other sites
Awesome, works but now it seems to print everything three times instead of once.  Fix one thing break another :P 

Share this post


Link to post
Share on other sites
Any ideas why it would print it three times?

[code]$sql = "SELECT posts.title, posts.post_body, posts.poster_name, posts.post_time, posts.postid, posts.comment_number, users.avatar FROM posts, users WHERE posts.post_type = '0' ORDER BY posts.postid DESC";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
    $value = strtotime($row['post_time']);
    echo "\n" . "\t";
    echo "<img src=" . $row['avatar'] . " height=39 width=39 align=left><h1>" . $row['title'] . "<br /><span> by " . $row['poster_name'] . " on " . date("F j @ H:i", $value);
if ($_SESSION['user_level'] == 2 && $row['poster_name'] == $_SESSION['screenname']) {
echo " - <a href=edit.php?id=" . $row['postid'] . ">Edit</a>";
}
    echo '</span></h1>';
    echo '<p>' . nl2br($row['post_body']) . '<br />';
    echo "<span><a href=comment.php?id=" . $row['postid'] . ">Post a comment</a> - " . $row['comment_number'] . " comments</span></p>";
}
?>[/code]

Share this post


Link to post
Share on other sites
Ahh okay, I think it's printing three times because there are three users in the users table.
So, I'm relying on posts.poster_name to be able to get the correct users.avatar.  Can I specify this somehow in the query?

Share this post


Link to post
Share on other sites
This is more of a hack than a proper solution, but I'm not sure of the proper way to do this.

[code]SELECT DISTINCT(posts.title), posts.post_body, posts.poster_name, posts.post_time, posts.postid, posts.comment_number, users.avatar FROM posts, users WHERE posts.post_type = '0' ORDER BY posts.postid DESC[/code]

Share this post


Link to post
Share on other sites
Awesome, you got me looking in the generally correct direction, the code that was needed was :
[code]
$sql = "SELECT posts.title, posts.post_body, posts.poster_name, posts.post_time, posts.postid, posts.comment_number, users.avatar FROM posts JOIN users WHERE posts.post_type = '0' AND users.screenname = posts.poster_name ORDER BY posts.postid DESC";
[/code]

There we go, no triple posts or anything

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.