Page 1 of 1

Putting mathematical equations in MySQL.

Posted: Wed Apr 04, 2007 6:19 pm
by Mightywayne
Hello there. :D Here's my problem.

Example. I've got a column called "stamina" and a column called "constitution", base stats, long story. Anyway, to get someone's HP, I do

Code: Select all

$hp = $themon['stam'] * 2 + ($themon['con'] * .4);
Works well, everything's fine.

But what I would like to do is make it so columns could do that instantly for me anyway, and I wouldn't need to use a script to do it. That'd help organizational/effort issues greatly, actually, if it were possible.

And yes, I (like always!) attempted a google search, but I'm not even sure what exactly to search for. ;/

Edit: Eh, I'll just do it the old way. A bit messier, but it might even be easier on the server, I guess. ;(

Posted: Wed Apr 04, 2007 6:26 pm
by RobertGonzalez

Code: Select all

SELECT (`stam` * 2 + (`con` * 0.4)) AS hp FROM `mytable`;

Posted: Wed Apr 04, 2007 6:35 pm
by Mightywayne
but but, (that's good) but I need it in a column of its own. Like, the HP column automatically does that.

Posted: Wed Apr 04, 2007 6:42 pm
by RobertGonzalez
When you say column of its own, what do you mean?

Posted: Wed Apr 04, 2007 6:50 pm
by Mightywayne
Hmm, like an excel sheet, how it would have lots of columns, then rows. Like a table. I figure it'd be like, you put the equation in its own cell, and it'd take the info from other cells, to be that info in that cell.

Posted: Wed Apr 04, 2007 7:00 pm
by RobertGonzalez
So for each row you want that calc done, or for the entire data set?

Posted: Wed Apr 04, 2007 7:02 pm
by Mightywayne
Each row in the column would be done auto-calculated.

Posted: Wed Apr 04, 2007 8:37 pm
by jwalsh
Why not do the calculation on INSERT's and UPDATE's. Then it doesn't need to autocalculate, because the value is already there.

Posted: Wed Apr 04, 2007 9:45 pm
by Mightywayne
So you mean... when I insert the table itself into the DB, I add as a value, that equation, and it'll do it instantly for me?

Posted: Wed Apr 04, 2007 10:12 pm
by jwalsh
No, each time you insert a new row into the database, or change a row you do your calculation and store it in your extra column.

MySQL is not a spreadsheet, it won't keep a dynamic column. It's purpose is storing data, your job is using SQL to get the desired result.

Posted: Wed Apr 04, 2007 11:24 pm
by RobertGonzalez
Typically you do not want to store calculations of data that is in the same table.