How make value of field to be mathematical equation

[w33nie]

My table is pretty well complete, but I would prefer it if the value for Points could be turned into a mathematical equation,
and this equation would use the data from the other fields in the table to achieve a value.

I want this done in phpMyAdmin 2.7, if possible, so that when I change the values in other fields, the Points variable changes, due to its equation.

The equation I would want would be something like:
"won * 3 + drawn * 1"
(won and drawn are 2 other fields in my database)
and have this equation become the value of my Points field.

and would it be possible to have more than one variable as an equation?


I doubt you'll need the php code from my page, but here it is anyway.

<? $hostname = "***.secureserver.net"; // The mySQL DB server. 
$username = "acssl_table"; // The username you created for this database. 
$password = "Victoria3"; // The password you created for the username. 
$usertable = "table"; // The name of the table you made. 
$dbName = "acssl_table"; // This is the name of the database you made. 
 
MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$dbName") or die( "Unable to select database"); 
?> 
<? 
//error message (not found message)begins 
$XX = "No Record Found, to search again please close this window"; 
//query details table begins
$query = mysql_query("SELECT * FROM `table` ORDER BY points DESC, g_difference DESC, g_for DESC LIMIT 0, 30 "); 
while ($row = @mysql_fetch_array($query)) 
{ 
$variable1=$row["team"];
$variable2=$row["played"]; 
$variable3=$row["won"]; 
$variable4=$row["drawn"];
$variable5=$row["lost"];
$variable6=$row["g_for"]; 
$variable7=$row["g_against"]; 
$variable8=$row["g_difference"]; 
$variable9=$row["points"];
//table layout for results 
 
print ("<tr>");
print ("<td width='150'><span class='BodyTable'>$variable1</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable2</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable3</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable4</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable5</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable6</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable7</span></td>");
print ("<td width='75' align='center'><span class='BodyTable'>$variable8</span></td>");
print ("<td width='75' align='center'><span class='BodyTable'>$variable9</span></td>");
print ("</tr>"); 
}
//below this is the function for no record!!
if (!$variable1)
{ 
print ("$XX");
} 
//end 
?>

[ronverdonk]

Simply: you cannot.

A database is to hold (atomic) data and is not a replacement for a spreadsheet.

Any calculations on that data can be done using an SQL statement with the builtin functions..

Ronald :cool:

[w33nie]

how would I be able to get those fields to become math equations then?

[ronverdonk]

how would I be able to get those fields to become math equations then?

Like this, just SQL arithmetic:

mysql> select * from test;
+------+--------+------+-------+
| id   | team   | won  | drawn |
+------+--------+------+-------+
|    1 | Team 1 |    3 |     2 |
|    2 | Team 2 |    4 |     0 |
|    3 | Team 3 |    0 |     4 |
+------+--------+------+-------+
3 rows in set (0.00 sec)
 
mysql> select team, (won*3+drawn*1) as total from test;
+--------+-------+
| team   | total |
+--------+-------+
| Team 1 |    11 |
| Team 2 |    12 |
| Team 3 |     4 |
+--------+-------+
3 rows in set (0.00 sec)

Ronald :cool:

[w33nie]

how would i implement that into my php script?

i really have no idea where to start or what to do.

[ronverdonk]

Starting at the $query, the code would be as follows. Notice

(1) the change in the SELECT statement
(2) that $variable5a holds the calculated field 'total_points'.

[php]$query = mysql_query("SELECT *, (won*3+drawn*1) as total_points FROM `table` ORDER BY total_points DESC, g_difference DESC, g_for DESC LIMIT 0, 30 ");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["team"];
$variable2=$row["played"];
$variable3=$row["won"];
$variable4=$row["drawn"];
$variable5=$row["lost"];
$variable5a=$row['total_points'];
$variable6=$row["g_for"];
$variable7=$row["g_against"];
$variable8=$row["g_difference"];
$variable9=$row["points"];
//table layout for results

print ("<tr>");
print ("<td width='150'><span class='BodyTable'>$variable1</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable2</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable3</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable4</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable5</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable5a</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable6</span></td>");
print ("<td width='50' align='center'><span class='BodyTable'>$variable7</span></td>");
print ("<td width='75' align='center'><span class='BodyTable'>$variable8</span></td>");
print ("<td width='75' align='center'><span class='BodyTable'>$variable9</span></td>");
print ("</tr>");
}[/php]

Ronald :cool: