Jump to content


Photo

Help with a query plz


  • Please log in to reply
2 replies to this topic

#1 2oMst

2oMst
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationFrance, Cannes

Posted 20 July 2003 - 05:58 PM

Ok i tried helping out a guy about a ranking system he tried to do on his site and couldn\'t quite get the query working.

What I want:
I have a forum_1_msg table with a user column.
I would like to have a ranking system of the most posts.
A post = 1 row in that table.

In theory:
Display every user once, using DISTINCT and count how many times this user has posted on the forum, and then sort it.

A few queries I tried:

mysql_query("SELECT DISTINCT user, count(user) AS nbr_posts FROM forum_1_msg ORDER BY nbr_posts DESC");mysql_query("SELECT GROUP_CONCAT(DISTINCT user) AS cur_user, GROUP_CONCAT(count(cur_user)) AS nbr_posts FROM forum_1_msg ORDER BY nbr_posts DESC");

I tried many other queries, the number 1 I thought would be the most accurate. After reading the doc on mysql.com I thought the number 2 query would be more appropriate but hey .... Both don\'t work.

Any help here ???

PS: I know this is not the best way for this to work but it\'s mainly for educational purpose on mysql_querying

Thank you
The technology is here guys ! use it and make us readers, life more simple.
[br][php] your php scripts !!! (even if just 1 line)
[br][code=auto:0] is not for php, php = [php]
[br]ENTER key is to go to the line or skip a line. Like when you\'re trying to make a point or talking about something else.
[br]Thank you ;)

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 27 July 2003 - 05:19 PM

SELECT user, COUNT(*) as nbr_posts FROM forum_1_msg
GROUP BY user ORDER BY nbr_posts DESC

hth
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 2oMst

2oMst
  • Members
  • Pip
  • Newbie
  • 6 posts
  • LocationFrance, Cannes

Posted 27 July 2003 - 05:40 PM

ahh !! yes, very nice

GROUP BY power :)

I\'ll experiment with group by, thank you.

ps: anyone know of any good sites showing all the syntax you can put into a mysql_query ... ?? (besides mysql.com)
The technology is here guys ! use it and make us readers, life more simple.
[br][php] your php scripts !!! (even if just 1 line)
[br][code=auto:0] is not for php, php = [php]
[br]ENTER key is to go to the line or skip a line. Like when you\'re trying to make a point or talking about something else.
[br]Thank you ;)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users