I have a table that inserts data when a kid checks in into a summer camp area. If the kid is < than 12 years of age a parents barcode must be scanned to allow the kid entry into the young kids area (<12 years of age).
The table has the following columns.
kcID
uID
id
age
room
barcode
date
amber
The data will usually be presented like this.
kcID uID id age room barcode date amber
25 1 1 30 1000 0001 6/26/2014 1:27:40 AM 0
26 6 1 1 1000 0005 6/26/2014 1:27:40 AM 0
The problem I have is that I need to compare the dates/hours to know if the kid is entering or leaving the camp area and via php send an SMS to the parents so they know their kid is outside a particular area.
I know I can retrieve the max(kcID) WHERE barcode = XXXX and that will return the last inserted row, but, in order for me to retrieve said information the kid must be scanned, properly inserting a new row and rendering max(kcID) useless in this case.
What I need is to be able to select max(kcID) WHERE barcode = xxxx and then select the previous row record in which barcode = xxxx is found. That way I can compare dates and know if the kid is leaving or entering that particular area.
The easiest solution I can think of right now is to have 2 tables (1 for entry 1 for out) and have the camp counselors choose if the kid is entering or leaving but I'm wondering if I can use only 1 table.
You could add a column indicating leave / return, and add this to your condition.
e.g.
ALTER TABLE `table`
ADD `status` enum('left', 'returned') NOT NULL DEFAULT 'left';
a query would be
SELECT `kcID`, `barcode`
FROM `table`
WHERE `status` = 'left'
ORDER BY `timefield` DESC
LIMIT 1
Add a column with a status left and returned
ALTER TABLE `table` ADD `status` enum('left', 'returned') NOT NULL DEFAULT 'left';
now you can select a second last row by following query
select `kcID`, `barcode` from `table` where `status` = 'left' ORDER BY `barcode` DESC LIMIT 1,1