Get the first and last value from set of records

mrgrammar

We're getting ready to do a weight loss challenge on www.Biggest-Loser.com. I have a table that contains everyone's weights that they enter with each weigh-in. Each record that contains a weight also has a date in a date field. I have another table of all the people.

I start by getting a list of the people. For each person, I want to display each person's starting weight and their latest weight.

$swt = "SELECT date, weight FROM weighttable WHERE peepid = $id";

What is the best way to do so?

ragedigital

$swt = "SELECT date, weight FROM weighttable WHERE peepid = $id ORDER BY date LIMIT 0,1";


$swt = "SELECT date, weight FROM weighttable WHERE peepid = $id ORDER BY date DESC LIMIT 0,1";

I'm sure there's an easier way to do this in one query...

Piranha

How many times will a person be weighted? It might (and might not) be better in this case to ge all results and only display the first and last, something like this:

$swt = "SELECT date, weight FROM weighttable WHERE peepid = $id ORDER BY date LIMIT 0,1";
$result = mysql_query($swt);
$norows = mysql_num_rows($result);
$first = mysql_result($result, 0); // The 0 is for the first row
echo $first['date'] . $first['weight'];
$last = mysql_result($result, $norows-1); // Here we chose to use the last row ($norows start from 1 since it counts the number of rows, that's why we use -1)
echo $last['date'] . $last['weight'];

I can't tell what is better, the idea from ragedigital (that actually was my first thought as well) or my idea. It depends on variables such as how many rows there are for each person, how long it takes for the database to connect and a few other things.

Note that there should be controls to make sure that there are at least one row returned. I didn't do them since I only wanted to show the idea.