Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving a list of values form a database but only displaying once

    This is a hypothetical, unfortunately I have no code to show just yet, but I'm trying to think of a solution of how to tackle the problem, rather than just get some code off you guys

    Lets say I have a table, Table A...

    Code:
    Table A:
    | A | NextID | UserID |
    +---+--------+--------+
    | 1 | 1      | 45     |
    | 2 | 1      | 32     |
    | 3 | 2      | 12     |
    | 4 | 2      | 76     |
    | 5 | 1      | 76     |
    What I now want to do is to list the User's ID and the number of times they are represented in this table, like so:

    UserID: 76, Listed: 2
    UserID: 45, Listed: 1

    And so on

    Any ideas? 'preciated
    Dave

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT UserID, COUNT(*) AS numtimes FROM tableA GROUP BY UserID
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hmmmpft... thanks for the reply, but this simply returns the 'resource id #47'

    PHP Code:
    echo(mysql_query("SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID")); 
    what does this mean? thanks again
    Dave

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by newmand2
    hmmmpft... thanks for the reply, but this simply returns the 'resource id #47'

    PHP Code:
    echo(mysql_query("SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID")); 
    what does this mean? thanks again
    it returns a resultset with a specific "resource id" (47 in your case). You can then acces the resultset (the records you actually want) by using the resource-id as a parameter to get an element of the resultset. Like this:
    PHP Code:
    $sql "SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID";
    $result mysql_query($sql) or die ('Queryproblem getting the counts');
    if (
    mysql_num_rows($result) >= 1){
        echo 
    'Count per user<ul>';
        while (
    $row mysql_fetch_assoc($result)){
            echo 
    '<li>UserID: '$row['UserID'] .', listed: ' $row['numtimes'] . '</li>';
        }
         echo 
    '</ul>';
    }else{
        echo 
    'No records found.';

    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts
    right - got that sorted... but how could I sort the rows by the frequency, turns out I can't have ORDER BY COUNT(*) in the same sql statement?
    Last edited by newmand2; 10-14-2004 at 08:13 PM.
    Dave

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You need to use an "ORDER BY" clause.
    try:

    $sql = "SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID ORDER BY numtimes DESC";

    which will sort them from the user with the highest count to the one ith the lowest count.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Regular Coder
    Join Date
    Jan 2004
    Location
    London, UK
    Posts
    234
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ahhh thanks so much, thats been bugging me all day

    I was using ORDER BY UserID
    Dave


  •  

    Posting Permissions

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