[Resolved] Matching checkins and checkouts in a database

klatt

In my database I have a checkin field and a checkout field and they are both date time and allowed null. Meaning when somebody logs in the date/time goes into that fiel and the logout field is null. Then the next record for that person should have a null for checkin and the date/time for the checkout. This is being used to check workers into the payroll system.

Basically the table goes:

ID-username-login-logout

I know this is kind of sloppy database design, but this needs to get done quickly and we won't be having an insanely amount of records or anything like that.

Anyway, I have a form asking for the dates the user wants to start looking for hours and the date the user wants to stop looking for hours and for which worker the user wants to check hours for.

So that information gets passed to my next pageg which should contain the queries neccessary to view that specific information and perform a few calculations on them.

I have to match up the checkins and checkouts for one day and then find the checkins and checkouts for the next days from where the user said to start looking and until where the user said to stop looking.

Sorry that was so long but I wanted to describe the problem as best as possible.

TIA,
Frank

Whew...

devinemke

why not use the same record for login and loginout? when someone logs in do an INSERT query with the current time in the login field:

INSERT INTO table SET
username = '$username'
login = NOW()

then when they log out do and UPDATE query on that same record with the logout as the current time. you could do a simple query to find out which record to update:

$result = mysql_query("SELECT MAX(ID) from table WHERE username = 'username' AND logout IS NULL");
mysql_query('UPDATE table SET logout = NOW() WHERE ID = ' . mysql_result($result, 0));

klatt

Wow, Haha I feel so dumb.

My boss gave me an outline of what he thought the tables should look like and at the time it was my first ever mysql/php project so I just took what he said down as fact.

I guess it shows you to question your boss 🙂

klatt

I have the SQL working for checkin but for the check out I seem to be having trouble.

When I manually the SQL statements into an SQL window with the proper things replaced they work.

But I cannot get the code to work in PHP. I had to change it to mysqli because that is what I am using for the rest...

klatt

Figured it out.

Just had to add:

	$row = $result->fetch_row();
	$id = $row[0];

between the queries.

Thanks a lot.