@DTest not sure I understand your fiddle, but I prefer to work around the terminator limitations with comments like so rather than using an alien delimiter?

@DTest ah, didn't notice that

@JackDouglas Ahh, I think I agree with your comments approach.

Could anyone take a look at my question please :)

@I'll-Be-Back it's one of those questions that makes people hesitate

@I'll-Be-Back also not everyone here is versed in MySQL. I'm certainly not.

Ohhh

@AaronBertrand it's not the MySQL side that gives me pause personally

@JackDouglas CALL validate_foo(;DELETE FROM foo; --'); results in a syntax error and then the Delete statement (multiple queries) when run from command line, and with a privileged user, the delete definitely succeeds. My idiocy was that I thought it was happening inside the stored procedure.

@JackDouglas that's fair, I was just explaining my hesitation.

@DTest I see - rather than just

CALL validate_foo(;

My question wasnt related what the SQL query query should be, but how to create a a table with formula setting and fields. Does my question make sense?

DELETE FROM foo;

@I'll-Be-Back I've commented

please reply on the Q

@JackDouglas correct

@JackDouglas can we ban the php tag from the site? :)

@DTest it certainly isn't needed

but banning it isn't something mods can do, so we'd have to have a good reason to pester SE staff to do it

@JackDouglas what do you mean by "are your categories subject to change?"

Yeah I figured. If only we could create a tag synonym to /dev/null

You mean the name of category?

@I'll-Be-Back no - new ones

@I'll-Be-Back new categories, deleted categories, changed formula for a category

is it a static list

Ok I will reply.

more important to know for the formulas

@JackDouglas also if it's finite I was thinking computed columns, but I don't even know if such a concept exists in MySQL. :-(

@AaronBertrand hard coded into a view is what I was thinking :-)

Computed columns - only in MariaDB variant.

@AaronBertrand for a RDBMS that doesn't even honor CHECK ? pssh

@JackDouglas I have replied

@ypercube are you involved in MariaDB project or just keep up with it?

No, I'm not involved.

I attended a meeting they had in Athens last year, so I learned a few things, and keep up from then.

VIRTUAL COLUMN

@I'll-Be-Back I was going to suggest something like @grid's answer

@ypercube I see..If only they would fix this bug

I wasnt expecting query answer

I was hoping for formula table.

@I'll-Be-Back why?

I was thinking like a formula table with a few fields. It contain formula setting

something like that

@Dtest: yeah, and many others

@I'll-Be-Back generic

only go that way if you absolutely have to

Yes something like category_formula table and I use php to calculate it.

But I am stuck with the fields and what should be there

Maybe you could answer that

@I'll-Be-Back using eval()?

no

@I'll-Be-Back it's business logic - it belongs in the database in my opinion

is php the only thing that will ever touch your database?

is your app the only php app that will ever touch your database?

@I'll-Be-Back as long as you aren't planning to build dynamic SQL from the formula string ;-)

The thing is.. admin can select a formula setting from a website on each category.. Then save it.

That is why I was hoping like a category_formula table

@I'll-Be-Back you need to describe all of these details in your question - most of the people who will read your question are not here checking for what you said in chat.

@I'll-Be-Back also you need to describe the logic a bit better - does an admin update the formula for a category for him/herself, or does that formula apply to everyone?

@JackDouglas dynamic SQL isn't the devil. It's very practical in a lot of cases.

I have updated my question.

@AaronBertrand it's not the dynamic sql that would bother me here but the bouncing of a formula stored in the db (in sql) up to the php layer and back down again

@I'll-Be-Back you need to answer Aaron's excellent question too

@I'll-Be-Back can you give a clear answer to "why" you want this to be dynamic rather than coded into a view like the answer

@I'll-Be-Back that's pretty crucial information ;-)

:/

@I'll-Be-Back do you trust your admins not to attack your database with SQL injections?

if so you could look into some dynamic SQL on the database side and just store the formula as a SQL snippet

@JackDouglas the use of dynamic SQL does not mean it would have to be evaluated at the PHP layer. Can MySQL not construct dynamic SQL in a stored procedure?

@JackDouglas Yes. On the interview it will be some kind of dropdown and such. It would be pretty safe.

@AaronBertrand it can

@AaronBertrand that's what I was getting at

On the interface!!*

On the interface it will be some kind of dropdowns and such. It would be pretty safe.

you can edit your chat messages if you are quick enough :-)

@I'll-Be-Back so who is updating the formulas? the DBA?

me and my boss

and one of processing admin

One of them will be doing it

so how many formulas in this dropdown are you looking at having?

if theres a handful you could easily generate all the formulas in one view as multiple columns per category

I have replied to your comment

Hmmmm

I didnt thought of that

and then you would just select the formula name

depending on how many you have

this would be a alright aproach

approach*

@grid if the formulas are arbitrary and could change every month, we were thinking it could be done with dynamic sql instead - but if not, what you are suggesting is better in my view

and thanks for taking the time to join us in here - welcome! :)

now I need to hit the road...

again if you are just having dropdowns and have a set number of formulas

then I believe the view is the best way to do it.

but if you are changing formulas on the fly eval would probably be the next best thing.

@grid See this: jsfiddle.net/vqWMQ

Im on the right path?

ok

now i understand. You can just build that into your query when you select then

Yep

Need to set the full set of requirements up front. Folks are chasing a moving target.

$query = "SELECT id, ". $formula_string ." AS formula FROM items WHERE category_id = 1";

and $formula_string would be something like: (cost + value) / 100

I would use jQuery to duplicate the dropdown if needed

I think that would work

@I'll-Be-Back this looks hideously overcomplicated to me, are you really sure you need to be this generic? Over the lifetime of the application, how many different formulas are you expecting your admins to ever enter?

and if you must be this generic, why not just enter the formula as text and carefully sanitise it

overcomplicated? it is very easy

I can do formula as text but it wont be easy for staff

Dropdown will be easier and less problem.

I suppose I can do formula as text with validation check but that will take time.

Dropdown is easier.

When a google search for SQL Server help doesn't turn up DBA.SE or MSDN I know I'm in trouble

Why are all other SS sites I find so cheesy and sleazy at the same time?

@I'll-Be-Back I'm not talking about the interface so much as how you store the formula - with your dropdowns you will need a whole set of tables of 'elements' for the formulas, and a way of linking them together. Far too much effort for no apparent gain as far as I can tell!

Also I sincerely hope that if you win the eBook it's just a card that has "Enter to Win" written on it just like that

heh, i just got a spam mail with an offer to "SEO optimize dba.se"