I am getting a multi-line block of text from the single column of a database query result set like this:

$data = <<<DATA
DATE      = FEE = PAYMENT
2021-03-09 = 119.25 = 119.25 = 2021-04-13

2021-03-15 = 119.25 = 119.25 = 2021-04-13
DATA;

I need to parse this text, ignore the header line and any blank lines, extract the first and second values in each line (a date and a float value), then populate an array of associative arrays.

Desired result:

[
    {"Date": "2021-01-23", "fee": 0.00, "title": "example" },
    {"Date": "2021-01-31", "fee": 0.00, "title": "example" },    
]

My current code:

$data = implode("=", $data);
$data = str_replace("\n", "=", $data);
$data = str_replace("       ", "", $data);
$data = explode("=", $data);

But my result looks like this:

Array
(
    [0] => DATE
    [1] =>  FEE 
    [2] =>  PAYMENT
    [3] => 2021-01-23
    [4] =>  119.25 
    [5] =>  119.25 
    [6] =>  2021-01-31
    [7] => 2021-01-31 
    [8] =>  119.25 
    [9] =>  119.25 
    [10] =>  2021-04-13
)

I also tried iterating and saving every n row:

$result = array();
$data = array_values($data);
$count = count($data);
for($i = 0; $i < $count; $i  = 4) {
    $result =  ["Date".$i => $data[$i]];
}
for($i = 0; $i < $count; $i  = 2) {
    $result["fee"] =  $data[$i];
}

CodePudding user response：

You need to first split by newline, drop the first row and then split again by equals sign. Trim to remove the spaces and add as an array. At the end convert the array to a JSON string.

$result = 'DATE      = FEE = PAYMENT
2021-03-09 = 119.25 = 119.25 = 2021-04-13

2021-03-15 = 119.25 = 119.25 = 2021-04-13';

$rows = explode("\n", $result);
array_shift($rows);

$results = [];
foreach ($rows as $row) {
    $row = trim($row);
    if (empty($row)) continue;
    [$date, $fee] = array_map('trim', explode('=', $row));
    $results[] = [
        'date'  => $date,
        'fee'   => floatval($fee),
        'title' => 'example',
    ];
}

echo json_encode($results, JSON_PRETTY_PRINT);

Output

[
    {
        "date": "2021-03-09",
        "fee": 119.25,
        "title": "example"
    },
    {
        "date": "2021-03-15",
        "fee": 119.25,
        "title": "example"
    }
]

CodePudding user response：

It will be most elegant to parse the sample input with sscanf() because it will properly remove unwanted whitespaces and cast the numeric substring as a float-type value. When crafting the "format" parameter of sscanf(), %s means a continuous string of non-whitespace characters and %f means a float value.

When assigning new variables from inside of sscanf(), the returned value is the number of assigned values. When parsing the "header"/first line, the numeric substring will not be matched by %f, so only 1 will be returned. Whenever the function evaluates a line and returns 2, then push that data into the result array. compact() is a concise and convenient way to populate an associative array from existing variables.

Code: (Demo)

$lines = <<<PAYLOAD
DATE      = FEE = PAYMENT
2021-03-09 = 119.25 = 119.25 = 2021-04-13

2021-03-15 = 119.25 = 119.25 = 2021-04-13
PAYLOAD;

$results = [];
foreach (explode("\n", $lines) as $row) {
    if (sscanf($row, '%s = %f', $date, $fee) === 2) {
        $title = 'example';
        $result[] = compact(['date', 'fee', 'title']);
    }
}

echo json_encode($result, JSON_PRETTY_PRINT);

Output:

[
    {
        "date": "2021-03-09",
        "fee": 119.25,
        "title": "example"
    },
    {
        "date": "2021-03-15",
        "fee": 119.25,
        "title": "example"
    }
]