One of the interesting things about programming is that you sometimes come across a task that sounds really easy but is actually very difficult to implement for some technical reason.
My boss came up with one of these recently, showing a field on the page that is a calculation using other fields. Sounded easy enough until he said that the formula used had to be configurable.
We had a bit of a chat about how it could be implemented and the only fast way we could come up with was to use eval()
$result = eval('400 + cos(45)');Fully functional but being the one to commit that would be a everlasting mark of shame.
The main reason that this is a challenging task is that parsing a mathematical formula is not simple. Consider working out the order of execution – doable with some simple rules maybe, but then think about; brackets, functions and other oddities. I did a bit of searching for some sort of maths parser hoping that someone had already done the hard work and didn’t really find anything that looked very good.
There is a library we already use to generate and parse spreadsheet files called PHPExcel which has the ability to calculate the value of a cell you give it. At this point we started considering an implementation that uses a spreadsheet file and various cells to hold the various field values then storing the formula in a column somewhere off to the right. An argument about if this is actually any better than just using eva() later and we decided to have a look at the code of the method that gives you the value of a cell. Not really knowing how spreadsheets work I was expecting a simple lookup, instead I found that the formula was actually parsed in full and calculated. This is almost exactly what we need! The method does not seem to appear in the documentation despite being public, I guess they just thought people would probably find it useful. Cutting an overly long story short, here’s the line you need.
$result = PHPExcel_Calculation::getInstance()->_calculateFormulaValue('=400 + COS(45)');This is actually better than what was asked for since it means people can use complex Excel logic in a way they are used to.