Executing PHP code stored in a BD

[diesel_heart]

Hello,

I'm a newbie to PHP and I'm currently developing a system where you have to perform some calculations on a set of values.

One of systems requisite is that the formulas used in the calculations must be stored on the data base. This happens because the formulas are expected to change a lot during the systems life, so you have to have way of just changing the formulas WITHOUT recoding the system.

My question is how can you do this with PHP? Like, execute a formula that has been retrieved from the BD, and have it producing a numerical result.

Here's a (pretty silly) example:

- on the BD you have two tables: Fruits and Formulas like this:

Fruits table:

Fruit          Number
--------------------
Apple          2
Orange        3
Banana        5

Formula table:

Name                        Formula
--------------------------------------
Total_fruits          Apple + Orange + Banana
Apples_Oranges    Apple + Orange

So you want to preform a first query to get the calculating formula and a second query for the data.

If tomorrow you want to exchange the Apples_Oranges formula to "Apple - Orange" it would be just a matter of changing the BD.

Any help on this would be widely appreciated.

Thanks in advance.

[SammyP]

I think this is more a question of database design. Assuming at any stage you are just going to want to add or subtract fruits then you could have a table with the name of the result and the fruit as a key, and a weighting to show how it works in the formula.

So you'd start the above with a Formula table looking like:
Total_fruits    Apple    1
Total_fruits    Orange    1
Total_fruits    Banana    1
Apples_Oranges    Apple    1
Apples_Oranges    Orange    1

And then later change it to this:
Total_fruits    Apple    1
Total_fruits    Orange    1
Total_fruits    Banana    1
Apples_Oranges    Apple    1
Apples_Oranges    Orange    -1

Your query for Apples_Oranges would join on the fruit name and multiply the Number value by the Multiplier value, and sum the result.

The numbers can be other than +1 and -1 if you want to apply weightings, but this is still restricted to a formula of the form result = x1.apples + x2.oranges + ...

Anything more complicated will require an extension to this solution.

Sam.

[diesel_heart]

First of all, I'd like to thank you for your help!

This is a good starter for the solution. My example was not only silly but also a bit simplistic: I forgot to mention that I will need to perform multiplications, divisions, and also to have the possibility of use constants. For example:

Apples_Oranges = (1 - (Apples*Oranges)/(10 - Apples)) * 100%

Anyway, it's like you said:

"Anything more complicated will require an extension to this solution."

If you think this subject is off-topic or not appropriate for this forum can you please give some tip where to find information on stuff like this?

Once again, thanks a lot.

[SammyP]

I suspected we weren't talking about fruit and that this might be more complicated.

To put it simply, we need to use Eval here.

If you store a function in a table like this: (1 - ($vals[Apples]*$vals[Oranges])/(10 - $vals[Apples]))

And download it into $fn so that it contains exactly that string then you can execute a line like this:

Eval("\$result = $fn;");

which should be the same as executing the statement:

$result = (1 - ($vals[Apples]*$vals[Oranges])/(10 - $vals[Apples]));


All you have to do to get that to work then is to make sure the $val array contains all of the variables required for the formula. I suspect you can work out how to do that without my help. Not knowing exactly how your variables work together I am probably wasting my time making suggestions.

The above might need a little tweaking as I have never needed to use Eval personally, so I am just going off the PHP Help. To ensure it is working prior to using the array you might want to use $apples and $oranges first. Actually a quick scan and it will need quotes around the fruit constants within the array brackets. Makes life a little more complicated.

[diesel_heart]

"I suspected we weren't talking about fruit and that this might be more complicated."

Indeed, we weren't talking about fruit...but I find that if you shoot complex examples or domain-specific issues on help forums, people tend to shy away from answering...anyway, the fruit stuff did fine!! :-)

Thanks a lot for your support. I believe that Eval might be the solution to my problem. I will want to test it and then, if it tests sucessfully, I'll give some feedback.

(Incidentally, I will be on vacations next week so I'll test only in a couple of days...)

As for the checking and the tweaking, I'll work it out myself.

Thanks again!

[SammyP]

No worries, and your method of posting the specifics of the problem and leaving the other stuff out is perfect. Would be easier to help people if they all did that.

[daiwa]

Just a quick note on the dangers of Eval. its not something you really want to be doing evaluating code that's in a database. As Rasmus Lerdof (creator of PHP) put it "If eval is the answer your asking the wrong question".

[diesel_heart]

Oops!!

[quote author=daiwa link=topic=105313.msg420731#msg420731 date=1156345786]
Just a quick note on the dangers of Eval. its not something you really want to be doing evaluating code that's in a database. As Rasmus Lerdof (creator of PHP) put it "If eval is the answer your asking the wrong question".
[/quote]

It's tempting not to think why Eval is around if it's meant not to be used...I think that remarks like that must be taken with caution...maybe Rasmus Lerdorf said that in a specific context where the statement made sense...I tend to think that on solid established programming languages (as PHP, which is pretty recent when compared to C, for example, but is widely used) when some tool or technique is around, then there must be some usage for it...it's up to the programmer to "ask the right questions"...

Nevertheless, I thank and welcome your feedback and I would appreciate if you could explain this ideia (as I said, I'm a newbie in PHP and I take this as an opportunity to learn more...). Also, if you could present another alternative to my problem's solution, I would be grateful and we all would be more enlighted.

[SammyP]

Most languages I program in (and there are a fair few) have an Eval type function. I very rarely use it and it is mainly for the reason that ol' Rasmus is probably on about. I would generally look for a better way of doing things. Something more object-oriented usually, or go back to my database design, or whatever.

But you're right, it exists for a reason, and if I was going to construct an example of when it might be right to use it, then I'd come up with a question a lot like the one you just came up with.

I was going to suggest it initially but in case your formula was a straight forward sum I showed a database technique of solving it. But the formula was more complicated, and though I can think of a database solution still, it is really just a clunky and repetitive extension of the original solution.

This problem is screaming out for a carefully used Eval.

The warning to stay away from Eval is decent, but not all-encompassing.

[daiwa]

Agreed if its there its because there can be a use for it. Just do keep in mind that you should try to exhaust all possibilities before jumping to it.