Display only most recent item

[davidcriniti]

Hi,

 

I have some code to help me keep track of the programs that I write for athletes. It is basically a traffic light system, where if an athlete's program does not need to be updated for more than a week, the cell background is green, if it needs to be updated in less than a week, it turns amber, and if it is overdue, it turns red.

 

However, the problem is that when I upload a new program, that row appears in green, but the old program can be seen in red. Basically I want the code to only show the most recent program for each athlete. Not sure if I use Max? for this.

 

I'm looking for the most recent instance of athlete_id_prog in the programs_for_athletes table.

 

Here's the relevant code:

 

 

<?php

$sql = mysql_query("SELECT * FROM t_athletes, programs_for_athletes WHERE athlete_id = athlete_id_prog and next_prog_completed='No' ORDER BY $field $sort");


echo "<table border='1'  align='center' bordercolor='#000000' CELLPADDING=5 cellspacing='0' STYLE='font-size:13px'>";
echo "<tr bgcolor='#000000'> <td>*</td><td><H3><font color='white'>First name</font> $srt[1]</h3></td> <td><H3><font color='white'>Last name</font> $srt[2]</H3></td>  <td><H3><font color='white'>Sex</font> $srt[3]</H3></td>  <td><H3><font color='white'>Program link</font> $srt[4]</H3></td> <td><H3><font color='white'>Program Expiry Date</font> $srt[5]</H3></td></tr>";
// keeps getting the next row until there are no more to get

$row_counter = 1; //create row counter with default value 0

// Print out the contents of each row into a table


/*?>$bgColors = array('F'=>'#FF99FF', 'M'=>'#CCCCCC'); while ($row = mysql_fetch_array($sql))

{   
// Print out the contents of each row into a table   
echo "<tr bgcolor=\"{$bgColors[$row['sex']]}\">\n";    


	echo "</td><td>";
echo $row_counter++; 
echo "</td>";
echo "<td>{$row['firstname']}</td>\n";    
echo "<td>{$row['lastname']}</td>\n";   
echo "<td>{$row['sex']}</td>\n";    
echo "<td>{$row['notes']}</td>\n";   
echo "<td>{$row['note_end_date']}</td>\n";   
echo "</tr>\n";<?php */

while ($row = mysql_fetch_array($sql)){   
if(strtotime($row['prog_expiry_date']) > time()){
  // date hasn't expired yet, so we check the remaining days
  $diff = strtotime($row['prog_expiry_date']) - time();
  if($diff <=  (60*60*24*7) ){
     $bgColors = '#CC6600';
  }else{
     $bgColors = '#99FF99';
  }
}else{
  // date has expired
  $bgColors = '#FF3333';
}

echo "<tr bgcolor=\"{$bgColors}\">\n";
	echo "</td><td>";
echo $row_counter++; 
echo "</td>";
echo "<td>{$row['firstname']}</td>\n";    
echo "<td>{$row['lastname']}</td>\n";   
echo "<td>{$row['sex']}</td>\n";    
echo "<td>\n";   

echo "<a href='http://www.mytestwebsite.com/athlete_programs/" . $row["program_link"] . "' target='_blank'>" . $row["program_link"] . "</a>";

echo "</td>\n";   

echo "<td>{$row['prog_expiry_date']}</td>\n";   
echo "</tr>\n";


}
echo "</table>";


?>

 

Any advice is much appreciated.

[xyph]

This is more of an SQL question, and can be more easily answered with a database dump including some same data. I know what you're trying to accomplish, but I don't trust myself to give you a query that's untested. It generally just leads to confusion if I've coded it incorrect.

[davidcriniti]

Thanks,

 

I've included both tables as attachments and code as I'm not sure which is easier.

 

Here's the t_athletes one:

 

-- phpMyAdmin SQL Dump
-- version 2.11.11.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 18, 2011 at 08:57 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `tesolcla_mylongrun`
--

-- --------------------------------------------------------

--
-- Table structure for table `t_athletes`
--

