[SOLVED] Mysql Queries Inside While Loops

[EZE]

How do you have a query inside a while loop? I have 2 tables, members and movies, and the movies table has a field/row called "uid" which is the user id of the user that submitted the movie. In the members table, i have the field "uid" as the primary key, and it is auto incremented. One other field in the members table, i have the field "username". I am listing a random movie, and I want to extract the "username" from the users table where the "uid" field on the users table is equal to the "uid" extracted from the movies table. I have my database connections set up, and here is my script:

$mvid = rand(1,$numovies_rows);
$randmovie=@mysql_query("SELECT * FROM movies WHERE mid=$mvid") or die(mysql_error());
while($randomovie=mysql_fetch_array($randmovie)){
	$mid = $randomovie['mid'];
	$name = $randomovie['name'];
	$views = $randomovie['views'];
	$uid = $randomovie['uid'];
	$size = $randomovie['size'];
	$date = $randomovie['date'];
	$user=@mysql_query("SELECT username FROM members WHERE uid=$uid") or die(mysql_error());
	echo "Random Movie: <a href=\"movie?mid=$mid\" onmouseover=\"return overlib('<center><b>By:</b> $user<br><b>Views:</b> $views<br><b>Size:</b> $size<br><b>Submitted On:</b> $date</center>', CAPTION, '<center>$name</center>');\" onmouseout=\"return nd();\">$name</a>";
}

($numovies_row is mysql_num_rows i defined earlier.) This script outputs everything except the usersname. Instead of username, it puts "Resource id #6", and there is only one user, "Admin", which should be the outputed username. I have read about the same problem in other topics, but I d not get the "JOIN" part of a mysql query. How would i implement JOIN in my query?

[Wildbug]

...
$user=@mysql_query("SELECT username FROM members WHERE uid=$uid") or die(mysql_error());
...

 

That line assigns a resource "handle" to the variable $user which you're printing which is why you get the "Resource id #6" or whatever.  You probably want something more like this:

 

$user = mysql_result(mysql_query("SELECT username FROM members WHERE uid=$uid"),0);

[btherl]

I wouldn't recommend that.. instead use:

 

$user_result = @mysql_query("SELECT username FROM members WHERE uid=$uid") or die(mysql_error());
$user = mysql_result($user_result, 0);

 

The reason is that Wildbug's code will not fail on errors.  Instead it will give you an incorrect $user as the result.

 

As for the join, it'll look like this:

 

$sql = "SELECT * FROM movies JOIN members ON (movies.uid = members.uid) WHERE mid=$mvid";
$randmovie=@mysql_query($sql) or die(mysql_error());

 

then username will be available without any additional queries.

[EZE]

Thank You Both! That worked and it outputs correctly now! I went to php.net for mysql_result and i get what it does now. Thank you again! (I'll click solved)