[SOLVED] selecting results based on how often they appear

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
phase
Forum Newbie
Posts: 24
Joined: Sun Jul 18, 2004 10:47 am

[SOLVED] selecting results based on how often they appear

Post by phase »

hey, this may seem like an obvious question, but i am struggling thinking of a way to do it.

what i am trying to do is to use a select query to find out who has been the most active user on my site.

i have a mysql table which has a signup page, this table has the user id stored inside it.

so i want to select which user appears in that table the most.

Code: Select all

$results = $db->sql_query("select player_id from ".$prefix1."_csignups GROUP BY player_id LIMIT 1");
this is what i have at the moment, it groups all the player ids and can output them if i desire, but all i want to do is limit is to one result and display that one player id. i feel i need an ORDER clause, but i have tried order by player id but that wont work as it will just list the first grouped id numerically, i want to display the player who appears the most.

if anyone can help id be very grateful

bye for now.

phase
Last edited by phase on Tue Jun 21, 2005 7:06 pm, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

was chatting with D11 and he came up with this...it works a peach:

Code: Select all

SELECT COUNT(`field`) as `t`, `field` FROM `table` GROUP BY `field` ORDER BY `t` DESC
phase
Forum Newbie
Posts: 24
Joined: Sun Jul 18, 2004 10:47 am

Post by phase »

Thank you very much, works perfectly!
Post Reply