CODEWITHSUNDEEP
phpmysql
Cluster
Cluster

Reputation: 65

PHP, MySQL - Multiple records in a DATE field

I was just wondering if it is possible to have multiple records in a date field? For VARCHAR I know that this works:

dater = CONCAT_WS(',', dater,  '$dater' )

However I just wondering if the same can be achieved with DATE field? Reason for this is tha every time record gets updated the date is saved. Any help is greatly appreciated.

Example: 19-12-2016, 20-12-2016, 21-12-2016

Upvotes: 1

Views: 50

Answers (1)

O. Jones
O. Jones

Reputation: 108686

Your best bet is to add an updates table to your schema.

Let's say your main table is called info and has these columns.

   info_id  INT  (autoincrementing)
   info_val VARCHAR(250)
   info_last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Then, you add a table called info_updates. It has these columns:

   info_update_id INT (autoincrementing)
   info_id        INT (points to info table row)
   info_update    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   info_why       VARCHAR(250)  (text describing update, if you like)

Then, every time you insert or update a row in info, you also insert a row in info_updates.

Then you'll be able to get an update history, with the most recent updates first, like this:

   SELECT info.info_id, info.info_val,
          GROUP_CONCAT(info_updates.info_update ORDER BY info_updates.info_update) updates
     FROM info
     JOIN info_updates ON info.info_id = info_updates.info_id
    GROUP BY info.info_id, info.info_val
    ORDER BY MAX(info_updates.info_update) DESC

If you stash your update timestamps into a comma-separated text field, none of this nice retrieval stuff will work properly.

This may seem like extra work. But, comma separated text fields cause much more extra work in the long run.

Prof. Richard Snodgrass has written a fine book on this topic and made it available. Developing Time-Oriented Database Applications in SQL.

Upvotes: 1

Related Questions