On Thu, Jul 11, 2002 at 11:30:16AM +0200, andy wrote:
>
> Table 1 user_table
> id
> points
>
> Table 2 reports
> report_id
> user_id
> rating
>
> The goal is to associate every user extra 50 points if he has a top 10
> report.
> Top 10 report means the 10 reports with the highest ranking. This value is
> always changing, thats why I did not include it to the other points.
> Now that we have the users with the additional points we could add them to
> the total points of the user and then find out the 5 users with the highest
> total points.
There are probably a couple ways to do this. This is the one that hits me
off the top of my head, assuming you're using a version of MySQL which
can't do subqueries.
Now, all of this is untested, so my query syntax may be off a tad, but
it'll give you the idea of where to go...
Do the following intermittently on a scheduled basis:
1) Make a third table. It'll hold temporary data.
user_id
totalscore
2) LOCK TABLES Table3 READ
3) DELETE FROM Table3 WHERE 1=1
4) INSERT INTO Table3 (user_id, totalscore) SELECT user_id, 20 AS sc
FROM Table2 ORDER BY rating LIMIT 10
5) INSERT INTO Table3 (user_id, totalscore) SELECT id, points
FROM Table1
6) UNLOCK TABLES
Now, whenever you want to generate the top 5 list use the following query
SELECT user_id, SUM(totalscore) AS thescore FROM Table3
GROUP BY user_id ORDER BY SUM(totalscore) LIMIT 5
If you can create subqueries, then you don't need the third table and can
write one nice query, something like this...
SELECT id, sc+points AS totalscore FROM Table1
LEFT JOIN (SELECT user_id, 20 AS sc FROM Table2 ORDER BY rating LIMIT 10)
ON (Table1.id = Table2.user_id)
ORDER BY sc+points LIMIT 5
--Dan
--
PHP classes that make web design easier
SQL Solution | Layout Solution | Form Solution
sqlsolution.info | layoutsolution.info | formsolution.info
T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y
4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
