-1

hey guys how are you, I want to create a function ( or more than function ) to show most uploaded users in the site, so I've created simpple code to test this and its worked

<?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);
echo $data['username']." uploaded :  ". mysql_num_rows($run2)." files";
echo "<br/>";
}
}
?>

and the output was somthing like this
user1 uploaded : 5 files
user2 uploaded : 50 files
and so on .....
I need to minimize this huge sql since my site contain more than 3000 users so I need to show only most 20 users that have uploaded on site.

5
Contributors
9
Replies
47
Views
4 Years
Discussion Span
Last Post by OsaMasw
1

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

Edited by AARTI SHRIVAS

0

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.

0

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/>";
}
}
?>
0

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 :-)

0

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.
0

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>';
}
?>
1
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

Edited by pritaeas

Votes + Comments
you are awesome
0

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.

Edited by OsaMasw

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.