a system that record and count user logged in

Question

hi again everyone

i am trying to build a log system that would record how many times a particular user logged in to my system, my temporary logic is this way:

every time a user logged in i would extract these fields

• user_id
• counter
• time(m/d/y) << daily log

and store those inside a table in the database

the action would be this way:

SELECT * FROM member_log WHERE user_id = posted_user_id AND logged_time = posted_logged_time

if the field is found then do UPDATE member_log SET counter = counter+1 if the field is not found then INSERT

am i on the right track, or is there any better approach ??

and the next question would be, how to count the amount of users logged in per month

need guidance

thank you in advance

Answer 1

If login time is not important for you - your approach is correct.

For counting the amount of users logged in per month

you can use query:

 SELECT Time, Sum(Counter)
 from YoutlogTable
 GROUP BY Time