idire Posted September 12, 2008 Share Posted September 12, 2008 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 Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/ Share on other sites More sharing options...
idire Posted September 12, 2008 Author Share Posted September 12, 2008 i can retrieve the max of a value if i already have the value stored in the table, but not like this Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-639912 Share on other sites More sharing options...
sasa Posted September 12, 2008 Share Posted September 12, 2008 try SELECT t.name, t.xp-y.xp as change FROM table_name as y, table_name as t WHERE y.`date`=yesterday_date and t.`date`=today_date and y.name=t.name ORDER BY change DESC Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-639924 Share on other sites More sharing options...
idire Posted September 13, 2008 Author Share Posted September 13, 2008 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 ?> Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640373 Share on other sites More sharing options...
idire Posted September 13, 2008 Author Share Posted September 13, 2008 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 Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640377 Share on other sites More sharing options...
Adam Posted September 13, 2008 Share Posted September 13, 2008 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... Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640464 Share on other sites More sharing options...
idire Posted September 13, 2008 Author Share Posted September 13, 2008 i have data stored from today AND yesterday for LOTS of users What i want to do is find a list of users ordered in te max change between the data stored yesterday and the data stored from today Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640546 Share on other sites More sharing options...
DarkWater Posted September 13, 2008 Share Posted September 13, 2008 <?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"; Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640550 Share on other sites More sharing options...
idire Posted September 13, 2008 Author Share Posted September 13, 2008 <?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' ?> Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640560 Share on other sites More sharing options...
DarkWater Posted September 13, 2008 Share Posted September 13, 2008 Okay, is overall_xp the right column name? =/ Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640562 Share on other sites More sharing options...
idire Posted September 13, 2008 Author Share Posted September 13, 2008 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 Link to comment https://forums.phpfreaks.com/topic/123892-solved-mysql-max-change-between-two-dates-and-multiple-usernames/#findComment-640567 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.