百分比差异循环阵列

Question

First time poster so I hope you can help me with what I think is a simple task but can't figure out.

I have a table called exports which among other things has a year and value field. I currently have data for the years from 1992 to 2011.

What I want to be able to do is extract this data from the database and then calculate the year on year percentage difference and store the results in an array so the data can be passed to a view file.

For example: ((1993-1992)/1992)*100) then ((1994-1993)/1993)*100) then ((1995-1994)/1994)*100) etc etc.

I need it to be flexible so I can add future data. For example I will eventually add data for the year 2012.

I'm really stuck as how to progress this. Help would be greatly appreciated.

Answer 1

If I'm understanding that correctly, the solution wouldn't have to be that complicated. A simple SELECT query to fetch the year and value, which you could then go through using a loop in PHP and calculate the percentages. Something like this:

<?php
// Get all the data from the database.
$sql = "SELECT year, value FROM exports";
$stmt = $pdo->query($sql);

// An array to store the precentages.
$percentages = [];

// A variable to keep the value for the last year, to be
// used to calculate the percentage for the current year.
$lastValue = null;

foreach ($stmt as $row) {
    // If there is no last value, the current year is the first one.
    if ($lastValue == null) {
        // The first year would always be 100%
        $percentages[$row["year"]] = 1.0;
    }
    else {
        // Store the percentage for the current year, based on the last year.
        $percentages[$row["year"]] = (float)$row["value"] / $lastValue;
    }

    // Overwrite the last year value with the current year value
    // to prepare for the next year.
    $lastValue = (float)$row["value"];
}

The resulting array would look like this:

array (
    [1992] = 1.0,
    [1993] = 1.2,
    [1994] = 0.95
    ... etc ...
)