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

$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]

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

 

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

 

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.