Jump to content

[SOLVED] MySQL: Max change between two dates and multiple usernames


idire

Recommended Posts

I have a table with the following rows:

 

date

username

xp

 

I have two dates, today and yesterday

 

What I want to locate is the username with the max xp change from the row today and the row yesterday so I can then retrieve via

 

while($r = mysql_fetch_array($sql))

 

the usernames in order of heighest exp change first.

 

Any ideas?

 

 

---

Thanks

 

Thanks for the advice, I tried this:

 

<?php
$sql = "SELECT t.username, t.overall_xp-y.overall_xp as change FROM `user` as y, `user` as t WHERE y.`date`='$yesterday' and t.`date`='$today' and y.username=t.username ORDER BY change DESC";
echo $sql;
$result = mysql_query($sql);

while($r = mysql_fetch_array($result_today)) {
echo $r["t.username"];
}
?>

 

<?php
SELECT t.username, t.overall_xp-y.overall_xp as change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY change DESC

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/i/d/i/idire/html/test2.php on line 10
?>

Error

 

SQL query: Documentation

 

SELECT t.username, t.overall_xp - y.overall_xp AS

CHANGE FROM `user` AS y, `user` AS t

WHERE y.`date` = '2008-09-12'

AND t.`date` = '2008-09-13'

AND y.username = t.username

ORDER BY CHANGE DESC

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change FROM `user` as y , `user` as t WHERE y . `date` = '2008-09-12' and t . `d' at line 1

well i sort of get you, but what date will be stored in date? yesturday, today?

 

could try something like..

 

SELECT max(xpField) FROM tableName WHERE dateField = 'someDate'

 

Never really used max before though, mentions a lot about grouping the results, could try using it...

<?php
$sql = "SELECT t.username, t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='$yesterday' and t.`date`='$today' and y.username=t.username ORDER BY exp_change DESC";

<?php
SELECT t.username, t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY exp_change DESC
Unknown column 't.overall_xp' in 'field list'
?>

Ah, i was using the wrong table, needed the tracking table, not the user table, as the field overall_xp is in the tracking table.

 

This seems to work, many thanks

 

If I wanted distinct user, I would need to change it to:

 

SELECT distinct(t.username), t.overall_xp-y.overall_xp as exp_change FROM `user` as y, `user` as t WHERE y.`date`='2008-09-12' and t.`date`='2008-09-13' and y.username=t.username ORDER BY exp_change DESC

 

I dont think I need to change that,  but thats how it would work?

 

 

EDIT: Its now working in my live page, thanks :)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.