/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Top 3 Users Total Amount Donated

Copy linkTweet thisAlerts:
Jan 20.2010

I’m having a hard time figuring out how to grab the list the cumulative totals of our site’s top 3 donors by adding up each donation they’ve ever given and seeing who’s given the most over the years.

So far I’ve gotten it to output the Top 3 single donations. I need help taking it that 1 step farther into totaling them all up for each user and then spitting out the top user.

[CODE]// TOP 3 SINGLE DONATIONS
$sql = ”
SELECT ROUND(p.gross) as total, u.uid, u.uname, p.tid
FROM “.$prefix.”_site_usrs AS u
INNER JOIN “.$prefix.”_paypal_donation AS p ON (u.uid=p.tid)
ORDER BY ROUND(p.gross) DESC LIMIT 3”;
$result = $db->sql_query($sql);
echo “Top 3 Donation Amounts”;
while(list($total, $uid, $uname, $tid) = $db->sql_fetchrow($result)){
echo “”.$uname.” – “.$total.” <br />”;
}[/CODE]

to post a comment
PHP

5 Comments(s) ↴

Copy linkTweet thisAlerts:
@lgdevilauthorJan 20.2010 — Took me a little while but I finally figured it out. When using GROUP BY it will automatically combine everything by that. I thought it was pretty much the same as ORDER BY but it's sooooo not. I have a new respect for the power of GROUP BY. ? The reason I had to do SUM ROUND on the gross field is because it's a decimal field for money. For some reason I don't agree with it SUM(99.00) is more than SUM(100.00). It completely ignores the decimals and goes by the first 2 whole numbers. Only way around it is to use ROUND which will make everything whole numbers. I'm ok with displaying it as whole numbers because it's just for displaying the top donations. This has nothing to do with displaying an official receipt because that's done in a completely separate process prior (of course). So it works pretty well for it's need.

BTW, I had one heck of a time finding anything like this on the net to my surprise. Searched for hours on terms like: PHP Price Total, Adding Values, SUM Values, etc... Glad to finally put this one behind me.

[CODE]// TOP 3 DONORS
$sql = "
SELECT SUM(ROUND(p.gross)) as total, u.uid, u.uname, p.tid
FROM ".$prefix."_site_usrs AS u
INNER JOIN ".$prefix."_paypal_donation AS p ON (u.uid=p.tid)
GROUP BY p.gross
ORDER BY SUM(ROUND(p.gross)) DESC LIMIT 3";
$result = $db->sql_query($sql);
echo "Top 3 Donors";
while(list($total, $uid, $uname, $tid) = $db->sql_fetchrow($result)){
echo "".$uname." - ".$total." <br />";
}[/CODE]
Copy linkTweet thisAlerts:
@lgdevilauthorJan 31.2010 — OK I eventually figured out how to display my site's top 3, top 5, top 10, etc... and show the exact amount of money. This will total all money donated by each user and figure out who are the top donors. This assumes that you have a users table and donations table to join.

[code=php]// TOP DONORS
$sql = "
SELECT SUM(ROUND(p.gross,2)) as total, u.uid, u.uname, p.tid
FROM ".$prefix."_site_usrs AS u
INNER JOIN ".$prefix."_paypal_donation AS p ON (u.uid=p.tid)
GROUP BY p.tid
ORDER BY SUM(ROUND(p.gross)) DESC LIMIT 0,3";
$result = $db->sql_query($sql);
echo "Top 3 Donors<br /><br />";
while(list($total, $uid, $uname, $tid) = $db->sql_fetchrow($result)){
echo "".$uname." - ".$total." <br />";
}[/code]
Copy linkTweet thisAlerts:
@ehimeFeb 01.2010 — Congrats on figuring it out yourself,

I do this all the time waiting for replies,

make sure to mark your thread as resolved ?
Copy linkTweet thisAlerts:
@Wera90Aug 15.2011 — Thanks for good job)) you don't even realize how great your work is!!
Copy linkTweet thisAlerts:
@ehimeAug 15.2011 — stop bumping dead threads dip****.....
×

Success!

Help @lgdevil spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...