SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2002
    Posts
    142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Question - Wanna Trim The Fat

    I have a query that you can see here:

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

    Here is how the current query(ies) work:

    PHP Code:
    $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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    try this (it's untested) --
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •