5

I have a requirement to allow my end users to input formula much like a spreadsheet. I have an array like this:

$table = array(
    1=>array(
            "id"=>1,
            "Name"=>"Regulating",
            "Quantity"=>"[2]Quantity+[3]Value",
            "Value"=>"[2]Cost"
        ),
...)

The first level array key is always the same value as the id key in that array.

A tabulated example follows:

id  Name        Quantity                Value
1   Regulating  [2]Quantity+[3]Value    [2]Cost
2   Kerbs       3                       6
3   Bricks      9                       7
4   Sausages    [3]Cost                 3
5   Bamboo      [4]Quantity             [7]Cost
6   Clams       [4]Quantity             NULL
7   Hardcore    [3]Quantity*0.5         12
8   Beetles     [6]Quantity*[4]Value    [2]Value

The Quantity and Value keys represent formula which reference the [id] and either Quantity, Value or Cost.

Cost is derived by multiplying the Value and Quantity.

I am using:

preg_match_all("/\[(.*?)\]([A-Z]*[a-z]*)/", $string, $matches, PREG_SET_ORDER);

which outputs an array like so for[1][Quantity]:

Array
(
    [0] => Array
        (
            [0] => [2]Quantity
            [1] => 2
            [2] => Quantity
        )

    [1] => Array
        (
            [0] => [3]Value
            [1] => 3
            [2] => Value
        )

)

