Comparing Info

[Matrixkid]

Howzat goin?

Im running into some trouble with comparing some data.

 

Ill try my best to describe the problem, feel free to ask any further questions if necessary.

 

I have a table like this:

iName | iPoints | iAttacks | iDate
Bob   |   25    |   15    | 2008-08-12
Jim   |  20     |   13    | 2008-08-12
Bob   |   15    |   10    | 2008-08-11
Jim   |   18    |   10    | 2008-08-11

 

What I am trying to do is get a daily update on how many points and attacks each person made. From the example, on the 12th, you can do this by taking the 12th data and minus the 11th.

 

 

I have got the 12th information displaying total points with a while loop but I am having trouble taking the information from the 11th to minus it.

 

I am trying this:

					
//data from the 12th is in $result
while($info = mysql_fetch_array( $result )) {

//$selectDay is the 12th.
$basedate = strtotime($selectDay);
$date1 = strtotime("-1 day",$basedate);
$yDate = date("Y-m-d", $date1);

//get data from the 11th, with idName equal to current 12th name.
$yQuery = "SELECT * FROM stats WHERE (idDate='".$yDate."') AND (idName='".$info['idName'].")";
$yResult = mysql_query($yQuery);

while ($yinfo = mysql_fetch_array ( $yResult )) {

 

Its not giving me the results that I want.

Is there another method in doing this?

 

thanks for your help, it is very much appreciated.

[toplay]

I'm not sure I fully understand. From the data example you have, you should list how you want the final output to be (or look like).

 

This one query will total up points and attacks by user per date:

 

select iName, iDate, sum(iPoints) AS total_points, sum(iAttacks) as total_attacks

from stats

group by iName, iDate  # or could be iDate, iName or depending on how you want it

;

[Matrixkid]

for example, if i wanted to see jim's points for the 2008-08-12, i would take the points from the 2008-08-12 and minus the ones from 2008-08-11

 

the numbers are running totals, not the # themselves, i am trying to get the daily numbers from the running total.

 

so jims points would be 2, bobs would be 10 - for 2008-08-12