knox203 Posted September 22, 2008 Share Posted September 22, 2008 Hello all, I'm running results of a query into a 2 level array, and am now in need of finding the time difference, of all the results per user. an example of the array: Array ( [1] => Array ( [Driver] => 002 [Event] => 1 [Timecode] => 1220546013 [DateTime] => 09-04-08 09:09:33 ) [2] => Array ( [Driver] => 002 [Event] => 2 [Timecode] => 1220549134 [DateTime] => 09-04-08 10:09:34 ) [3] => Array ( [Driver] => 002 [Event] => 1 [Timecode] => 1220894334 [DateTime] => 09-08-08 10:09:54 ) [4] => Array ( [Driver] => 002 [Event] => 2 [Timecode] => 1220984242 [DateTime] => 09-09-08 11:09:22 ) ) I'm at a loss on how to code this correctly, if anyone could point me into the right direction, it would be greatly appreciated!! My current idea is to put every other result into a separate array (the sql query is sorting the results by time) then find the difference between $array1[0]["timecode"] and $array2[0]["timecode"] so on and so forth, but on top of that, am at a loss on how to group all the users when all is said and done. I've got a feeling this could be done much much more efficiently, someone please prove me right Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 22, 2008 Share Posted September 22, 2008 array_multisort should do what you want, if I understand corectly. http://us3.php.net/manual/en/function.array-multisort.php Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Thanks for the response, F1Fan. I'm not sure if array_multisort is going to do what I need it to do. Ultimately what I need to do is calculate the time difference of the first two results in the array for a driver, then the next two so on and so forth. I then need to group those results by the corresponding driver to give me a total time worked for each driver. Does that make sense? Quote Link to comment Share on other sites More sharing options...
discomatt Posted September 23, 2008 Share Posted September 23, 2008 Where are you getting this data from? Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 It somewhat makes sense. You'll need to loop through them, but I'm confused as to what you want to do with the info. This should get you started: <?php $calc = false; $diff = ""; $compare = ""; foreach ($array as $k=>$arr){ if (!$calc){ $calc = true; $compare = $arr['DateTime']; } else{ $calc = false; $diff = abs($compare-$arr['DateTime']; } } ?> Not sure what you want to do with the $diff value, but like I said, it should get you started. Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Discomatt, I'm getting these values from MSSQL, here's the code: <?php $job_select = " SELECT Driver.DriverCode AS Driver, ManifestDriverEvent.ManifestDriverEventTypeID AS Event, DATEPART(dd, ManifestDriverEvent.StartDateTime) AS Day, DATEPART(mm, ManifestDriverEvent.StartDateTime) AS Month, DATEPART(yyyy, ManifestDriverEvent.StartDateTime) AS Year, DATEPART(hh, ManifestDriverEvent.StartDateTime) AS Hour, DATEPART(mi, ManifestDriverEvent.StartDateTime) AS Minute, DATEPART(ss, ManifestDriverEvent.StartDateTime) AS Second FROM dbo.Driver INNER JOIN dbo.ManifestDriver ON Driver.DriverID = ManifestDriver.DriverID INNER JOIN dbo.ManifestDriverEvent ON ManifestDriver.ManifestDriverID = ManifestDriverEvent.ManifestDriverID WHERE (CONVERT(VARCHAR(25), ManifestDriverEvent.StartDateTime, 110) > CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 110)) AND (DATEPART(yyyy, ManifestDriverEvent.StartDateTime) = DATEPART(yyyy, GETDATE())) ORDER BY Driver.DriverCode, ManifestDriverEvent.StartDateTime"; $job_query = mssql_query($job_select) or die("Didn't Work!"); $dates_array = array(); while ( $job_result = mssql_fetch_assoc($job_query) ) { $dates_array[] = array( Driver => $job_result['Driver'], Event => $job_result['Event'], Timecode => mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']), DateTime => date('m-d-y H:m:s', mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year'])) ); } ?> Allow me to add on to what I previously explained, I want to compare the time difference between the first two results for a driver, then do the same for the next two results and so on, then combine the total time differences for each driver to give the total time worked. I need to do it this way because we have drivers that open a manifest that we dispatch jobs to. The drivers frequently close their manifests when they're not working, this can happen multiple times in a day. It doesn't matter to the drivers how long they work, since they are paid on pure commission depending on how many packages they deliver, but we need to know the total time worked for these guys for various reasons for our dispatchers. Thanks for your help guys! - Adam Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 I am officially lost. Do you want the difference of driver1 vs driver2, driver2 vs driver3, and so on, or driver1 vs driver2, driver1 vs driver3, driver2 vs driver1, driver2 vs driver3, and so on? Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Driver 1 Result 1 vs Driver 1 Result 2, Driver 1 Result 3 vs Driver 1 Result 4. Sub Total 1 plus Sub Total 2 to give total time worked, then the same thing for the next driver (all drivers are returned in the array with the results sorted by driver, then by datetime). Again, drivers open and close their manifests multiple times per day, so there are multiple results per driver per day. Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 OK, I think I'm close to understanding. Is it sorta like "Event" 1 is the start, and "Event" 2 is the end, and you need the total times between all the 1's and 2's for each driver? Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Yes, that's exactly right, although there are cases where "event 3" and "event 4" pop up, I haven't been able to determine which stands for what (The data is from a closed source application and they're not giving me much info into this) that's why I'm sorting by datatime, to try to avoid comparing by the Event code (even though it seems it would be much easier to do it that way). Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 <?php $drivertimes = array(); $driverstart = array(); foreach ($array as $k=>$arr){ if (($arr['Event']%2)==0){ // divisible by two means it's an end, not a start if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0; $drivertimes[$arr['Driver']] += $arr['DateTime']-$driverstart[$arr['Driver']]; } else{ $driverstart[$arr['Driver']] = $arr['DateTime']; } } ?> $drivertimes will have the total times for each driver with the 'Driver' as the key. Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Here's the return for $drivertimes(): Array ( [002] => 0 [007] => 0 [008] => 0 [011] => 0 [013] => 0 [077] => 0 [085] => 0 [088] => 9 [090] => 18 [1017] => 9 [1031] => 9 [1038] => 18 [108] => 252 [112] => 0 [1164] => 9 [1309] => 0 [1324] => 9 [1334] => 0 [1354] => 0 [1363] => 9 [1375] => 9 [1378] => 9 [1379] => 27 [1381] => 0 [1385] => 0 [1392] => 9 [1396] => 18 [1402] => 0 [144] => 0 [158] => 0 [236] => 0 [238] => 0 [287] => 0 [313] => 0 [533] => 0 [535] => 144 [618] => 0 [684] => 0 [744] => 9 [781] => 0 [8] => 0 [843] => 27 [932] => 9 [983] => 0 [990024] => 0 [990027] => 9 [990047] => 9 [990059] => 9 [990097] => 0 [990126] => 0 [990141] => 45 [990155] => 9 [990168] => 9 [990170] => 9 [990176] => 9 [990204] => 0 [990209] => 0 [990214] => 0 [990226] => 0 [990232] => 0 [990233] => 0 [990238] => 0 [990240] => 0 [990242] => 54 [990245] => 54 [990261] => 9 [990262] => 27 [990264] => 0 [990266] => 0 [990267] => 9 [990277] => 9 [990287] => 18 [990289] => 9 [990292] => 0 [990299] => 9 [990304] => 9 [990309] => 9 [990311] => 0 [990312] => 9 [990317] => 9 [990318] => 9 [990320] => 0 [990325] => 0 [990327] => 0 [990328] => 0 [990329] => 0 [990331] => 0 [990332] => 0 [990333] => 0 [990334] => 0 [994] => 198 [9999] => 0 ) I'm not 100% sure what I'm looking at here, would it be easier to get the total number of seconds returned by adding the differences of the Timecode, then I could just turn that into hours. I should mention that every once in a while there is a result that looks like this: [55] => Array ( [Driver] => 013 [Event] => 2 [Timecode] => 1222099751 [DateTime] => 09-22-08 09:09:11 ) [56] => Array ( [Driver] => 013 [Event] => 2 [Timecode] => 1222113483 [DateTime] => 09-22-08 12:09:03 ) [57] => Array ( [Driver] => 056 [Event] => 1 [Timecode] => 1221668478 [DateTime] => 09-17-08 09:09:18 ) [58] => Array ( [Driver] => 077 [Event] => 1 [Timecode] => 1220363863 [DateTime] => 09-02-08 06:09:43 ) [59] => Array ( [Driver] => 077 [Event] => 2 [Timecode] => 1220363875 [DateTime] => 09-02-08 06:09:55 ) Notice how driver 56 (result #57) has only returned one result, I hope this doesn't screw up what I'm trying to accomplish too much :-\ Thanks for all your help F1Fan!! Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 Wow, that data is UGLY! I'm noticing that the 'Event' is basically useless. Try this one; <?php $drivertimes = array(); $driverstart = array(); foreach ($array as $k=>$arr){ if (isset($driverstart[$arr['Driver']])){ // Check if the start exists if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0; $drivertimes[$arr['Driver']] += strtotime($arr['DateTime'])-$driverstart[$arr['Driver']]; unset($driverstart[$arr['Driver']]); } else{ $driverstart[$arr['Driver']] = strtotime($arr['DateTime']); } } ?> That should account for the weird 'Event' problem and will return the results in seconds. It will also basically ignore any driver with only one event, or ignore the last event of any driver with an odd number of events. Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 Haha, you're telling me! My head is running in circles trying to understand the best way to do this. Anyways, here's the output from your code, looks like were definitely getting closer, looks like there's still a couple of oddities: Array ( [007] => 0 [011] => -1234145363 [077] => 107996 [085] => 5353136 [088] => -1249999817 [090] => 5306472 [1031] => -1257786507 [108] => -1247141331 [112] => 10321194 [1164] => 7725662 [1334] => 21009486 [1375] => 15685186 [1378] => 10558885 [1379] => 18388873 [1385] => 18536304 [144] => 5281152 [236] => -1252494674 [313] => -1247382614 [533] => 15843645 [535] => -1252289407 [618] => 13291228 [684] => 5108410 [781] => 5119167 [8] => 0 [843] => -1249773005 [990024] => -1247152218 [990027] => -1247026223 [990047] => -1239257422 [990059] => 15681636 [990097] => 13136420 [990126] => -1260274143 [990141] => 15436738 [990155] => 18190840 [990170] => -1234202890 [990176] => 5076019 [990204] => 7851555 [990209] => 5306374 [990214] => 23716773 [990226] => -1252458675 [990232] => -1252455039 [990233] => -1254910128 [990238] => -1241881658 [990240] => -1249787325 [990242] => -1249996139 [990245] => 2872747 [990262] => -1239656943 [990264] => -1241745027 [990266] => -1252271306 [990267] => -1255151284 [990287] => -1242126584 [990289] => 7685972 [990292] => 0 [990299] => -1244599854 [990304] => 20905112 [990311] => 93666 [990312] => -1250046642 [990317] => -1247069351 [990318] => -1247335793 [990320] => 20844011 [990325] => -1252354228 [990327] => -1252498142 [990328] => -1257585014 [990329] => -1247105193 [990331] => -1260248925 [990332] => 2613548 [990333] => 39596 [990334] => 0 [994] => -1252361310 [9999] => -1249960229 ) Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 It looks like your incoming array is not sorted by the date/time. Try using array_multisort to sort by the driver then by the date/time. Then run the code. You could do a abs() around each calculation, but that would probably just mask the solution, and not calculate it correctly. If you've never used array_multisort, follow the link I left before and follow the example on that page. Quote Link to comment Share on other sites More sharing options...
discomatt Posted September 23, 2008 Share Posted September 23, 2008 I'm thinking more of this sorting/calculation sohuld be done at a database-level... I'm not very familiar with MSSQL though, so I can't really help Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 Doesn't look like he has access to that, though. Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 I have not used array_multisort, so I hope I'm doing this correctly, here's the code: <?php $job_select = " SELECT Driver.DriverCode AS Driver, ManifestDriverEvent.ManifestDriverEventTypeID AS Event, DATEPART(dd, ManifestDriverEvent.StartDateTime) AS Day, DATEPART(mm, ManifestDriverEvent.StartDateTime) AS Month, DATEPART(yyyy, ManifestDriverEvent.StartDateTime) AS Year, DATEPART(hh, ManifestDriverEvent.StartDateTime) AS Hour, DATEPART(mi, ManifestDriverEvent.StartDateTime) AS Minute, DATEPART(ss, ManifestDriverEvent.StartDateTime) AS Second FROM Driver INNER JOIN ManifestDriver ON Driver.DriverID = ManifestDriver.DriverID INNER JOIN ManifestDriverEvent ON ManifestDriver.ManifestDriverID = ManifestDriverEvent.ManifestDriverID INNER JOIN DispatchGroup ON Driver.DispatchGroupID = DispatchGroup.DispatchGroupID WHERE (CONVERT(VARCHAR(25), ManifestDriverEvent.StartDateTime, 110) > CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 110)) AND (DATEPART(yyyy, ManifestDriverEvent.StartDateTime) = DATEPART(yyyy, GETDATE())) AND (DispatchGroup.DispatchGroup = 'Corp Driv' OR DispatchGroup.DispatchGroup = 'Truck' OR DispatchGroup.DispatchGroup = 'Corridor' OR DispatchGroup.DispatchGroup = 'PRDriv') ORDER BY Driver.DriverCode, ManifestDriverEvent.StartDateTime"; $job_query = mssql_query($job_select) or die("Didn't Work!"); $dates_array = array(); while ( $job_result = mssql_fetch_assoc($job_query) ) { $dates_array[] = array( Driver => $job_result['Driver'], Event => $job_result['Event'], Timecode => mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year']), DateTime => date('m-d-y H:m:s', mktime($job_result['Hour'], $job_result['Minute'], $job_result['Second'], $job_result['Month'], $job_result['Day'], $job_result['Year'])) ); } array_multisort( $dates_array[]["Driver"], SORT_DESC, SORT_NUMERIC, $dates_array[]["Timecode"], SORT_DESC, SORT_NUMERIC); ?> Here's the result: Array ( [007] => 0 [011] => -1234145363 [077] => 107996 [085] => 5353136 [088] => -1249999817 [090] => 5306472 [1031] => -1257786507 [108] => -1247141331 [112] => 10321194 [1164] => 7725662 [1334] => 21009486 [1375] => 15685186 [1378] => 10558885 [1379] => 18388873 [1385] => 18536304 [144] => 5281152 [236] => -1252494674 [313] => -1247382614 [533] => 15843645 [535] => -1252289407 [618] => 13291228 [684] => 5108410 [781] => 5119167 [8] => 0 [843] => -1249773005 [990024] => -1247152218 [990027] => -1247026223 [990047] => -1239257422 [990059] => 15681636 [990097] => 13136420 [990126] => -1260274143 [990141] => 15436738 [990155] => 18190840 [990170] => -1234202890 [990176] => 5076019 [990204] => 7851555 [990209] => 5306374 [990214] => 23716773 [990226] => -1252458675 [990232] => -1252455039 [990233] => -1254910128 [990238] => -1241881658 [990240] => -1249787325 [990242] => -1249996139 [990245] => 2872747 [990262] => -1239656943 [990264] => -1241745027 [990266] => -1252271306 [990267] => -1255151284 [990287] => -1242126584 [990289] => 7685972 [990292] => 0 [990299] => -1244599854 [990304] => 20905112 [990311] => 93666 [990312] => -1250046642 [990317] => -1247069351 [990318] => -1247335793 [990320] => 20844011 [990325] => -1252354228 [990327] => -1252498142 [990328] => -1257585014 [990329] => -1247105193 [990331] => -1260248925 [990332] => 2613548 [990333] => 39596 [990334] => 0 [994] => -1252361310 [9999] => -1249960229 [] => 0 ) Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 23, 2008 Share Posted September 23, 2008 For some reason, I didn't notice your query before. It looks like you are already sorting it by those fields and in that order. But it clearly isn't working. Since that's the case, I say get rid of your array_multisort and figure out why it's not sorting properly in your select. Also, I don't see where "DateTime" is being declared. Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 23, 2008 Author Share Posted September 23, 2008 DateTime is added as a key in the array (along with it's associated value)... I'm creating it with mktime() then date(). Can I just compare the Timecodes (mysql timecode) that's being created with mktime() from the query? When I print_r() the main array, it looks like SQL is sorting it correctly. Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 24, 2008 Share Posted September 24, 2008 What happens when you print the SQL results array, manually calculate a few of the drivers' times, and compare them to that other array's results? Quote Link to comment Share on other sites More sharing options...
knox203 Posted September 24, 2008 Author Share Posted September 24, 2008 Hey F1Fan, thanks so much for your help! I made the code compare the Timecode instead of DateTime, and it seems to be working just fine! Here's what I ended up with: <?php $drivertimes = array(); $driverstart = array(); foreach ($dates_array as $k=>$arr){ if (isset($driverstart[$arr['Driver']])){ // Check if the start exists if (!isset($drivertimes[$arr['Driver']])) $drivertimes[$arr['Driver']] = 0; $drivertimes[$arr['Driver']] += $arr['Timecode']-$driverstart[$arr['Driver']]; unset($driverstart[$arr['Driver']]); } else{ $driverstart[$arr['Driver']] = $arr['Timecode']; } } ?> Not much different than what you originally gave me... either way, it seems to be working now. Thanks again so much for all your help, I couldn't have done it without ya!! - Adam Quote Link to comment Share on other sites More sharing options...
F1Fan Posted September 25, 2008 Share Posted September 25, 2008 I'm glad I can help! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.