Topic: SELECT, GROUP, ORDER BY, JOIN all in one query

[dreamwest]

Im trying to make a query to tell how many videos each user has uploaded, but the there are two tables and i need to sort by amount of videos descending

Heres what ive done

Code: [Select]

$query = "SELECT videos.*, signups.* ".
 "FROM videos, signups ".
"WHERE videos.user_id = signups.user_id limit 5 ";

$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['username']. " has ". $row['   ']." videos";
echo "<br />";
}

Heres my table structure

Table1:
signups (tabe name)

user_id  |  username

Table 2:
videos (Table name)

id |  user_id 


I proberly need a count or group in here but im uncertain

[dzelenika]

Code: [Select]

SELECT sugnups.username, Count(*)
FROM videos, signups
WHERE videos.user_id = signups.user_id
GROUP BY sugnups.username
ORDER BY 2 DESC

[dreamwest]

Thanks. The query works,  but im still having trouble with the while loop

Code: [Select]

while($row = mysql_fetch_array($result)){
echo $row['username']." has ".$row['COUNT(id)']." videos";
echo "<br />";
}
The username is grouped and echos but doesnt have any video count

[dzelenika]

Thanks. The query works,  but im still having trouble with the while loop

Code: [Select]

while($row = mysql_fetch_array($result)){
echo $row['username']." has ".$row['COUNT(id)']." videos";
echo "<br />";
}
The username is grouped and echos but doesnt have any video count

Put $row[1]

Instead of

$row['COUNT(id)']

[fenway]

Oh, please don't do that... don't rely on array offsets.

Use a column alias and refer to it that way.