Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:
log - area (CHAR) - lognotice (CHAR OR text) - logtime (TIMESTAMP WHEN the event was logged).
Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated – the Query to fetch the data could typically look like this:
SELECT * FROM log log1 WHERE logtime = ( SELECT MAX(logtime) FROM log log2 WHERE log2.area = log.area)
To keep things clean and tidy, I only sorte data from the past month, week or day (depending on the “log intensity”). To achieve this I usually do something like this:
DELETE FROM log WHERE logtime < ###time###