4

I have my SQL table: 

+----+-----+-------+-------+
| id | reg |  in   | out   |
+----+-----+-------+-------+
|  1 | a   | 11:10 |       |
|  2 | a   |       | 11:30 |
|  3 | b   | 06:10 |       |
|  4 | c   |       | 07:10 |
+----+-----+-------+-------+

I have it sorting to an array so that it will match up if the reg is the same and in is before out so for example:

array( 
[0]=> array('reg'=>'a','in'=>'11:10','out'=>'11:30'),
[1]=> array('reg'=>'b','in'=>'06:10','out'=>''),
[2]=> array('reg'=>'c','in'=>'','out'=>'07:10')
)

I then need to resort this array so that its in order of time, but where there is both an in and out time only use the out time. So if this was sorted it would be: 

 array(
    [0]=> array('reg'=>'b','in'=>'06:10','out'=>''),
    [1]=> array('reg'=>'c','in'=>'','out'=>'07:10'), 
    [2]=> array('reg'=>'a','in'=>'11:10','out'=>'11:30')
    )

I hope that makes sense, I have around 200 entries per day and I've spent all of my weekend trying to get it ordered but there seems to always be a stray.

The times will be a unix timestamp i've just put it in H:i here as the SQL will sort WHERE > midnight and < 00:01 that morning showing only the current day.

There is probably something really simple and obvious staring at me but I can't see it.

Browners
  • 81
  • 3
  • 5
  • usort() with a callback that compares based on max(in, out)? – Mark Baker Dec 14 '15 at 14:19
  • 1
    Though if the data comes from a database, handle the sorting using an ORDER BY clause – Mark Baker Dec 14 '15 at 14:20
  • Thanks @MarkBaker usort() might actually work for me. I already use the Order by for the original set of results sometimes they can get out of order so I needed a way to sort them back again. Thanks again – Browners Dec 14 '15 at 20:02

2 Answers2

1

I would do it in SQL while you query the data, assuming that out date is later than in date:

select reg, max(`in`) as maxin, max(`out`) as maxout from table
group by reg
order by greatest(maxin,ifnull(maxout,0))
Shadow
  • 30,859
  • 10
  • 44
  • 56
  • This will only work if every `reg` only checks in and out once. If they visit the next day again you'll get wrong results. – Gerald Schneider Dec 14 '15 at 15:13
  • @GeraldSchneider 1. The OP does not provide such example, we do not even know if that's possible. 2. The OP does not provide any pointer as to what should we do if there are multiple ins and outs. If there are multiple ins and outs in the period being queried, then my solution will order by the last in/out pair. In absence of a clear definition you cannot assume that this is wrong. – Shadow Dec 14 '15 at 15:23
0

You can create a sorting value with IF():

SELECT `reg`,`in`,`out`, IF(`out`='00:00:00', `in`,`out`) AS sortfield 
FROM `test` 
ORDER BY sortfield

This assumes that your in and out fields are of the type TIME. It checks if out is has the value00:00:00, which is the default value when it's empty. If yes the sort value will get the value from in, else it gets out, so it's always populated.

Of course a checkin or -out at exactly 00:00:00 would be a problem, so it would be better to make the field NULL if it's empty and check for that.

For long term logging the type DATETIME would be the best option.

Gerald Schneider
  • 16,520
  • 9
  • 55
  • 76
  • I need to match up the rows when the reg matches so I don't think this will work ? Thanks though. With the times there will always be a time in the field so I shouldn't have to worry about 0 been mistaken for midday/midnight – Browners Dec 14 '15 at 20:00