SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order Data by Counted Values

    Here's my problem. I have a table called prank with two fields, one called pid which will store the players ID and one called ip which stores the IP address of the person that submitted the players ID. I am setting this up as a basic voting system and counting the number of results for each pid submitted.

    pid | ip
    -----------
    1 | 1.2.3.4.5.6
    3 | 3.5.5.3.5.3
    1 | 5.3.6.6.4


    I have this nested inside a while loop which counts the number of entries for each pid, this works fine. pid 1 would return 2 and pid 3 would return a total of 1.

    PHP Code:
    <? 
                
                
                $query_count 
    "SELECT pid, COUNT(*) as total from prank WHERE pid = '$uid' GROUP BY pid";
                
    $result_count mysql_query($query_count) or die(mysql_error());
                
                if (
    $vote_count mysql_fetch_array($result_count)) {
                echo 
    $vote_count['total'];
                } else {
                echo 
    '0';
                } 
    ?>
    The problem I'm having is then taking the counted values and sorting them from highest to lowest and then inserting that into my database query so that when I use ORDER BY the player with the most votes will be first and so on.

    PHP Code:
    $sql "SELECT * FROM player"
    I know I need to use ORDER BY DESC, but I'm not sure how to pass the counted values that are in a while loop to my database query that is located in the header of my .php file.

    Hope this makes sense, thanks for your help
    Soccer Surfer | Top 10 Soccer Players

  2. #2
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Let the query do all the hard work for you

    Try

    Code:
    SELECT pid, Count( pid ) AS total
       FROM `prank` 
    GROUP BY pid
       ORDER BY total DESC
    So were asking the query to group all the same pid's together, count how many there are of each and output the pid reference and total in each group. then to order those records by the total value largest first.

    then all you need to do is read out the values in the array.

    PHP Code:
    $query_count = @mysql_query("
               SELECT pid, Count( pid ) AS total
                 FROM `prank` 
               GROUP BY pid
                 ORDER BY total DESC 
               "
    ); 
    if(!
    $query_count){ 
               
    //your error code here
    }
     
    while (
    $sql mysql_fetch_array($query_count)){
          echo 
    'User ID ' $sql['pid'] . ' - ' $sql['total'] . ' votes<br />';

    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  3. #3
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mandes View Post
    Let the query do all the hard work for you
    Thanks, I got your code to work on my test page, but I'm having trouble using it with my already existing sql query that joins all my tables together.

    My code is

    PHP Code:
    $sql "SELECT * FROM player 
    LEFT JOIN national on player.national_id = national.n_id
    LEFT JOIN club on player.club_id = club.c_id 
    LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id"

    Is there a way I can put your code into it so I have one query that LEFT JOINS all my tables and counts the pid so it can ORDER BY total DESC
    PHP Code:
    $query_count = @mysql_query("
               SELECT pid, Count( pid ) AS total
                 FROM `prank` 
               GROUP BY pid
                 ORDER BY total DESC 
               "
    ); 
    Something like this? I don't think it'll work, but something along these lines

    PHP Code:
        $sql "SELECT pid, Count( pid ) AS total
                 FROM `prank` 
               GROUP BY pid
                 ORDER BY total DESC 
                SELECT * FROM player 
    LEFT JOIN national on player.national_id = national.n_id
    LEFT JOIN club on player.club_id = club.c_id 
    LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id"

    Soccer Surfer | Top 10 Soccer Players

  4. #4
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try

    PHP Code:
    $sql "SELECT pid, COUNT(pid) AS total, add your other used fields here 
                FROM player  
                 LEFT JOIN national ON player.national_id = national.n_id 
                 LEFT JOIN club ON player.club_id = club.c_id  
                 LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id
            GROUP BY pid
              ORDER BY total DESC 
    "


    You should never really use * in your SELECT statements so find out which fields you are using in the query and add those in the select statement
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  5. #5
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mandes View Post
    try

    PHP Code:
    $sql "SELECT pid, COUNT(pid) AS total, add your other used fields here 
                FROM player  
                 LEFT JOIN national ON player.national_id = national.n_id 
                 LEFT JOIN club ON player.club_id = club.c_id  
                 LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id
            GROUP BY pid
              ORDER BY total DESC 
    "


    You should never really use * in your SELECT statements so find out which fields you are using in the query and add those in the select statement
    Unknown column 'pid' in 'field list'
    I received this problem when I tried the code.
    Soccer Surfer | Top 10 Soccer Players

  6. #6
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As youve not posted you database layout I have no way to know what your fields are called, this is the problem when you dont post your real code at the start of the thread !!

    You'll need to change the instances of 'pid' for the real field name that your using.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  7. #7
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did post my real code, the table that pid is in is called prank, so I tried using

    SELECT pid, COUNT(pid) AS total,

    and

    SELECT prank.pid, COUNT(pid) AS total,

    both resulted in the same problem
    Soccer Surfer | Top 10 Soccer Players

  8. #8
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do appreciate your help Mandes

    I think my problem is, the pid field is in the prank table and everything else I need is from the player table. So I need to somehow tell it to get pid from prank and everything else I wanted from player.
    Soccer Surfer | Top 10 Soccer Players

  9. #9
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Exactly what data do you need and from what tables for this piece of code ?

    What information are you looking to tie in to the rating results
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  10. #10
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what my full query looks like

    PHP Code:
    $sql "SELECT player.p_id, player.national_id, player.club_id, player.player_first, player.player_last, player.month, player.year, player.day,
                national.national_name, club.club_name, player.player_position, profile_pic.profile_image, profile_pic.profile_thumb 
                FROM player 
                LEFT JOIN national on player.national_id = national.n_id
                LEFT JOIN club on player.club_id = club.c_id 
                LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id"

    This is what I have working now, I got rid of the * like you mentioned and just put in the fields I needed.

    I tried doing

    PHP Code:
    $sql "SELECT pid, COUNT(pid) AS total,
                player.p_id, player.national_id, player.club_id, player.player_first, player.player_last, player.month, player.year, player.day,
                national.national_name, club.club_name, player.player_position, profile_pic.profile_image, profile_pic.profile_thumb
                FROM player 
                LEFT JOIN national on player.national_id = national.n_id
                LEFT JOIN club on player.club_id = club.c_id 
                LEFT JOIN profile_pic ON player.p_id = profile_pic.proimg_id"

    I think the issue is that it's looking in the player table instead of the prank table.

    I tried adding LEFT JOIN player.p_id = prank.pid which have the same values, but that didn't solve the problem.
    Soccer Surfer | Top 10 Soccer Players

  11. #11
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try this

    PHP Code:
    $sql "SELECT pid, COUNT(pid) AS total,
                       player.p_id, player.national_id, player.club_id, 
                       player.player_first, player.player_last, player.month, 
                       player.year, player.day, national.national_name, 
                       club.club_name, player.player_position, 
                       profile_pic.profile_image, profile_pic.profile_thumb
                FROM player 
                LEFT JOIN prank 
                     ON prank.pid = player.p_id      
                LEFT JOIN national 
                     ON player.national_id = national.n_id
                LEFT JOIN club 
                     ON player.club_id = club.c_id 
                LEFT JOIN profile_pic 
                     ON player.p_id = profile_pic.proimg_id
                GROUP BY pid
                ORDER BY total DESC"


    Strikes me that your gathering an awfull lot of data just to give a ranking, are you sure that this particular query needs all that data ?
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  12. #12
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mandes View Post
    Try this
    This ended up working and I added this like you mentioned it worked great thanks!

    PHP Code:
    GROUP BY pid
           ORDER BY total DESC 
    The only issue I'm having now is that because I selected the pid table I think it's only displaying the results from that table (which are the players with votes) rather than displaying both the players with votes and the players with 0 votes which aren't in prank.pid field
    Soccer Surfer | Top 10 Soccer Players

  13. #13
    SitePoint Member Jako's Avatar
    Join Date
    Jun 2005
    Location
    Michigan
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually fixed it by changing GROUP BY to p_id

    Thanks for all your help!
    Soccer Surfer | Top 10 Soccer Players


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
  •