SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist klik's Avatar
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    547
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql query and PHP coding

    Hey,

    I have a table in my database called cms_privatemessage. I am trying to display stats in my admin panel of my CMS, and would like to display the user who has the most PM's and also show the number which they have.

    The 'userto' column is where the recipients name is stored.


    This is what I have tried but it fails to work.

    PHP Code:
    <?php
                    $result 
    mysql_query("SELECT userto, COUNT(*) FROM cms_privatemessage GROUP BY userto");
                    
    $row mysql_fetch_assoc($result);
                    
    $pm_count mysql_num_rows($result);
                    
    $user $row[userto];
                    
                    echo 
    '<font color ="#FF3300">' $user ' - ' $pm_count ' PM\'s</font>';
    ?>
    Thanks!

  2. #2
    SitePoint Wizard mark_W's Avatar
    Join Date
    Mar 2004
    Location
    West Midlands, United Kingdom
    Posts
    2,631
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you actually need that comma after userto? You could do with adding so Dies to your sql commadsn to see if they are the problem or not.

  3. #3
    SitePoint Evangelist klik's Avatar
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    547
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, yeh I need the comma. That's what seperates the columns. I will add a dies command shortly and get back to you.

  4. #4
    SitePoint Evangelist klik's Avatar
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    547
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No error. So the query seems to be working. Whether or not it is the correct query for my task is really the answer I'm seeking.

  5. #5
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haven't tested it but it should work:

    PHP Code:
    $query "SELECT userto, COUNT(*) as pm_count FROM cms_privatemessage GROUP BY userto ORDER BY pm_count DESC LIMIT 1";

    $result mysql_query($query) or die (mysql_error());

    $row mysql_fetch_row($result);

    echo 
    '<font color ="#FF3300">' .$row['userto'] . ' - ' .$row['pm_count'] .' PM\'s</font>'

  6. #6
    SitePoint Evangelist klik's Avatar
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    547
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply.

    It's now working as I want it to


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
  •