PHP/MYSQL - computing differences between rows & sorting
shwaip
bluffin' with my muffin Icrontian
Ok - I'm working on adding new features to the stats page, and this required an update to the database/webpage. Mostly in that I'm now storing the stats for all members on the team, once daily and I moved from the JS tables to php. I'm trying to figure out the best way to get points per day for a subset of users (for example, uid 1 & 2 below) for the past two days and points over the last week.
I've included the following partial table as an example of how I've stored the stats:
The goal is to end up with a table somewhat like this (ranks based on total pts):
with entries for a set of users (1 & 2 in this example). I know how I'm generating that set, so just assume 1 & 2 now. I want it to be sorted by rank/total points (same thing). This is where my question comes in: can I do this with SQL? PHP? Is one way "better" than the other? If I can't do this because of the structure of the table (or some other restriction), say so. Nothing is really set in stone. My knowledge of php is fairly thin, my SQL knowledge is intermediate.
I've included the following partial table as an example of how I've stored the stats:
tblStats: +------------+------+---------+-------+-----+ | udate | rank | points | wus | uid | +------------+------+---------+-------+-----+ | 2006-01-05 | 1 | 2845061 | 14247 | 1 | | 2006-01-06 | 1 | 2852844 | 14270 | 1 | | 2006-01-07 | 1 | 2859771 | 14288 | 1 | | 2006-01-08 | 1 | 2867452 | 14307 | 1 | | 2006-01-09 | 1 | 2874687 | 14324 | 1 | | 2006-01-10 | 1 | 2882382 | 14346 | 1 | | 2006-01-11 | 1 | 2893726 | 14379 | 1 | | 2006-01-12 | 1 | 2900858 | 14400 | 1 | | 2006-01-05 | 3 | 2571553 | 41128 | 2 | | 2006-01-06 | 3 | 2572097 | 41131 | 2 | | 2006-01-07 | 3 | 2573054 | 41134 | 2 | | 2006-01-08 | 3 | 2573807 | 41138 | 2 | | 2006-01-09 | 3 | 2573807 | 41138 | 2 | | 2006-01-10 | 3 | 2574773 | 41142 | 2 | | 2006-01-11 | 3 | 2575462 | 41144 | 2 | | 2006-01-12 | 3 | 2576680 | 41149 | 2 | +------------+------+---------+-------+-----+
The goal is to end up with a table somewhat like this (ranks based on total pts):
+-----+------+-----------------+--------------+------------------+-------------+--------------+ | uid | rank | rank within set | points today | points yesterday | points week | points total | +-----+------+-----------------+--------------+------------------+-------------+--------------+
with entries for a set of users (1 & 2 in this example). I know how I'm generating that set, so just assume 1 & 2 now. I want it to be sorted by rank/total points (same thing). This is where my question comes in: can I do this with SQL? PHP? Is one way "better" than the other? If I can't do this because of the structure of the table (or some other restriction), say so. Nothing is really set in stone. My knowledge of php is fairly thin, my SQL knowledge is intermediate.
0
Comments
Look up SQL query syntax if you don't know it, it will show you the ORDER BY clause usage for this.
Here is a decent resource for the syntax. Everything you need to get your data back in a meaningful way can be found under the SELECT statement from there.
I guess my question amounts to: what's the best way to sort a 2-d array in php by a column (total points, for example). I think I need to do that because I can't think of a way to compute the "points today/yesterday/week" info with an SQL query