Jump to content


Photo

Read from two tables in one query? (SOLVED)


  • Please log in to reply
6 replies to this topic

#1 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 05:26 AM

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"?

#2 Moon-Man.net

Moon-Man.net
  • Members
  • PipPipPip
  • Advanced Member
  • 84 posts

Posted 05 September 2006 - 05:37 AM

SELECT posts.title, users.avatar FROM posts, users WHERE posts.post_type = '0' ORDER BY DESC

#3 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 05:50 AM

Awesome, works but now it seems to print everything three times instead of once.  Fix one thing break another :P 

#4 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 05:51 AM

Any ideas why it would print it three times?

$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>";
}
?>


#5 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 05:57 AM

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?

#6 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 05 September 2006 - 07:14 AM

This is more of a hack than a proper solution, but I'm not sure of the proper way to do this.

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


#7 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 01:11 PM

Awesome, you got me looking in the generally correct direction, the code that was needed was :
$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"; 

There we go, no triple posts or anything




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users