Iterating through the table using something similar to: $calcString = $table[1]['Quantity'];`

foreach ($matches as $match) {
    $calcString = str_replace($match[0], $table[$match[1]][$match[2]], $calcString);
}

I can get the string to be calculated and am using a matheval class to do the sum.

For example

[1]Quantity = [2]Quantity + [3]Value
[2]Quantity = 3
[3]Value = 7 // [1]Quantity = 3 + 7 = 10

[1]Value = [2]Cost
[2]Cost = [2]Quantity * [2]Value // 3 * 6 = 18

Basically the variables in the table refer to other [id]key in the same table.

But here is my issue

I need to resolve references to other parts of the table (which may or may not themselves be formula) to fill in the blanks. This is outside my comfort zone and I would appreciate any advice (or even better functional code) which provides enlightenment on how I might be able to achieve this.

Thanks

10
  • It's still not clear how formula evaluation is supposed to work. What would be a reference to another cell? Can you do the calculations by hand, step-by-step, annotating each step with comments and intermediate values? Commented Sep 26, 2015 at 8:50
  • For example [1]Quantity is: [2]Quantity+[3]Value. [2]Quantity=3, [3]Value=7 so the formula is 3+7, the answer to [1]Quantity = 10. [1]Cost = [2]Cost, [2]Cost = [2]Quantity * [2]Value which is 18, we now know the answer to both [2]Cost and therefore [1]Value. Basically the variables in the table refer to other [id]key in the same table. Commented Sep 26, 2015 at 9:01
  • 1
    Ah, I see. BTW, add this walkthrough to the question itself Commented Sep 26, 2015 at 9:06
  • 1
    @tozjerimiah: my PHP is non-existent, but I'll give a stab at it in ruby, just for fun. Let's see how it goes :) Commented Sep 26, 2015 at 9:15
  • 1
    @tozjerimiah: if you manage to handle recursive evaluation, the next problem is to detect reference cycles (as in "a = b; b = c; c = a"). This is also a fun problem to solve :) Commented Sep 26, 2015 at 9:26

3 Answers 3

3

Deep down, you already know how to solve this, you're just intimidated by the task.

A recursive approach would be to expand references instantly. For example,

expand('[1]Value') # returns '[2]Cost'
  expand('[2]Cost') # returns '[2]Quantity * [2]Value'
    expand('[2]Quantity') # returns 3
    expand('[2]Value') # returns 6
    eval('3 * 6')
    # returns 18
  # returns 18
# returns 18

An iterative (non-recursive) approach is to expand one reference at a time and repeat until there are unresolved references in the string.

expand('[1]Value') // returns '[2]Cost'
expand('[2]Cost')  // returns '[2]Quantity + [2]Value'
expand('[2]Quantity + [2]Value') // returns 3 for [2]Quantity
expand('3 * [2]Value')  // returns 6 for [2]Value
eval('3 * 6') 
# returns 18

Normally, I prefer iterative solutions, because they're much less prone to stack overflows. However, recursive solutions are usually easier to write.

Here's a quickly slapped-together recursive evaluator: https://gist.github.com/stulentsev/b270bce4be67bc1a96ae (written in ruby, though)

Sign up to request clarification or add additional context in comments.

1 Comment

Your assessment is probably right. I will sit down tonight and hammer it out. Will come back if I have any questions. Thanks for your input and faith.
2

If calcString's are reasonably sized and you don't expect replacements to get too elaborate, you could use a while loop to simulate the recursion. Here's an example that outputs the string along the way as it is being modified:

$calcString = $table[8]['Quantity'];

preg_match_all("/\[(.*?)\]([A-Z]*[a-z]*)/", $calcString, $matches, PREG_SET_ORDER);

print_r($calcString . "\n");

while (!empty($matches)){
  foreach ($matches as $match) {
    preg_match_all("/\[(.*?)\](Cost)/", $match[0], $matchCost, PREG_SET_ORDER);

    if (!empty($matchCost)){
      $cost = $table[$matchCost[0][1]]['Quantity'] . "*" . $table[$matchCost[0][1]]['Value'];
      $calcString = str_replace($match[0], $cost, $calcString);
    } else {
      $calcString = str_replace($match[0], $table[$match[1]][$match[2]], $calcString);
    }

    print_r($calcString . "\n");

  }
  preg_match_all("/\[(.*?)\]([A-Z]*[a-z]*)/", $calcString, $matches, PREG_SET_ORDER);
}

Output:

[6]Quantity*[4]Value
[4]Quantity*[4]Value
[4]Quantity*3
[3]Cost*3
9*7*3

The table variable:

$table = array(
  1 => array(
         "id" => 1,
         "Name" => "Regulating",
         "Quantity" => "[2]Quantity+[3]Value",
         "Value" => "[2]Cost"
       ),
  2 => array(
         "id" => 2,
         "Name" => "Kerbs",
         "Quantity" => 3,
         "Value" => 6
       ),
  3 => array(
         "id" => 3,
         "Name"=>"Bricks",
         "Quantity"=> 9,
         "Value"=> 7
       ),
  4 => array(
         "id" => 2,
         "Name" => "Sausages",
         "Quantity" => "[3]Cost",
         "Value" => 3
       ),
  5 => array(
         "id" => 2,
         "Name" => "Bamboo",
         "Quantity" => "[4]Quantity",
         "Value" => "[7]Cost"
       ),
  6 => array(
         "id" => 2,
         "Name" => "Clams",
         "Quantity" => "[4]Quantity",
         "Value" => NULL
       ),
  7 => array(
         "id" => 2,
         "Name" => "Hardcore",
         "Quantity" => "[3]Quantity*0.5",
         "Value" => 12
       ),
  8 => array(
         "id" => 2,
         "Name" => "Beetles",
         "Quantity" => "[6]Quantity*[4]Value",
         "Value" => "[2]Value"
       )
);

Comments

1

A dangerously easy, and your-situation-specific well-performable solution!

<?php
class solver {
    private
            // The final output array
            $arr_evaled,
            // When a cell gains its final value, the corresponding entry in the following array gets marked as being done!
            $arr_done;

    private $solving_iterations_count;

    public function solver($array) {
        $this->arr_done = array();

        foreach($array as $k => $arr)
            $this->arr_done[$k] = array('Quantity' => false, 'Value' => false);

        // Firstly,expand all of the "[x]Cost"s to "([x]Quantity*[x]Value)"s!
        $this->arr_evaled = array_map(
            function($v){ return preg_replace('#\[(\d*?)\]Cost#', '([$1]Quantity*[$1]Value)', $v); },
            $array
        );

        $this->solving_iterations_count = 0;
        $this->solve();
    }

    private function isDone() {
        foreach($this->arr_done as $a)
            if($a['Quantity'] == false || $a['Value'] == false)
                return false;
        return true;
    }
    private function isCellDone($id, $fieldName) {
        return $this->arr_done[$id][$fieldName];
    }
    private function markCellAsDone($id, $fieldName, $evaluation) {
        $this->arr_done[$id][$fieldName] = true;
        $this->arr_evaled[$id][$fieldName] = $evaluation;
    }
    private function isEvaluable($str) {
        return preg_match('#^[0-9*+-\/\(\)\.]*$#', $str) == 1 || strtolower($str)=='null';
    }
    private function replace($from, $to) {
        foreach($this->arr_evaled as &$arr) {
            $arr['Quantity'] = str_replace($from, $to, $arr['Quantity']);
            $arr['Value'] = str_replace($from, $to, $arr['Value']);
        }
    }

    private function solve() {
        $isSolvable = true; // YOUR TODO: I believe coding this part is also fun!) (e.g: check for "reference cycles")
        if(!$isSolvable) return null;

        while( !$this->isDone() )
        {
            foreach($this->arr_evaled as $arr) {
                foreach(['Quantity', 'Value'] as $fieldName) {
                    if(!$this->isCellDone($arr['id'], $fieldName)) {
                        if($this->isEvaluable($arr[$fieldName])) {
                            $evaluation = eval("return {$arr[$fieldName]};");
                            $this->markCellAsDone($arr['id'], $fieldName, $evaluation);
                            $this->replace("[{$arr['id']}]$fieldName", "$evaluation");
                        }
                    }
                }
            }
            $this->solving_iterations_count++;
        }
        foreach($this->arr_evaled as &$row)
            $row['Cost'] = $row['Quantity'] * $row['Value'];
        return $this->arr_evaled;
    }

    public function print_tabulated() {
        echo "The count of solving iterations: {$this->solving_iterations_count}<br/><br/>";
        echo '<table border="1"><tr><th>id</th><th>Name</th><th>Quantity</th><th>Value</th><th>Cost</th></tr>';
        foreach($this->arr_evaled as $arr)
            echo "<tr><td>{$arr['id']}</td><td>{$arr['Name']}</td><td>{$arr['Quantity']}</td><td>{$arr['Value']}</td><td>{$arr['Cost']}</td></tr>";
        echo '</table>';
    }
}

// Testing
$arr = array(
    1 => array( 'id' => 1, 'Name' => 'Regulating', 'Quantity' => '[2]Quantity+[3]Value', 'Value' => '[2]Cost'  ),
    2 => array( 'id' => 2, 'Name' => 'Kerbs',      'Quantity' => '3',                    'Value' => '6'        ),
    3 => array( 'id' => 3, 'Name' => 'Bricks',     'Quantity' => '9',                    'Value' => '7'        ),
    4 => array( 'id' => 4, 'Name' => 'Sausages',   'Quantity' => '[3]Cost',              'Value' => '3'        ),
    5 => array( 'id' => 5, 'Name' => 'Bamboo',     'Quantity' => '[4]Quantity',          'Value' => '[7]Cost'  ),
    6 => array( 'id' => 6, 'Name' => 'Clams',      'Quantity' => '[4]Quantity',          'Value' => 'NULL'     ),
    7 => array( 'id' => 7, 'Name' => 'Hardcore',   'Quantity' => '[3]Quantity*0.5',      'Value' => '12'       ),
    8 => array( 'id' => 8, 'Name' => 'Beetles',    'Quantity' => '[6]Quantity*[4]Value', 'Value' => '[2]Value' ),
);
echo '<pre>';
(new solver($arr))->print_tabulated();

Here is the output:

The output of the code in http://stackoverflow.com/a/32805259/3709765

4 Comments

Hi-performance, you say? You have numbers to back this up? :)
@SergioTulentsev Well, actually, the code has not gone through the tough tests, like infinitely many times!! maybe the "well-performable" is a better substitution :)
This is excellent however I need the output to include the 'Cost' field and result. Would you be able to modify your answer so that the final output contains this? Thanks!!
@tozjerimiah The answer has been updated to reflect the new requirement announced in the comment above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.