top-x-posters sql query

Danko · Bericht door **Danko** » 19 apr 2005, 15:32

deze code wordt gebruikt om users uit te lezen gesorteerd op hun berichten.

	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) 
		GROUP BY user_id, username
		ORDER BY user_posts DESC
		LIMIT $str_input";
	}

ik wil dit veranderen in users gesorteerd op hun gemiddelde berichten per dag. ik zal dus een array moeten aanmaken waar die waardes in worden gestopt van alle users, vervolgens sorteren en uitlezen maar hoe kan ik dit het beste aanpakken?

de reden dat ik wil sorteren op posts/dag van een bepaalde user is dat iedereen dan een gelijke kans heeft in de top-x-posters lijst te komen.

Luuk · Bericht door **Luuk** » 19 apr 2005, 15:49

Probeer eens zo:

Code: Selecteer alles

   {
      $sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
      FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p
      WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id)
      GROUP BY user_id, username
      ORDER BY ((" . time() . "-u.user_regdate)/86400/user_posts) DESC
      LIMIT $str_input";
   }

Danko · Bericht door **Danko** » 19 apr 2005, 16:34

yup dat was de goede insteek!

sorteren op posts per dag (per gebruiker)

Code: Selecteer alles

//
// MOD START - TOP 'X' USERS MOD - AbelaJohnB
//		This function will be used to acquire the "Top 'x'" members of your forum.
//		It can be called on any page that this file is included on.
//		You can specific the amount of members it returns by chaning the function input string.
//		Example:
//					top_posters('10', x, x) will return Top Ten Members
//					top_posters('5', x, x) will return Top Five Members
//
//		If you want to limit ADMIN and/or MOD from showing, use the following methods:
//
//					top_posters('10', 1, 1) Top Ten Members, Show both ADMIN & MOD
//					top_posters('10', 1, 0) Top Ten Members, Show both ADMIN, but not MOD
//					top_posters('10', 0, 1) Top Ten Members, Show both MOD, but not ADMIN
//					top_posters('10', 0, 0) Top Ten Members, Show neither ADMIN nor MOD
//

function top_posters($str_input, $show_admin, $show_mod)
{
	global $db, $phpEx, $theme;
	// Show All
	if ( ( $show_admin == '1' ) && ( $show_mod == '1' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, u.user_regdate, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) 
		GROUP BY user_id, username
		ORDER BY ((" . time() . "-u.user_regdate)/86400/user_posts) ASC 
		LIMIT $str_input";
	}
	// Show Admin But Not Moderators
	elseif ( ( $show_admin == '1' ) && ( $show_mod == '0' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, u.user_regdate, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . MOD . ") 
		GROUP BY user_id, username
		ORDER BY ((" . time() . "-u.user_regdate)/86400/user_posts) ASC 
		LIMIT $str_input";
	}
	// Show Moderators But Not Admins
	elseif ( ( $show_admin == '0' ) && ( $show_mod == '1' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, u.user_regdate, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . ADMIN . ") 
		GROUP BY user_id, username
		ORDER BY ((" . time() . "-u.user_regdate)/86400/user_posts) ASC 
		LIMIT $str_input";
	}
	// Show Neither Admins Nor Moderators
	else/// ( ( $show_admin == '0' ) && ( $show_mod == '0' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, u.user_regdate, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . ADMIN . ") AND (u.user_level != " . MOD . ") 
		GROUP BY user_id, username
		ORDER BY ((" . time() . "-u.user_regdate)/86400/user_posts) ASC 
		LIMIT $str_input";
	}
	//
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not query forum top poster information', '', __LINE__, __FILE__, $sql);
	}
	// $top_posters = array();
	while( $row = $db->sql_fetchrow($result) )
	{
				$style_color = '';
				if ( $row['user_level'] == ADMIN )
				{
					$row['username'] = '<b>' . $row['username'] . '</b>';
					$style_color = 'style="color:#' . $theme['fontcolor3'] . '"';
				}
				else if ( $row['user_level'] == MOD )
				{
					$row['username'] = '<b>' . $row['username'] . '</b>';
					$style_color = 'style="color:#' . $theme['fontcolor2'] . '"';
				}
				if (isset($top_posters)) 
				$top_posters .= ', '; 

		$regdate = $row['user_regdate'];
		$memberdays = max(1, round( ( time() - $regdate ) / 86400 ));
		$posts_per_day = round($row['user_posts'] / $memberdays, 2);
		
		$top_posters .= '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . "=" . $row['user_id']) . '"' . $style_color .'>' . $row['username'] . '</a>(' . $posts_per_day . ')';
	}
  return $top_posters;
}
// MOD END - TOP 'X' USERS MOD - AbelaJohnB

