Query Question - Wanna Trim The Fat

[thebigtymer]

I have a query that you can see here:

http://www.thebigtymer.com/visuals/

Here is how the current query(ies) work:

$result = mysql_query ("SELECT * FROM albums ORDER BY albumid DESC");
if ($row = mysql_fetch_array($result)) {
do {
echo "<a href=""\"/visuals/?album=${row['albumid']}\">${row['title']}</a><br>";
echo "${row['description']}<br>";
$dateresult = mysql_query ("SELECT MAX(FROM_UNIXTIME(time,'%M %e, %Y %r')) AS time FROM photos WHERE albumid = ${row['albumid']}");
if ($daterow = mysql_fetch_array($dateresult)) {
do {
echo "<span class=\"smallbold\">Updated ${daterow['time']}.</span><br><br>";
} while($daterow = mysql_fetch_array($dateresult));
}
} while($row = mysql_fetch_array($result));
} 

What I want to do is reduce it to one query. However, when I tried a join, it printed an album section for every phpto in the album, that ain't right.

Anybody have ideas on what I should do?

[r937]

try this (it's untested) --

select a.albumid
, a.title 
, FROM_UNIXTIME(MAX(p.time),'%M %e, %Y %r'))
  as lastdatetime
from albums a
inner
join photos p
on a.albumid = p.albumid
group by a.albumid
, a.title 
order by a.albumid desc

if you convert a unix timestamp column to a string format where the month comes first, and then take the max of that string, then Sep comes after Oct, Nov comes after Dec, Jan comes after Feb, etc.

what you want to do is take the max unix timestamp and then format that

rudy