Query for Max PM's

[klik]

I need a query to give me the member name with the most PM's. It needs to then echo the member with the most PM's and the amount they have.

The database structure is as follows:

mid int(11) NOT NULL auto_increment,
userfrom varchar(20) NOT NULL default '',
userto varchar(20) NOT NULL default '',
subject varchar(32) NOT NULL default '',
message text NOT NULL,
date varchar(10) NOT NULL default '',
time varchar(5) NOT NULL default '',
opened int(1) NOT NULL default '0',
status int(1) NOT NULL default '0',
sort_date varchar(12) NOT NULL default '',

Thanks!

[leatherback]

So where are you stuck / what is the question / What have you tried?

J.

[klik]

Well, I'm stuck in the fact I don't know the SQL syntax to perform this query.

The question is what I mentioned before. What is the query I need to perform to extract the member with most PM's (name who appears most frequently in userto field) and also the number of PM's they have.

I haven't tried anything as I don't know the syntax to even get close to it, although I think I need to SELECT COUNT or something of this nature.

[leatherback]

Then perhaps you should have a little read in the manual first. It is a bit useless to just chew out code for you: The next thing you want to do on your site you will have to have build again.
The idea behind the forum is to support people to learn the language. If you want scripts built for you, most of us can be hired for that too.

-- Give a man a fish, and he will eat for a day. Learn him how to fish, and he will have food for the rest of his life --

J.

[klik]

I didn't just want the code. The query only and an explanation was what I was after. After the query is executed, I can create the PHP no problem to take the data and display it.
And yes, I have looked for similar queries and have also looked in the manual, but I was having difficulty so I decided to ask people on this board.

As for hiring for scripts, I don't think asking for assistance on one small query really needs someone to be hired. The script is already built, and I'm trying to generate a small stat panel in the admin section to display this data.

Anyway, thanks for your help.

You may delete this thread, if my posts are unjustified.

[leatherback]

Hey Klik,

No need to get all excited about it. It is just.. It is very easy to just ask for it, and then hear how it was done. You will not learn from that however. Considering that you have 100+ posts in this forum, I was pushing you a bit of self-help.

We all went through the initial scrambles (You can look at my first 50 posts, in which I really had no clue). But by trying, asking and working with people who do know you get further. The only thing we ask is that you try yourselve too.

Anyway..

What you need:

define a query:

$query = "Select * from SOMETABLE where SOMECONDITION";

In your case, you want to select a count of *, and you want to group by userto

Give that a little spin, and let us know how it goes.

You are aware of
http://dev.mysql.com/doc/

[klik]

Thank you for your help, and I'll be sure to give the manual a good read and try and generate the queries for my other stats by myself firstly, as i did with this one in fact.

I'll go give it a go.

Oh, and I very much agree with you on the point of being told something rather than having understood it and learnt sometihng new. Your help is appreciated, and I will do some research into mysql to try and wrap my head around it.

Cheers.

[klik]

Here is the code I currently have.

 <?php
				$result = mysql_query("SELECT 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>';
?>

This is outputting no name and the wrong number of PM's for that user. I'm not sure if I can use fetch_assoc on a count theory. Anyway, I'll do some reading and get back to you!

[leatherback]

Hi,

See, that was not too hard 😉

Add userto in the requested fields. Right now, your query is only retrieving the counted values.

so:

$result = mysql_query("SELECT userto, COUNT(*) as count FROM cms_privatemessage GROUP BY userto");

J.

[klik]

Thanks. 🙂

The ouput is now

admin - 2 PM's

admin is the correct name as they do have the most PM's, but I'm not sure where the 2 is coming from. I currently have 2 members so maybe it is that.

Anyway, it should be displaying 7(the number of PM's admin has) and not 2.

[partypete]

Hey klik,

Try this:

SELECT userto, COUNT(userto) AS total_messages
FROM pm_table
GROUP BY userto
ORDER BY total_messages DESC LIMIT 1

[klik]

Thank you. I have used the query along with some other code and got it to work.

Thanks again for the help.