[ Swingley Development ] [ Jump to Content ]
animals baseball beer blog / photolog books bookbinding me other weather woodworking


I'm using a PostgreSQL database to store all the animals, locations, and observations shown on the animal pages.

The database looks like this (click on the image for a PDF version):

Data entry is done using a web form, and the display pages query the database using PHP. Most of the queries are fairly simple, but here's one (not shown on any of the pages) that's a bit more complex because it uses a SQL SUBQUERY:

SELECT observations.ccode, to_char(date, 'DD Mon'), species.common
FROM observations 
    INNER JOIN species ON observations.ccode=species.ccode
WHERE (observations.ccode, date) IN
    (   SELECT ccode, MAX(date)
        FROM observations
        WHERE location='Home'
        GROUP BY ccode HAVING MAX(date) > '2007-12-31 23:59:59'
    AND class='Aves'
ORDER BY species.aou_order;

That query would build a list of birds seen at home in 2008, and the most recent recorded observation for each species. You can see the result here. It would probably be wise to make the year part of the SUBQUERY date dynamic so that I won't have to change it in 2009.

Other animal pages: