[RESOLVED] ID Last Row in Subsets of Query Result

[bronzino]

I have a PHP/MySQL query and need to produce a query result like this:

Mike
Albert
Mike
Albert <last row of Alberts>
Mike
Brad <last row of Brads>
Mike <last row of Mikes>

IOW, I need to be able to identify the last row in a subset of query results. How many subsets there are depends on the data. If a new name like "Bill" gets added, I want to be able to dynamically identify the last row for all "Bill" entries too.

I know how to do a loop. Arrays, not so much, and I think that's what I need here. I just spent several hours reading up in the PHP Manual and Googling for some sort of similar example to illustrate the proper way to do this, and I'm not much farther ahead than I was before I started. Can someone advise what array functions would be applicable here, if any? Any other advice would be appreciated as well.

Thanks.

[NogDog]

Pending someone coming up with some slick way to do it in SQL, you could do something like this, perhaps:

$sql = "SELECT name, foo, bar FROM table_name . . . ";
$result = mysql_query($sql);
$names = array();
while($row = mysql_fetch_assoc($result)) {
   @ $names[$row['name']] += 1; // suppress notice if first assignment for this name
}
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)) {
   // do your output or whatever
   if(--$names[$row['name']] == 0) {
      // this was the last one, so output that message
   }
}

Pretty kludgey, but all I can think of right now.

[dagon]

seeing the code used to produce the above would help, things like order by and group by come to mind.

[johanafm]

It depends on what data you need from the row and what the row contains.

As long as you only need values that are identical between rows for each group (such as first and last names, phone number etc), and then the latest date for update or similar, then

SELECT all, fields, with, identical, values, MAX(updateD)
FROM sometable
GROUP BY all, fields, with, identical, values

Should other data differ, you'd need to

SELECT t.stuff, t.you, t.need
FROM sometable t
INNER JOIN
(
	SELECT MAX(date_or_id_or_whatever_determines_this_is_the_last_row) AS last FROM sometable GROUP BY name
) t2 ON t2.last = t.date_or_id_or_whatever_determines_this_is_the_last_row

[bronzino]

Dagon, here you go.

mysql_select_db($DB_Local, $Local);
$query_Option = "SELECT `Key`, `Display Order`, Label, Field1 FROM Pages_NavOption WHERE Pages_NavOption.Style='5'  ORDER BY `Display Order`";
$Option = mysql_query($query_Option, $Local) or die(mysql_error());
$row_Option = mysql_fetch_assoc($Option);
$totalRows_Option = mysql_num_rows($Option);

Key is the unique key column. Display Order is a unique numeric field that orders the results as determined by myself (1, 2, 3, 4 etc). Label is a text field that will display in the results (Mike, Albert, etc.). There is no GROUP BY because I wanted to return all the rows from this table that meet the WHERE criteria.

Field1 is the field that motivated my question. The values in there are: 4, 5, 6 and they are repeated across rows (there may be more values later). I need to find a way to consistently identify the number of 4, 5, and 6s (and any values added later), and then tell me which row appears last in the results ordered by Display Order.

What I am doing, to the extent it helps, is setting up links in a CSS drop-down menu. For that to work correctly, I need to find a way to set the closing "</ul>" tag when the last '4' '5' or '6' row appears within the order set by Display Order. That tag tells the menu, in effect, when to end submenus.

Nogdog and Johanafm, thanks for your suggestions. I will give them a try right now.

[bronzino]

Nogdog, it might be klugey, but klugey is working like a champ right now. I adapted as follows:

  <?php $sql = "SELECT `Key`, Label, `Display Order`, Field1 from Pages_NavOption where style='5' Order by `Display Order`";
$result = mysql_query($sql);
$names = array();
while($row = mysql_fetch_assoc($result)) {
   @ $names[$row['Field1']] += 1; 
}
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)) {
   echo $row['Label'];
   if(--$names[$row['Field1']] == 0) {
     echo '</ul>';
   }
   echo '<br/>';
} 
 ?>

Thank you very much for your help. 🙂