[SOLVED] how to log multiple dates and time in mysql

Issue

I have a mysql database, inside this database. I have every subscription and badge and I’d like to log everytime a badge or a subscription is been used.

This is my database :

id badge validateat
1 badge1 2022-03-22 02:24:25
2 badge2 2022-03-22 02:24:25
3 subscription1 2022-01-22 10:20:15

Can I log everytime a badge is validated inside "validateat" or I have to create a table for every badge and subscription?

Solution

You need a table to log badge activity.

create table badges (
  id integer primary key auto_increment,
  name varchar(255) not null,
  validated_at datetime not null
);

create table badge_activities (
  badge_id integer not null,
    index(badge_id),
    foreign key(badge_id) references badges,
  activity_at datetime not null
)

Then insert into badge_activities every time you want to log something.

insert into badge_activities (badge_id, activity_at) values (3, current_timestamp)

You can also add a column to describe the activity. This could be a varchar, an enum, or a foreign key to another table of activites.

However, unless you need to query this data consider writing to a log file and using a log indexing service instead.

Answered By – Schwern

Answer Checked By – Pedro (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.