sorteren op totaal aantal posts per gebruiker

Code: Selecteer alles

//
// MOD START - TOP 'X' USERS MOD - AbelaJohnB
//		This function will be used to acquire the "Top 'x'" members of your forum.
//		It can be called on any page that this file is included on.
//		You can specific the amount of members it returns by chaning the function input string.
//		Example:
//					top_posters('10', x, x) will return Top Ten Members
//					top_posters('5', x, x) will return Top Five Members
//
//		If you want to limit ADMIN and/or MOD from showing, use the following methods:
//
//					top_posters('10', 1, 1) Top Ten Members, Show both ADMIN & MOD
//					top_posters('10', 1, 0) Top Ten Members, Show both ADMIN, but not MOD
//					top_posters('10', 0, 1) Top Ten Members, Show both MOD, but not ADMIN
//					top_posters('10', 0, 0) Top Ten Members, Show neither ADMIN nor MOD
//

function top_posters($str_input, $show_admin, $show_mod)
{
	global $db, $phpEx, $theme;
	// Show All
	if ( ( $show_admin == '1' ) && ( $show_mod == '1' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) 
		GROUP BY user_id, username
		ORDER BY user_posts DESC
		LIMIT $str_input";
	}
	// Show Admin But Not Moderators
	elseif ( ( $show_admin == '1' ) && ( $show_mod == '0' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . MOD . ") 
		GROUP BY user_id, username
		ORDER BY user_posts DESC
		LIMIT $str_input";
	}
	// Show Moderators But Not Admins
	elseif ( ( $show_admin == '0' ) && ( $show_mod == '1' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . ADMIN . ") 
		GROUP BY user_id, username
		ORDER BY user_posts DESC
		LIMIT $str_input";
	}
	// Show Neither Admins Nor Moderators
	else/// ( ( $show_admin == '0' ) && ( $show_mod == '0' ) )
	{
		$sql = "SELECT u.user_id, u.username, u.user_posts, u.user_level, count(u.user_id) as user_posts
		FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
		WHERE (u.user_id <> " . ANONYMOUS . ") AND (u.user_id = p.poster_id) AND (u.user_level != " . ADMIN . ") AND (u.user_level != " . MOD . ") 
		GROUP BY user_id, username
		ORDER BY user_posts DESC
		LIMIT $str_input";
	}
	//
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not query forum top poster information', '', __LINE__, __FILE__, $sql);
	}
	// $top_posters = array();
	while( $row = $db->sql_fetchrow($result) )
	{
				$style_color = '';
				if ( $row['user_level'] == ADMIN )
				{
					$row['username'] = '<b>' . $row['username'] . '</b>';
					$style_color = 'style="color:#' . $theme['fontcolor3'] . '"';
				}
				else if ( $row['user_level'] == MOD )
				{
					$row['username'] = '<b>' . $row['username'] . '</b>';
					$style_color = 'style="color:#' . $theme['fontcolor2'] . '"';
				}
		$top_posters .= '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . "=" . $row['user_id']) . '"' . $style_color .'>' . $row['username'] . '</a>(' . $row['user_posts'] . '), ';
	}
  return $top_posters;
}
// MOD END - TOP 'X' USERS MOD - AbelaJohnB

Danko · Bericht door **Danko** » 20 apr 2005, 07:24

is er een rekensommetje om de jaar/maand/dag uit een TimeStamp te lezen zonder daar een functie voor te gebruiken?

Luuk · Bericht door **Luuk** » 20 apr 2005, 20:48

Nee, ofja eigenlijk wel. Op 1-1-1970 om 0:00:00 is de tijdstamp 0, elke seconde later is 1tje meer.
Dus het gaat niet echt, moet dus met een functie.