phpmyadmin Question posted in
The official documentation can be found here.

mysql / php select first and last record for each year there are records in a particular column in database – Phpmyadmin

I have a table of records of wildlife sightings for different animals. Each row in the table is a record card containing columns for the different animals that it is possible to see, plus an ID column and a DateSeen column.

I would like to display the first and last records for each year from the table below where 1 or more of a particular animal has been seen. I need to display the first and last dates for every year including future years as more data is added. The first and last dates shown should be based on URL parameter that selects which column (animal) to query.

How is this done using mySQL select query and PHP?

TABLE

ID    DateSeen       Lion             Tiger
01    2018-01-01     1                4
02    2018-08-15     2                0
03    2018-09-04     0                2
04    2017-02-02     1                3
05    2017-06-19     2                0 
06    2017-07-24     1                1
07    2017-07-26     0                4
08    2016-01-06     1                1
09    2016-09-17     1                0 
10    2016-09-19     0                4
11    2015-06-11     0                1
12    2015-10-09     1                0
13    2015-10-12     1                0

If the URL parameter = Tiger, results should return

ID    DateSeen (DD-MM-YYYY)       Tiger
01    01-01-2018                  4        = first sighting of Tiger in 2018
03    04-09-2018                  2        = last sighting of Tiger in 2018
04    02-02-2017                  3        = first sighting of Tiger in 2017
06    24-07-2017                  1        = last sighting of Tiger in 2017
08    06-01-2016                  1        = first sighting of Tiger in 2016
10    19-09-2016                  4        = last sighting of Tiger in 2016
11    11-06-2015                  1        = first sighting of Tiger in 2015
11    11-06-2015                  1        = last sighting of Tiger in 2015

If the URL parameter = Lion, results should return

ID    DateSeen (DD-MM-YYYY)      Lion
01    01-01-2018                 1        = first sighting of Lion in 2018
02    15-08-2018                 2        = last sighting of Lion in 2018
04    02-02-2017                 1        = first sighting of Lion in 2017
06    24-07-2017                 1        = last sighting of Lion in 2017
08    06-01-2016                 1        = first sighting of Lion in 2016
09    17-09-2016                 1        = last sighting of Lion in 2016
12    09-10-2015                 1        = first sighting of Lion in 2015
13    12-10-2015                 1        = last sighting of Lion in 2015

It doesn’t matter if the first and last sightings dates of a particular animal are the same (i.e. Tiger only seen once in 2015 as I need to show that that sighting is a first and last sighting in the results.

The date format in the database is YYYY-MM-DD and I need do display the results with the year formatted to DD-MM-YYYY.

Huge thanks in advance to anyone who can help.
Oh and I’m using PHP (pdo), MySql and PhpMyAdmin. I have over 30K records and 40 animals!
Steve

Tags:

3

Answers


  1. 0

    Your test data is all from 2018, and assuming that you need entries from table column value for of animal is greater than zero

    SELECT * 
FROM   (SELECT id, 
               dateseen, 
               lion, 
               year1 
        FROM   table5 t1 
               INNER JOIN (SELECT Substr(dateseen, 1, 4) year1, 
                                  Min(dateseen)          min_dateseen 
                           FROM   table5 
                           WHERE  lion <> 0 
                           GROUP  BY Substr(dateseen, 1, 4)) t2 
                       -- get first and last dateseen for each year  
                       ON t1.dateseen = t2.min_dateseen 
        UNION 
        SELECT id, 
               dateseen, 
               lion, 
               year1 
        FROM   table5 t2 
               INNER JOIN (SELECT Substr(dateseen, 1, 4) year1, 
                                  Max(dateseen)          AS max_dateseen 
                           FROM   table5 
                           WHERE  lion <> 0 
                           GROUP  BY Substr(dateseen, 1, 4)) t3 
                       ON t2.dateseen = t3.max_dateseen) t4 
ORDER  BY year1 DESC, 
          Date_format(dateseen, '%Y-%m-%d') ASC;

    refer to dbfiddle – DBFiddleDemo for complete solution

    If your version is 8.0 then you can use CTE which will reduce the number of queries needed.

    Hope this helps

    Login or Signup to reply.
  2. 0

    your database scheme very bad, must be such

    id, datetime,animal

    1, 2018-01-01 15:21:32, Lion(type=enum(Lion,Tiger,etc..)),

    But your question

    SELECT id,Tiger,date_format(DateSeen, '%d-%m-%Y') as dateAlias from your_table where date_format(DateSeen, '%Y-01-01') = DateSeen OR date_format(DateSeen, '%Y-12-31') = DateSeen AND Tiger > 0
    Login or Signup to reply.
  3. 0

    You have to change table structure, this is bad.
    After you make good structure you can use query like this:

    SELECT min(DateSeen) as first, max(DateSeen) as last, animal FROM animals
GROUP BY year(DateSeen)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search