mySQL sort results (and other) written into a column

From: 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

« previous php.db (#24589) next »