Wintergreen Posted September 5, 2006 Share Posted September 5, 2006 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"? Quote Link to comment Share on other sites More sharing options...
Moon-Man.net Posted September 5, 2006 Share Posted September 5, 2006 SELECT posts.title, users.avatar FROM posts, users WHERE posts.post_type = '0' ORDER BY DESC Quote Link to comment Share on other sites More sharing options...
Wintergreen Posted September 5, 2006 Author Share Posted September 5, 2006 Awesome, works but now it seems to print everything three times instead of once. Fix one thing break another :P Quote Link to comment Share on other sites More sharing options...
Wintergreen Posted September 5, 2006 Author Share Posted September 5, 2006 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] Quote Link to comment Share on other sites More sharing options...
Wintergreen Posted September 5, 2006 Author Share Posted September 5, 2006 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? Quote Link to comment Share on other sites More sharing options...
zq29 Posted September 5, 2006 Share Posted September 5, 2006 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] Quote Link to comment Share on other sites More sharing options...
Wintergreen Posted September 5, 2006 Author Share Posted September 5, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.