CodeSOD: Seven First Dates

Your programming language is PHP, which represents datetimes as milliseconds since the epoch. Your database, on the other hand, represents datetimes as seconds past the epoch. Now, your database driver certainly has methods to handle this, but can you really trust that?

Nancy found some code which simply needs to check: for the past week, how many customers arrived each day?

$customerCount = array();$result2 = array();$result3 = array();$result4 = array();$min = 20;$max = 80;for ( $i = $date; $i < $date + $days7 + $day; $i += $day ) {$first_datetime = date('Y-m-d H:i',substr($i - $day,0,-3));$second_datetime = date('Y-m-d H:i',substr($i,0,-3));$sql = $mydb ->prepare("SELECT COUNT(DISTINCT Customer.ID) 'Customer' FROM Customer WHERE Timestamp BETWEEN %s AND %s",$first_datetime,$second_datetime);$output = $mydb->get_row($sql);array_push( $customerCount, $output->Customer == null ? 0 : $output->Customer);}array_push( $result4, $customerCount );array_push( $result4, $result2 );array_push( $result4, $result3 );return $result4;

If you have a number of milliseconds and you wish to convert it to seconds, you might do something silly and divide by 1,000, but here we have a more obvious solution: substr the last three digits off to create our $first_datetime and $second_datetime.

Using that, we can prepare a separate query for each day, looping across them to populate $customerCount.

Once we've collected all the counts in $customerCount, we then push that into $result4. And then we push the empty $result2 into $result4, followed by the equally empty $result3, at which point we can finally return $result4.

There's no $result1, but it looks like $customerCount was a renamed version of that, just by the sequence of declarations. And then $min and $max are initialized but never used, and from that, it's very easy to understand what happened here.

The original developer copied some sample code from a tutorial, but they didn't understand it. They knew they had a goal, and they knew that their goal was similar to the tutorial, so they just blundered about changing things until they got the results they expected.

Nancy threw all this out and replaced it with a GROUP BY query.

[Advertisement] Forget logs. Next time you're struggling to replicate error, crash and performance issues in your apps - Think Raygun! Installs in minutes. Learn more.