CREATE TABLE IF NOT EXISTS `t_athletes` (
  `athlete_id` int(11) NOT NULL auto_increment,
  `approved` enum('Yes','No') NOT NULL default 'No',
  `date_started` date NOT NULL default '0000-00-00',
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `emailaddress` varchar(100) NOT NULL default '',
  `password` varchar(15) NOT NULL default '',
  `phone` varchar(20) NOT NULL default '',
  `sex` text NOT NULL,
  `dob` date NOT NULL default '0000-00-00',
  `height` varchar(30) NOT NULL default '',
  `weight` varchar(30) NOT NULL default '',
  `pulse` varchar(30) NOT NULL default '',
  `current_injuries` longtext NOT NULL,
  `previous_injuries` longtext NOT NULL,
  `goal1` text NOT NULL,
  `goaldate1` text NOT NULL,
  `goal2` text NOT NULL,
  `goaldate2` text NOT NULL,
  `goal3` varchar(255) NOT NULL default '',
  `goaldate3` varchar(255) NOT NULL default '',
  `current_training` longtext NOT NULL,
  `monday` varchar(255) NOT NULL default '',
  `tuesday` varchar(255) NOT NULL default '',
  `wednesday` varchar(255) NOT NULL default '',
  `thursday` varchar(255) NOT NULL default '',
  `friday` varchar(255) NOT NULL default '',
  `saturday` varchar(255) NOT NULL default '',
  `sunday` varchar(255) NOT NULL default '',
  `inclusions` longtext NOT NULL,
  `exclusions` longtext NOT NULL,
  `other` longtext NOT NULL,
  `notes` longtext NOT NULL,
  PRIMARY KEY  (`athlete_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;

--
-- Dumping data for table `t_athletes`
--

INSERT INTO `t_athletes` (`athlete_id`, `approved`, `date_started`, `firstname`, `lastname`, `emailaddress`, `password`, `phone`, `sex`, `dob`, `height`, `weight`, `pulse`, `current_injuries`, `previous_injuries`, `goal1`, `goaldate1`, `goal2`, `goaldate2`, `goal3`, `goaldate3`, `current_training`, `monday`, `tuesday`, `wednesday`, `thursday`, `friday`, `saturday`, `sunday`, `inclusions`, `exclusions`, `other`, `notes`) VALUES
(3, 'Yes', '2011-07-05', 'Belinda', 'Summerville', 'b_summerville@bigpond.com', 'secret', '', 'F', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'asdf'),
(4, 'Yes', '0000-00-00', 'Bernard', 'Lew', 'bernardlew@hotmail.com', 'secret', '', 'M', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'asdf'),
(32, 'Yes', '2011-08-09', 'Pej', 'Ziaei', 'Pej123@hotmail.com', 'secret', '', 'M', '1981-01-11', 'Na', 'Na', 'Na', 'None', 'None', 'Run half marathon in under two hours ', 'Sep 18', 'Run NY and Boston marathon', '2013', 'Duathlon and triathlon', '2012', 'I train 3 times a week\r\nLong run is 12 km', 'Rest', '5pm run', 'Cycle 20min', 'Rest', 'Rest', '12km run', 'Rest', '', 'I work 7 days a week so \r\nMon- Friday I train at 10 pm as I have kids\r\nAnd sat and sun I finish work at 2 so I can train a bit earlier', '', ''),
(29, 'Yes', '0000-00-00', 'Amanda', 'Smith', 'walter391@bigpond.com', 'secret', '', 'F', '0000-00-00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'Sutherland 1/2 20/08/11 Lake Mac 1/2 28/08/11 Bankstown 1/2 04/09/11 Blackmores 1/2 18/09/11'),
(30, 'Yes', '2011-07-15', 'Dave', 'Criniti', 'davidcriniti@gmail.com', 'secret', '', 'M', '1978-06-12', 'asdf', 'asdf', 'asdf', 'This''s a test.', 'test', 'test', '', '', '', '', '', 'test', '', '', '', '', '', '', '', '', '', '', '');

 

Here's the t_programs_for_athletes table:

 

Server: localhost   Database: tesolcla_mylongrun   Table: programs_for_athletes 
-- phpMyAdmin SQL Dump
-- version 2.11.11.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 18, 2011 at 09:00 AM
-- Server version: 4.1.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `tesolcla_mylongrun`
--

-- --------------------------------------------------------

--
-- Table structure for table `programs_for_athletes`
--

CREATE TABLE IF NOT EXISTS `programs_for_athletes` (
  `id` int(11) NOT NULL auto_increment,
  `athlete_id_prog` int(255) NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `program_link` text NOT NULL,
  `prog_start_date` date NOT NULL default '0000-00-00',
  `prog_expiry_date` date NOT NULL default '0000-00-00',
  `next_prog_completed` enum('Yes','No') NOT NULL default 'No',
  `program_message` longtext NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=55 ;

--
-- Dumping data for table `programs_for_athletes`
--

INSERT INTO `programs_for_athletes` (`id`, `athlete_id_prog`, `date`, `program_link`, `prog_start_date`, `prog_expiry_date`, `next_prog_completed`, `program_message`) VALUES
(44, 4, '2011-07-18 22:21:00', 'Bernard_Training_Log_2011071.xls', '0000-00-00', '2011-08-07', 'No', 'Bernard, I hope your injury is behind you now. We are trying to build back up the km in this program, and gradually introduce a bit more pace in bite sized chunks. \r\n\r\nLet me know how you go.\r\nDave'),
(43, 29, '2011-07-16 12:51:20', 'Amanda_Training_Log_11_071.xls', '0000-00-00', '2011-08-14', 'No', 'Hi Amanda. This program sees you continue to build your mileage as well as including more work at tempo. It incorporates the M7 half and C2S. I have restructured your rest days to ensure you are able to continue building, yet to be as fresh as possible for these two runs. Let me know if you have any concerns.\r\nDave'),
(42, 3, '2011-07-16 11:53:19', 'Belinda_Training_Log2011071.xls', '0000-00-00', '2011-08-14', 'No', 'Good luck with this program Belinda. The mileage is almost identical to last month, so hopefully it will be fairly manageable. \r\n\r\nCheers,\r\nDave'),
(41, 30, '2011-07-15 20:57:13', 'market research.doc', '0000-00-00', '2011-07-31', 'No', ''),
(39, 30, '2011-07-15 20:55:31', 'welcome pack.doc', '0000-00-00', '2011-07-19', 'No', 'Hi Dave, good luck with this program, and with the Hunter Valley run.'),
(47, 30, '2011-07-23 15:29:06', 'hdd2.jpg', '2011-08-31', '2011-08-31', 'No', 'Good luck with this program Dave.'),
(49, 4, '2011-08-07 18:00:57', 'Bernard_Training_Log_2011081.xls', '2011-09-04', '2011-09-04', 'No', 'Hi mate,\r\n\r\nThis program sees you continue to progress with both distance and intensity towards a maximum of 72k in week 3, before dropping back to 60k in week 4. I hope your injury is now a thing of the past?\r\n\r\nBest wishes,\r\nDave'),
(52, 32, '2011-08-14 08:17:25', 'Pej_Training_Log_11_081.xls', '2011-08-15', '2011-09-11', 'No', 'Hi Pej. Your first program will see your weekly km climb relatively steeply in preparation for the Sydney Half Marathon. You hit a maximum long run of 16k in this program, which falls back to 12k in your rest week (wk 4). It also introduces the concept of pace work. Look at the ''Notes'' worksheet for an idea of what the words "easy", "tempo", and "steady" mean. It also starts with a 5k time trial so we can get an idea of your current speed. Let me know how you go. Dave'),
(53, 29, '2011-08-14 08:24:57', 'Amanda_Training_Log_11_081.xls', '2011-08-15', '2011-09-11', 'No', 'Hi Amanda. This block is an ambitious one, taking you through 3 halves. I''ve tried to find a balance between allowing sufficient recovery and still allowing you to build towards Chicago. Hopefully you''ll be happy to target Lake Mac as the one to really have a crack at, since it''s fast and will give us the best indication of your current progress. The other two, I''d recommend running hard, but leaving a little in the tank. Sutherland is an out-and-back, so try to ease into it and finish stronger. Let me know how you go. Dave'),
(54, 3, '2011-08-14 09:00:16', 'Belinda_Training_Log2011081.xls', '2011-08-15', '2011-09-18', 'No', 'Wow, time does fly. Race day is nearly upon us. I decided to send through a 5 week block this time, so you know what you''re doing right up until then. This block sees you continue to progress with a bit of a rise in both mileage and intensity for the first 3 weeks, then an easier 2 weeks leading into race day. Also take note of the bike notes. I''ve got you building to 16 mins at 60hard,  30 easy, before switching to keeping the time the same but increasing the proportion of intensity to 90 sec hard, 30 sec easy. Good luck with this. Let me know how you go. Dave');

 

 

 

 

There is some  same data in the programs_for_athletes table.

 

Thanks for your time,

 

Dave

 

[attachment deleted by admin]

[jcbones]

I would suggest MAX() along with GROUP BY, something like:

SELECT a.athlete_id, a.athlete_id, MAX(p.date) AS `date`, p.program_link
FROM
    t_athletes AS a
JOIN
    programs_for_athletes AS p
ON
    a.athlete_id = p.athlete_id_prog
GROUP BY
    a.athlete_id

[davidcriniti]

Thanks for the advice cbones,

 

I had a bit of a play around with your suggestion and tried as many variations as I could, but the best I could get was that it did come up with one link for each athlete, however, there was no info displayed in the table except the program link.

 

ie. no firstname, lastname, sex or program expiry date.

 

Any tips for how to get that?

 

Yours sincerely,

Dave

[jcbones]

Sorry, I plugged in just a couple of returns so that I could see everything without having to scroll.

 

Just change the column selection to *, but make sure to leave the MAX() in place.

SELECT a.*, MAX(p.date) AS `mdate`, p.*
FROM
    t_athletes AS a
JOIN
    programs_for_athletes AS p
ON
    a.athlete_id = p.athlete_id_prog
GROUP BY
    a.athlete_id

[davidcriniti]

I tried that code and it seems to grab one instance of each athlete and show the data for all rows...however, the instance it grabs shows the first entry for each athlete, rather than the most recent entry. I changed the MAX to MIN but still got the same result. I also changed the script so it was incorporating the prog_expiry_date rather than the date column (tried MIN and MAX variations of this to no avail).

 

Ie:

 

SELECT a.*, MAX(p.prog_expiry_date) AS `mprog_expiry_date`, p.*
FROM
    t_athletes AS a
JOIN
    programs_for_athletes AS p
ON
    a.athlete_id = p.athlete_id_prog
GROUP BY
    a.athlete_id ORDER BY $field $sort

 

Any idea what can rectify this?

[jcbones]

With the few rows of data you gave, this query returned one row of data for each athlete_id, based on the highest date in the programs_for_atheletes table.