Fuzzy sort implementation in PHP

Question

I am dealing with specific request from my users, that I am unable to "break".

Situation:

we work with historic data, that sometimes have uknown date values. We, for example, know, that something happend in year 1943, but we do not know when exactly, what day and what month. Sometimes we do have exact dates. We work with dates from-to, and usualy those are chained so when one event ends, the other starts. You can think of these events for example as dates when a ship was on sea, and in harbor.

Example:

we have two records (in this example, in real we can have dozens of those in one post), with such a structure

Date From - Date To - Event
01.01.1943 - 01.02.1943 - Event 1
DD.MM.1943 - 01.04.1943 - Event 2
01.04.1943 - DD.11.1943 - Event 3
DD.02.1943 - 28.02.1943 - Event 4

DD and MM stays for "day uknown" and "month uknown", so the format of the date stays in DD.MM.YYYY format for further processing.

Problem:

human eye and brain can quickly sort out, that there are two events related - are one after the other, as it sees the "date to" in event 2 to be equal to "date from" in event 3, and sort them in correct order. But, when I want to do it in code, I use some php array_sort function (and sort it only by dateFrom for example), i will get this BAD order

Date From - Date To - Event
01.01.1943 - 01.02.1943 - Event 1
01.04.1943 - DD.11.1943 - Event 3
DD.02.1943 - 28.02.1943 - Event 4
DD.MM.1943 - 01.04.1943 - Event 2

due to order of "01" in alphabet preceding "DD" string. Expected CORRECT order

Date From - Date To - Event
01.01.1943 - 01.02.1943 - Event 1
DD.02.1943 - 28.02.1943 - Event 4
DD.MM.1943 - 01.04.1943 - Event 2
01.04.1943 - DD.11.1943 - Event 3

Is there a way, how to order this in the same way the human brain would? I honestly have no idea, how to aproach this.

Thank you

[This question may help you](https://stackoverflow.com/questions/12590002/php-sort-array-alphabetically-then-numerically) — Martin, Jun 01 '17 at 12:55
I don't think it is possible to do what you want. Unless you tell the computer that these two events belong to eachother so you can chain events by date to and date from, there is no information that a logical processor can use to tell in which order the events should be in. Since the dates are unknown, there is no way to order them in any logical sense. — Glubus, Jun 01 '17 at 13:09
I think I can find a relation between the records by searching if the value of "date from" is in another "date to" record, but I dunno where to go from there :) How to "sort per interval" in this case? Sorting by alphabet and numbers is not the case here, without finding out the relation between records, it cannot be sorted. Please note that example used is very trivial, but the data might be much more complicated (so plain sorting doesnt help). — Radek, Jun 01 '17 at 13:58

score 0 · Answer 1 · answered Jun 01 '17 at 13:41

First of all, for sorting it is better to save your date internaly YYYY-MM-DD so 1943-04-01 is lower than 1943-11-DD. May be you can use an hidden property for sorting.

Second is not clear what to do with your unkown data my be you can set all unkown dates to zero, but I think it is not what you want So for example

Event 1 :DD.MM.1943 - 01.04.1943
Event 2 :01.04.1943 - DD.11.1943
Event 3 :DD.MM.1943 - DD.12.1943

I think you can use an sorting date field, that is not exact, but help you on sorting

If date from is almost known, than sorting date is date from. If there are unknown parts but date to is known, than sorting date is date to - 1 day

I think you need some more roles and some parts may not always correct but may help you

thank you, i will think about this solution (but I guess just inverting the date format doesnt help here, as there might be more complicated examples that the one I used, with multiple "DD.MM" dates and then we are doomed anyway :) — Radek, Jun 01 '17 at 14:00
Yes I think you should search some complicated example and try to get guess dates on date_from, date_to and the dates prev and next to your data, but I think you can also have events which can not sort at all! — Thomas, Jun 01 '17 at 14:16

mickmackusa · Answer 2 · 2017-06-01T22:13:37.913

Second attempt. This provides the desired output using your new input data.

Method: (Demo)

$post='Date From - Date To - Event
01.01.1943 - 01.02.1943 - Event 1
01.04.1943 - DD.11.1943 - Event 3
DD.02.1943 - 28.02.1943 - Event 4
DD.MM.1943 - 01.04.1943 - Event 2';

