MySql query and PHP coding

[klik]

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
                $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!

[mark_W]

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.

[klik]

Well, yeh I need the comma. That's what seperates the columns. I will add a dies command shortly and get back to you.

[klik]

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.

[aamonkey]

haven't tested it but it should work:

$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>'; 

[klik]

Thanks for the reply.

It's now working as I want it to