sort csv data by date and time columns using php

Question

Background:

I have a php script that reads a csv file. This csv file contain columns. The last two column are date and time

What I need to do is order this data by date and time columns in ascending order

My PHP script:

$data = file("myfile.csv"); 

$string = ""; 



for($i = 1; $i < count ( $data ); $i ++)

{ 

   $info1 = str_getcsv($data [$i],",",'');



    $string .= $info1 [4]  .  "," ; //colomns 1 in position 4 in csv file

    $string .= $info1 [6]  .  "," ; //colomns 2 in position 6 in csv file

    $string .= $info1 [7]  .  "," ; //colomns 3 in position 7 in csv file



    //colomns 4 --this colomn contain date like this format (2/8/2016)

     $string .= $info1 [2]  .  "," ;  

    //colomns 5 --this colomn contain timelike this format (12:30 AM) 

    $string .= $info1 [10];          







  $string .= "\n";

 }

this generates the following:

mark,mark,456345,5/10/2016,9:00 AM

mordl,mordl,23564,5/10/2016,1:00 PM

corten,corten,3216589,5/10/2016,12:00 PM

jack,jack,123645,5/10/2016,8:00 AM

olemn,olemn,29845155,5/10/2016,2:00 PM

jab,jab,457362,5/10/2016,10:45 AM

monk,monk,326251,5/10/2016,3:00 PM

I need it to be sorted like so:

jack,jack,123645,5/10/2016,8:00 AM

mark,mark,456345,5/10/2016,9:00 AM

jab,jab,457362,5/10/2016,10:45 AM

corten,corten,3216589,5/10/2016,12:00 PM

mordl,mordl,23564,5/10/2016,1:00 PM

olemn,olemn,29845155,5/10/2016,2:00 PM

monk,monk,326251,5/10/2016,3:00 PM

What can I do to achieve this?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Use `usort`:

usort() is a function native to PHP that is used for sorting PHP arrays. It can be used to help you.

Firstly in order to use usort(), you need an array, so you need to push the data into an array first instead of pushing it to a string.

Setting up an Array:

This part is easy. All you need to do is initialize an array and push the data into it 2 dimensionally with array_push()

Start the script like this:

$data = file( "myfile.csv" );

$first_dimension = array();
$second_dimension = array();

for($i = 1; $i < count ( $data ); $i ++)
{ 
  $info1 = str_getcsv( $data[ $i ], "," , '' );

  $second_dimension = array( $info1[4], $info1[6], $info1[7], $info1[2], $info1[10] );
  array_push( $first_dimension, $second_dimension );
}

Explanation

The $first_dimension array will contain all of the data, and we will use this array when sorting the data

The $second_dimension array will temporarily contain all data from one row. For each iteration in the for loop second_dim will be update for the current row

The $second_dim = array() assignment inside the for loop will make an array out of the data in the 4^th, 6^th, 7^th, 2^nd, and 10^th element in $info1 respectively

The array_push( $first_dimension, $second_dimension ) inside the for loop adds the $second_dim as an element of $first_dim, therefore creating a 2-dimensional array.

The point of 2 dimensions is so that there is a physical separation between each row, so when we go to sort the $first_dimension array, we will have rows to actually sort.

^{It will also come in handy when pushing new line characters to the string output!}

Prepare for Sorting by Defining a Compare Function:

usort() requires 2 arguments: an array to sort, and a callback comparing function used for determining the order.

The comparing function needs to return a negative number for a preceding element and a positive number for a succeeding element. This means that when comparing 2 rows, the row that comes first needs to be a negative number, because it precedes the row that comes second, and vice versa

This would be a good comparator to use in your script (add it after the for loop):

function compare($row_a, $row_b)
{
    $date_a = date_create( $row_a[3] ) //$info1[2]
    $date_b = date_create( $row_b[3] ) //$info1[2]
    $time_a = date_create( $row_a[4] ) //$info1[10]
    $time_b = date_create( $row_b[4] ) //$info1[10]

    $day_diff = intval( date_diff( $date_a, $date_b )->format( "%R%a" ) )
    $hour_diff = intval( date_diff( $time_a, $time_b )->format("%R%h"))
    $min_diff = intval( date_diff( $time_a, $time_b )->format("%R%i"))
    $time_diff = $hour_diff * 60 + $min_diff;

    if( datediff !== 0 ){
        //if $date_b is a day later than $date_a, then $day_diff will be positive
        //otherwise $day_diff will be negative
        return $day_diff;
    }else{
        //if $time_b is a time later than $time_a, then $time_diff will be positive
        //otherwise $time_diff will be negative
        //if the dates and times are the same, it will return 0
        return $time_diff;
    }
}

Dissecting this Comparator:

In this comparator, we are comparing 2 rows inside $first_dimension side by side and determining if one should precede or succeed the other.

We do this by obtaining the dates (which are in column 3) and times (which are in column 4) from that row.

We then use PHP's date_diff() function to compare each of the times.

Note: date_diff() requires the arguments to be php date objects; we create those with the date_create() function.

Sort with `usort`:

API:

usort ( $array , $comparator_name )

Implementation:

usort( $first_dim, "compare" );

Dumping the Array to a String:

To dump the contents, just use PHP's native implode() function to concatenate the elements of each row into a single string with each element separated by commas

var $string = "";

for($i = 1; $i < count ( $first_dimension ); $i ++)
{ 
    $string .= implode( ",", $first_dimension[$i] );
    $string .= "\n"
}