Putting mathematical equations in MySQL.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Mightywayne
Forum Contributor
Posts: 237
Joined: Sat Dec 09, 2006 6:46 am

Putting mathematical equations in MySQL.

Post 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. ;(
Last edited by Mightywayne on Thu Apr 05, 2007 2:34 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

SELECT (`stam` * 2 + (`con` * 0.4)) AS hp FROM `mytable`;
Mightywayne
Forum Contributor
Posts: 237
Joined: Sat Dec 09, 2006 6:46 am

Post by Mightywayne »

but but, (that's good) but I need it in a column of its own. Like, the HP column automatically does that.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

When you say column of its own, what do you mean?
Mightywayne
Forum Contributor
Posts: 237
Joined: Sat Dec 09, 2006 6:46 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So for each row you want that calc done, or for the entire data set?
Mightywayne
Forum Contributor
Posts: 237
Joined: Sat Dec 09, 2006 6:46 am

Post by Mightywayne »

Each row in the column would be done auto-calculated.
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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.
Mightywayne
Forum Contributor
Posts: 237
Joined: Sat Dec 09, 2006 6:46 am

Post 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?
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Typically you do not want to store calculations of data that is in the same table.
Post Reply