unknown unknown - 3 months ago 9
MySQL Question

time log in database for users

Hi guys I have been trying to solve my problem i want to observe the users log in and log out time in the system so I decided to create a separate table like user table and log time table in the user table there is a username , password and the other table which is time log table has timelog_in, timelog_out and the foreign key of user_IDno which is referred to the user table i can insert the user log in time but when the time that the user log out i dont know anymore what to do it's hard to update a row when the cardinality of the table is one to many it's hard to find what column will i use to update the log out time of timelog table

This is the table for users

---------------------------
user_IDno | username | userpassword
---------------------------


This is the table for time log for users

---------------------------
timelog_IDno | timelog_in | timelog_out | user_IDno
---------------------------

Answer

I think there should be multiple entries in your log table for a particular user for a particular day. At the time of log_out you nedd to check if there is an entry for that particular user with a timelog_in woth out timelog_out. if so you have to update the entry.

IF EXISTS  (SELECT 1 
      FROM UserTable u
            JOIN userLogInfo i
                on u.[user_IDno]=i.[user_IDno]
       WHERE user_IDno =@user -- user you wanted to update the info
                   AND timelog_in is not null and timelog_out is null)

 THEN 

  /* Your update query*/

ELSE 
 /* Your insert query*/