How to split string with date as delimiter?

Question

Could you help me with splitting large string into substrings?

Scenario: In one of fields inside MySQL table I've got string in which there is an activity log in following format:

yyyy-mm-dd hh:mm:ss - Name1 Surname1 - Activity1
yyyy-mm-dd hh:mm:ss - Name2 Surname2 - Activity2
yyyy-mm-dd hh:mm:ss - Name3 Surname3 - Multiline Activity1
Multiline Activity2
Multiline Activity3
yyyy-mm-dd hh:mm:ss - Name4 Surname4 - Activity4

Question:

How can I split the above data into array like this:

id       date                    name                    activity
1        yyyy-mm-dd hh:mm:ss     Name1 Surname1          Activity1
2        yyyy-mm-dd hh:mm:ss     Name2 Surname2          Activity2
3        yyyy-mm-dd hh:mm:ss     Name3 Surname3          Multiline Activity 1, Multiline Activity2, Multiline Activity 3
4        yyyy-mm-dd hh:mm:ss     Name4 Surname4          Activity4

using PHP or MySQL query?

I know it could be done somehow, but unfortunately by knowledge of PHP/MySQL doesn't allow me to parse text with variable delimiter (date), or maybe there is another way?

You can do it even with mysql. Check this great answer: http://stackoverflow.com/questions/6152137/mysql-string-split However, this is advanced usage, for master programmers :) — hek2mgl, Jun 04 '13 at 09:58
i think your table design is wrong .you can use explode function in php to get the output as desired — Arun Killu, Jun 04 '13 at 09:59
Unfortunately I'm working on existing database, so I'm not the designer of the data structure. That's question is related to future conversion of the datastructure into more accessible format — user2451110, Jun 04 '13 at 10:29

enenen · Accepted Answer · 2013-06-04T10:42:11.397

You can try with preg_split:

$string = <<<STR
2013-06-04 12:12:12 - Name1 Surname1 - Activity1
2013-06-04 12:12:12 - Name2 Surname2 - Activity2
2013-06-04 12:12:12 - Name3 Surname3 - Multiline Activity1
Multiline Activity2
Multiline Activity3
2013-06-04 12:12:12 - Name4 Surname4 - Activity4
STR;

$arr = preg_split("/(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}) - (.*) - (.*)[\r\n]?/", $string, null, PREG_SPLIT_DELIM_CAPTURE);

$arr = array_filter($arr);

print_r($arr);

Output:

Array
(
    [1] => 2013-06-04 12:12:12
    [2] => Name1 Surname1
    [3] => Activity1
    [5] => 2013-06-04 12:12:12
    [6] => Name2 Surname2
    [7] => Activity2
    [9] => 2013-06-04 12:12:12
    [10] => Name3 Surname3
    [11] => Multiline Activity1
    [12] => Multiline Activity2
Multiline Activity3

    [13] => 2013-06-04 12:12:12
    [14] => Name4 Surname4
    [15] => Activity4
)

Example for a real usage:

$i = -1; 
$result = array();

foreach($arr as $value) {
    if(preg_match("/(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})/", $value)) {
        // 1st element - datetime
        $i++;
        $result[$i]['datetime'] = $value;
    } elseif(!isset($result[$i]['name'])) {
        // 2nd element - name
        $result[$i]['name'] = $value;
    } else {
        // 3rd element - activities separated by comma
        if(!isset($result[$i]['activities'])) {
            $result[$i]['activities'] = $value;
        } else {
            $result[$i]['activities'] .= ', ' . $value;        
        }
    }
}

print_r($result);

Output:

Array
(
    [0] => Array
        (
            [datetime] => 2013-06-04 12:12:12
            [name] => Name1 Surname1
            [activities] => Activity1
        )

    [1] => Array
        (
            [datetime] => 2013-06-04 12:12:12
            [name] => Name2 Surname2
            [activities] => Activity2
        )

    [2] => Array
        (
            [datetime] => 2013-06-04 12:12:12
            [name] => Name3 Surname3
            [activities] => Multiline Activity1
, Multiline Activity2
Multiline Activity3

        )

    [3] => Array
        (
            [datetime] => 2013-06-04 12:12:12
            [name] => Name4 Surname4
            [activities] => Activity4
        )

)

thanks, it's exactly what I've been looking for! cheers, and have a good day sir ;) — user2451110, Jun 04 '13 at 10:44

score 0 · Answer 2 · answered Jun 04 '13 at 09:57

0

Loop over the MySQL result and for each row, perform this:

$row = explode(' - ', $row);

answered Jun 04 '13 at 09:57

silkfire

24,585
15
82
105

Problems with this solution: A name may be hyphenated e.g. John - Doe. Activity contains some unknown set of characters (to us). – BLaZuRE Jun 04 '13 at 09:58
@BLaZuRE And how do you know that? – silkfire Jun 04 '13 at 10:03
I code for worst-case scenarios, so I point out potential problems instead of downvoting. – BLaZuRE Jun 04 '13 at 10:07
thanks, but @BLaZuRE got that point... Activity is usually multi line text with every character (and their combinations) possible – user2451110 Jun 04 '13 at 10:26

How to split string with date as delimiter?

2 Answers2