SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting how many files a user has uploaded

    Hi Guys,

    i am having trouble with the count query in php, i have a table:

    uploaded_files and it has:

    PHP Code:
    CREATE TABLE `uploaded_files` (
      `
    idint(10NOT NULL auto_increment,
      `
    poster_namevarchar(50NOT NULL,
      `
    file_namevarchar(100NOT NULL,
      `
    file_sizevarchar(20NOT NULL
    i was wanting to count which of the users had uploaded the most files for a stats page e.g

    Name Number of files
    graham23s 14

    this is what i have so far:

    PHP Code:
         $query1 "SELECT file_name, COUNT(poster_name) FROM products GROUP BY file_name"
         
    $result1 mysql_query($query1); 
    im probably way off lol

    any help would be appreciated

    Graham

  2. #2
    SitePoint Addict CommanderZ's Avatar
    Join Date
    Apr 2006
    Location
    Czech Republic
    Posts
    236
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This way

    Code:
    "SELECT poster_name, COUNT(poster_name) FROM products GROUP BY poster_name" ORDER BY COUNT(poster_name);
    It will return sorted list of posters and counts of their uploads

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You want to collapse all the rows with the same poster_name value into one, so you GROUP BY that column.

    Code:
    SELECT poster_name, COUNT(*) FROM uploaded_files GROUP BY poster_name
    There's a dedicated MySQL forum here on SitePoint if you need help with queries

  4. #4
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    That's perfect i have while looped the users out, but i'm not sure how to assign the number of files uploaded in number to each user, would i need another query inside the while loop?

    cheers guys

    Graham

    my code so far:

    PHP Code:
         $query1 "SELECT `poster_name`, COUNT(*) FROM `uploaded_files` GROUP BY `poster_name` LIMIT 10"
         
    $result1 mysql_query($query1);
         
         echo 
    '<table width="60%" border="1" cellpadding="2" cellspacing="0" />
               <tr>
               <th bgcolor="#004E98" colspan="2"><font color="#ffffff">Top 10 Uploaders</font></th>
               </tr>
               <td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>Uploader</b></font></td><td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b># Files Uploaded Uploaded</b></font></td>
               <tr>'
    ;          
                 
         while (
    $row mysql_fetch_array($result1)) {
         
         
    $top_10_names $row['poster_name'];
         
         echo 
    '<td>'.$top_10_names.'</td><td></td></tr><tr>';
         
         }
        
         echo 
    "</table><br />"

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No

    PHP Code:
        $query1 "SELECT `poster_name`, COUNT(*) AS `count` FROM `uploaded_files` GROUP BY `poster_name` ORDER BY `count` DESC LIMIT 10"
        
    $result1 mysql_query($query1);
         
         echo 
    '<table width="60%" border="1" cellpadding="2" cellspacing="0" />
               <tr>
               <th bgcolor="#004E98" colspan="2"><font color="#ffffff">Top 10 Uploaders</font></th>
               </tr>
               <tr>
               <td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>Uploader</b></font></td><td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b># Files Uploaded Uploaded</b></font></td>
               </tr>'
    ;          
                 
         while (
    $row mysql_fetch_array($result1)) {
         
             
    $top_10_names $row['poster_name'];
             
    $count $row['count'];
         
             echo 
    '<tr><td>'.$top_10_names.'</td><td>' $count '</td></tr>';
         
         }
        
         echo 
    "</table><br />"

  6. #6
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant mate thanks a lot Dan

    Graham


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •