subquery results not aligning with main query

[deejay]

Having a problem with this query. It runs fine, but the results are presented wrong.

<?

$sql = "SELECT * FROM userlevels ORDER BY levelid DESC";
$query = mysql_query($sql);

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

$lid = $row['levelid'];

echo "<h2>".$row['level']."</h2>";
echo "<table border=0 cellpadding=5>";

echo "<tr> <td>Username</td> </tr>";

$sql2 = "SELECT * FROM users WHERE users.userlevel = $lid" or die("select: ".mysql_error());
$query2 = mysql_query($sql2);
while ($row2 = mysql_fetch_array($query2)) {

echo "<tr> <td>".$row2['username']."</td></tr>";

}

echo"</table>";
}

?>

What the query gives me:

Administrator
Jude

Facilitator
Joe
Sally

Member
(no result)

What the query SHOULD give me:

Administrator
Dee

Facilitator
Jude

Member
Joe
Sally

Each user level is showing up against the level above it, with my administrator just plain not showing up at all.

I'm sure it's got to be something simple, but darned if I can see it.

forgot my manners - any help would be greatly appreciated.

[Sekka]

Seems fine, but try,

$sql2 = "SELECT * FROM users WHERE users.userlevel = {$lid}";
$query2 = mysql_query($sql2) or die("select: ".mysql_error());

?

[deejay]

Thanks Sekka - no luck though, got exactly the same result.

If it makes any difference, the joined fields are both enum type, with the same options (0, 1, 2)

Any other ideas?

[deejay]

heh.. ok, apparently usng enum does make a difference.

I just changed both fields to Int and it now works perfectly.

If anyone can identify what the problem was with the enum option though, I'd appreciate it. I rather like the idea of having a limited range of options to enter into a field.

[coopster]

Show us your CREATE TABLE statement for both tables (don't need to see all the columns, just the ENUM column definitions)