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

$table = array(

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]:

    [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.

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.


  • 2
    The classic solution is a two-pass compiler. The first builds a table of all references in the program, and the second looks them up from the table. – Davislor Sep 26 '15 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.