$rows=array_slice(explode("\r\n",$post),1);  // split by linebreak, remove column heading row
preg_match_all('/- ([\dD]{2}).([\dM]{2}).([\dY]{4})/',$post,$to_bits,PREG_SET_ORDER);  // extract Date To values

foreach($to_bits as $i=>$a){
    $keyed_rows["{$a[3]}-{$a[2]}-{$a[1]}"]=$rows[$i];  // assign Date To values as keys
}
ksort($keyed_rows);  // sort by keys ASC
echo "Date From - Date To - Event\n",implode("\n",$keyed_rows);

Output:

Date From - Date To - Event
01.01.1943 - 01.02.1943 - Event 1
DD.02.1943 - 28.02.1943 - Event 4
DD.MM.1943 - 01.04.1943 - Event 2
01.04.1943 - DD.11.1943 - Event 3

If this doesn't do what you intend with your actual project data, please improve your question with a sample input that will expose the fault in my method.

I had to delete my previous exited comment here, sadly, it doesnt work. Please try in the two rows from my example. You will get the same order as I mentioned in section "problem", and that result is incorrect. I am looking for some logic to sort it as it is mentioned in part "Example". I modified my question to be more precise in this. — Radek, Jun 01 '17 at 17:45
@Radek I updated my answer. If this still doesn't do it for ya, please leave me a comment explaining the issue. — mickmackusa, Jun 01 '17 at 22:14

score 0 · Answer 3 · answered Jun 02 '17 at 12:12

@Radek, I have i little bit refine my idee and here is an algorithmus as start point. May be it need some futher refinements but you can test it with your data ...

<?php

 $event[0]['from']='1943-00-00';
 $event[0]['to']='1943-04-00';

 $event[1]['from']='1943-05-00';
 $event[1]['to']='1943-05-14';

 $event[2]['from']='1943-06-00';
 $event[2]['to']='1943-06-20';

 $event[3]['from']='1943-06-00';
 $event[3]['to']='1943-06-00';

 $event[4]['from']='1943-04-00';
 $event[4]['to']='1943-05-13';

 $event[5]['from']='1943-05-14';
 $event[5]['to']='1943-00-00';

 print_r($event);

 $dates=array();
 foreach($event as $key => $value)
 {
   $from['date']=$value['from'];
   $from['type']=1;
   $from['id']=$key;
   array_push($dates,$from);
   $to['date']=$value['to'];
   $to['type']=2;
   $to['id']=$key;
   array_push($dates,$to);
 }

 function compare_dates($a, $b)
 {
   // sort by date
   $retval = strcmp($a['date'], $b['date']);
   // if date are equal sort my id 
   if(!$retval) $retval = $a['id'] - $b['id'];
   //if also id is equal sort by type
   if(!$retval) $retval = $a['type'] - $b['type'];
   return $retval;
 }


 //first sort by date
 usort($dates,'compare_dates');
 /*
  * unspezific dates shoud be more at the beginning than specific dates 
  * So go from back to begin 
  */
  function searchEvent($array,$id)
  {
    $result=false;
    foreach( $array as $key => $value)
    {
      if($value['id'] == $id)
      {
         $result=$key;
         break;
      }
    }
    return $result;
  }

  print_r($dates);

  $newEvent=array();
 //go over dates and rebuild events
 while(count($dates) > 0 )
 {
   //on the beginn of array we hve the most unspezific events, so we start on the end
   $lastdate=array_pop($dates);
   $id=$lastdate['id'];
   $type=$lastdate['type'];
   if($type == 1)
   {
    $from=$lastdate['date'];
   }
   else
   {
    $to=$lastdate['date'];
   }
   $otherDateKey=searchEvent($dates, $id);

   $otherDate=$dates[$otherDateKey];
   array_splice($dates, $otherDateKey,1);

   $type=$otherDate['type'];
   if($type == 1)
   {
      $from=$otherDate['date'];
   }
   else
   {
     $to=$otherDate['date'];
   }
   $data['from']=$from;
   $data['to']=$to;
   array_unshift($newEvent, $data);
}

print_r($newEvent);

?>

Hi, thank you. I've implemented this solution and ask users to track the results, as there are so many variants to test. But whatever I tried so far, it worked! — Radek, Jun 07 '17 at 07:44

Fuzzy sort implementation in PHP

3 Answers3