Most uploaded users

hello osamasw
i am not understand what you want to do can you explain more about your needs

I need to create a section in my site contain most uploaded users which contain names of users and how many files uploaded, but I need only 20 users to show not all users.

$sql2 = "SELECT addedby FROM files WHERE addedby = '".$data['username']."' LIMIT 20";

should do it

This can be done without extra code you are using

<?php

$sql = "SELECT username ,addedby FROM users ORDER BY addedby DESC LIMIT 0, 20";
$run = mysql_query($sql);
if (mysql_num_rows($run) > 0 ) {
while ($data = mysql_fetch_assoc($run)) {
echo $data['username']." uploaded :  ". $data['addedby']." files";
echo "<br/>";
}
}
?>

Hey @OsaMasw I didn't noticed that you were using two tables and my above answer is using only one table
the answer by @TonyG_cyprus would help you to get 20 records :-)

the code provided by @TonyG_cyprus will limit the number of uploaded files to 20 :(
not limiting the users, look what is my mind, I need to find how many files uploaded by users then arrange them in desc order and get only first 20 results.
so

• I go through users tables to get username. $sql = "SELECT username FROM users"
• calculate number of files uploaded for each user. mysql_num_rows(mysql_query("SELECT addedby FROM files WHERE addedby = '".$data['username']."' "))
• arrange users according to uploaded files number in DESC order. (HERE IS THE PROBLEM)
• select first 20 users with most upload.

I've created a temp solution but really its not efficient can anyone simplify it ?

<?php
$sql = "SELECT username FROM users";
$run = mysql_query($sql);
if (mysql_num_rows($run) > 0 ) {
while ($data = mysql_fetch_assoc($run)) {
$sql2 = "SELECT addedby FROM files WHERE addedby = '".$data['username']."'";
$run2 = mysql_query($sql2);
if (mysql_num_rows($run2) > 0 ) {
$dat[] = array('username' => $data['username'],'files' => mysql_num_rows($run2));
}
}
foreach ($dat as $key => $row) {
    $files[$key]  = $row['files'];
    $users[$key]  = $row['username'];
}
array_multisort($files, SORT_DESC, $users, SORT_ASC, $dat);
$dat = array_slice($dat, 0, 20);
echo '<pre>';
print_r ($dat);
echo '</pre>';
}
?>

SELECT username 
FROM users u, files f 
WHERE f.addedby = u.username
GROUP BY username

Rest depends on what's in table files and what you need to retrieve of that table. Probably something like this:

SELECT username, COUNT(*) AS filecount 
FROM users u, files f 
WHERE f.addedby = u.username
GROUP BY username
ORDER BY filecount DESC
LIMIT 20

I knew pritaeas will save the day :P
thats solved my problem, here is the code I used.

<?php
echo '<ul>';
$sql = "SELECT username,avatar, COUNT(*) AS filecount
    FROM users u, files f
    WHERE f.addedby = u.username
    GROUP BY username
    ORDER BY filecount DESC
    LIMIT 20";
$run = mysql_query($sql);
if ( mysql_num_rows($run) > 0 ) {
while ($data = mysql_fetch_assoc($run)) {
echo "<li><img src='avatar/".$data["avatar"] . "' width='50' height='50' /><br/><a href='user-".$data["username"]."' >" . $data["username"] . "</a> : " . $data["filecount"]."</li>";
}
}
echo '</ul>';
?>

Thanks.