inner join

[tronicsmasta]

Hey guys,

 

I need to join these tables

 

comments (table)
date
username
comment
channel

with: 

users (table)
username
avatar

 

There are more to these tables but what I have listed is only what I need...

 

What I want to do is get the username date comments from the comments table where channel = 01 and the avatar from the users table that matches the username...

 

 

Now the what I am trying to query is

SELECT comments.username, comments.date, comments.comment, users.avatar 
FROM comments, users WHERE users.username = comments.username AND comments.channel = 01 ORDER BY date DESC

 

Now mysql returns 0 rows. but when i run this:

SELECT comments.username, comments.date, comments.comment, users.avatar 
FROM comments, users WHERE users.username = comments.username ORDER BY date DESC

 

I get 2 entries from my comments table because there are 2 comments entries with that username but all I am trying to get from the users table is the avatar...

 

any ideas how i can do this?

 

I also tried:

 

while ($row = mysql_fetch_array($result)) {

	$username = stripslashes($row['username']);
	$comment = stripslashes($row['comment']);
	$date = $row['date'];

	$sqlA = "SELECT avatar FROM users WHERE username = '$username'";
	$resultA = @mysql_query($sqlA,$connection);
	$rowA = mysql_fetch_array($resultA);

	$avatar = $rowA['avatar'];


	echo "<ul>";
	echo "<li>";
	echo "$username - <span id=\"comment_date\">$date</span><br/><br/>";
	echo "<table>
			<td>
				<tr width=\"100\"><img src=\"$avatar\" width=\"100\" height=\"100\"></tr>
				<tr width=\"430\">    $comment - avatar: $avatar</tr>
			</td>
		  </table>";
	echo "</li>";
	echo "</ul>";
			} //end list all comments

 

but the avatar shows up with an X and avatar: is blank... but when i run $sqlA by itself... no problems I get that avatar...

 

hope this is clear...

 

thank you!

 

 

[Barand]

use quotes round the '01'

 

SELECT comments.username, comments.date, comments.comment, users.avatar

FROM comments, users WHERE users.username = comments.username AND comments.channel = '01' ORDER BY date DESC