Sorting By Smallest Datediff

[NiallFH]

Hi all, first time poster here, but long time beginner using PHP and MySQL to put together sports statistics sites.

 

The one I'm working on just now has me befuddled. I've never really worked with dates in MySQL before, so this is new territory for me, but I've been reading into DATEDIFF to try to achieve the following:

 

I have two tables. One with a list of players and one with a list of goals scored. What I want to display is ONE result for the player who was the YOUNGEST scorer of a goal.

 

Within the tables, I have:

 

PlayerID | PlayerName | PlayerDOB

 

GoalID | GoalPlayerID | GoalMatchDate

 

So, I'm assuming we're looking at the DATEDIFF between the MIN(GoalMatchDate) and the PlayerDOB, while PlayerID = GoalPlayerID. That being said, I'm completely at a loss on how to start to query this, most specifically, in terms of limiting it to one query and how to order it by the difference in the dates.

 

Hopefully someone can put me on the right track or even be so kind as to post an example that I could follow.

Edited December 5, 2012 by NiallFH

[NiallFH]

For clarification, I am using MySQL 5.0.

[Barand]

try

SELECT GoalMatchDate, playerDOB, playerName,
DATEDIFF(GoalMatchDate, playerDOB) as age
FROM goal g
INNER JOIN player p ON g.GoalPlayerID = p.playerid
ORDER BY age
LIMIT 1;

[NiallFH]

try

SELECT GoalMatchDate, playerDOB, playerName,
DATEDIFF(GoalMatchDate, playerDOB) as age
FROM goal g
INNER JOIN player p ON g.GoalPlayerID = p.playerid
ORDER BY age
LIMIT 1;

 

Thanks for that Barand. This works a treat!

 

I have a related question however - is there a way to rearrange the format of the days into years, months and days?

[Muddy_Funster]

my suggestion would be

SELECT GoalMatchDate, playerDOB, playerName,
DATEDIFF(GoalMatchDate, playerDOB) as age,
DATE_FORMAT(FROM_DAYS(DATEDIFF(GoalMatchDate, playerDOB)),'%yYears, %mMonth(s) and %dDays') as displayAge
FROM goal g
INNER JOIN player p ON g.GoalPlayerID = p.playerid
ORDER BY age
LIMIT 1;

This way you get a formated field for display and a numerical field for accurate sorting.

[Barand]

I tried FROM_DAYS() but it doesn't give accurate results. In my test data below it should give 20 years 10 months, however

Match_date | PlayerDOB | Name | Days | FROM_DATE
2012-02-01 | 1991-04-01| Harry | 7611  | 0020-11-02

 

You would be better with a custom function

eg

function YMDdiff($date1, $date2)
{
   list($y1, $m1, $d1)  = sscanf($date1, '%d-%d-%d');
   list($y2, $m2, $d2)  = sscanf($date2, '%d-%d-%d');
   $diff = array('y'=>0, 'm'=>0, 'd'=>0);
   if ($d1 < $d2) {
    $d1 += date('t', mktime(0,0,0,$m2,1,$y2));
    $m2++;
   }
   $diff['d'] = $d1 - $d2;
   if ($m1 < $m2) {
    $m1 += 12;
    $y2++;
   }
   $diff['m'] = $m1 - $m2;
   $diff['y']= $y1 - $y2;
   return ($diff['y'] ? "{$diff['y']} years " : '') .
    ($diff['m'] ? "{$diff['m']} months " : '') .
    ($diff['d'] ? "{$diff['d']} days " : '');
}

echo YMDdiff('2012-02-01', '1991-04-01');  // --> 20 years 10 months