[SOLVED] joining

[tronicsmasta]

Hey guys,

 

I need to join these tables

 

 

comments (table)
date
username
comment
channel

with: 

users (table)
username
avatar

There is 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:

 

 

	(//NOTE: $start and $limit are part of my pagination)
$sql = "SELECT * FROM comments WHERE channel = '$channel' ORDER BY date DESC LIMIT $start, $limit;";

    //create list of results
$result = @mysql_query($sql,$connection) or die(mysql_error());	
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!

 

[darkfreaks]

why is there a @ before the query ???

 

try removing it.

[tronicsmasta]

I have put a second query in a while loop before... idk why this isnt working... but after removing the @ symbol that still leaves $avatar empty... hmmf...

 

what does that @ symbol do anyway? surpresses warnings right?

 

I tried cleaning it up a bit... here is the updated version:

 

	$display = "";
while ($row = mysql_fetch_array($result)) {

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

	$display .= "<ul>
					<li>$username - <span id=\"comment_date\">$date</span><br/><br/>
				<table>
					<td>
						<tr width=\"100\"><img src=\"";

	$sqlA = "SELECT * FROM users WHERE username = '$username'";
	$resultA = mysql_query($sqlA,$connection);
	while ($rowA = mysql_fetch_array($resultA)) {
		$avatar = $rowA['avatar'];
		$display .= "$avatar";
	}

	$display .= "\" width=\"100\" height=\"100\"></tr>";
	$display .= "<tr width=\"430\">    $comment</tr>
					</td>
		  		</table>
				</li>
			</ul>";
			} //end list all comments
	echo $display;

[tronicsmasta]

hmmm

 

I tried this:

SELECT comments.username, comments.date, comments.comment, users.avatar FROM comments LEFT JOIN users ON comments.username = users.username WHERE comments.channel = '01' ORDER BY comments.date DESC;

 

I get the right entry but avatar comes back null in phpMyAdmin... any ideas?

[tronicsmasta]

SELECT comments.username, comments.date, comments.comment, users.avatar 
FROM comments 
LEFT JOIN users 
ON users.username = comments.username 
WHERE comments.channel = '01' 
ORDER BY comments.date DESC;

 

haha error in my database... whoops... fixed!

 

Thanks to that guy who asked the question about joining earlier!

[Smackie]

SELECT comments.username, comments.date, comments.comment, users.avatar 
FROM comments 
LEFT JOIN users 
ON users.username = comments.username 
WHERE comments.channel = '01' 
ORDER BY comments.date DESC;

 

haha error in my database... whoops... fixed!

 

Thanks to that guy who asked the question about joining earlier!

 

Just a quick question for you tronicsmasta why use comments.username why not change it over and use like c.username saves on typing so much lol. but good to see you got things worked out