mySQL sort results (and other) written into a column
| From: | Raphael Pirker | Date: | Mon, 06 Jan 2003 14:45:55 +0000 |
| Subject: | mySQL sort results (and other) written into a column | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-24589@lists.php.net to get a copy of this message | ||
Hi,
I know this is OT here, but as a regular reader I thought I'd just drop a line in here since I
couldn't find any decent news-community elsewhere.
Anyways, here goes my little question that has been troubling me for quite some time now:
I have a database that looks somewhat like this:
nick | pos | total_frags | total_points | level1_frags | level1_points | level2_frags |
level2_points | ...
now my question:
is there a way to calculate all the levelx_frags and levelx_points together dynamically? (one week
there are more levels, another week there are less) and write those results into the total_frags and
total_points columns? Right now I'm going through each of them individually with a php script,
but seems kinda painful to me, because I need to re-launch the php page everytime I collect new
stats (which can be up to 10x a day)
then, I'd like to do a "SELECT nick FROM users WHERE total_points > 0 ORDER BY
total_points DESC LIMIT 0,100" query and with the results I get, I want to write into the
"pos" column the position they have in that query. So for example if "m0rpheus"
is third in that query, he'd get a "UPDATE users SET pos=3 WHERE
nick='m0rpheus'". This, too, is done through php at the moment, and again, I wonder
if there's a mySQL way of doing that, because I'm accessing that mySQL interface not
through a web-browser but through iRC, which in turn makes it painful to launch a browser every
time. (and we want to be fully-automatic these days, don't we :)
Cheers and TIA
Raphael