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.

Expand|Select|Wrap|Line Numbers

 <? $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

Quote:

Originally Posted by w33nie

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

Like this, just SQL arithmetic:

Expand|Select|Wrap|Line Numbers

 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'>$variable1</td>");
print ("<td width='50' align='center'>$variable2</td>");
print ("<td width='50' align='center'>$variable3</td>");
print ("<td width='50' align='center'>$variable4</td>");
print ("<td width='50' align='center'>$variable5</td>");
print ("<td width='50' align='center'>$variable5a</td>");
print ("<td width='50' align='center'>$variable6</td>");
print ("<td width='50' align='center'>$variable7</td>");
print ("<td width='75' align='center'>$variable8</td>");
print ("<td width='75' align='center'>$variable9</td>");
print ("</tr>");
}[/php]

Ronald :cool:

How make value of field to be mathematical equation

re: How make value of field to be mathematical equation

re: How make value of field to be mathematical equation

re: How make value of field to be mathematical equation

re: How make value of field to be mathematical equation

re: How make value of field to be mathematical equation

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about