FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL Calculated field





welshsteve
Hi everyone, is it possible to have a calculated field in a MySQL database?

Basically, I want the value of a particular field to be calculated based on values of other fields in a record.

Three columns: "Pld", "W", "Percentage"

I need the "Percentage" field to be automatically calculate from the other two fields i.e. W/Pld * 100, to give me the percentage of wins.

Is this possible? Or is there a way in PHP that I can pull the data from the database, calculate the percentage, and then order it again.

Currently, this is what I am doing.

Code:

<?php
$con = mysql_connect("SERVER","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Please contact your web developer');
  }

mysql_select_db("DATABASE", $con);

$sql = "
SELECT * FROM tablename WHERE Pld > 0 ORDER BY W DESC , Percentage DESC , PlayerName";

$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table class='tab'>
<tr>
<th class='team'>Player</th>
<th>Pld</th>
<th>W</th>
<th>L</th>
<th>%</th>
</tr>";
       
while($row = mysql_fetch_array($result))
  {
    echo "<tr>";
    echo "<td class='team'>&nbsp;" . $row['PlayerName'] . " (" . $row['Team'] . ")</td>";
    echo "<td>" . $row['Pld'] . "</td>";
    echo "<td>" . $row['W'] . "</td>";
    echo "<td>" . ($row['Pld'] - $row['W']) . "</td>";
    echo "<td>" . ($row['W'] / $row['Pld'] * 100) . "</td>";
  echo "</tr>";
  }
echo "</table>";
mysql_close($con);
?>

I need to calculate the % for each record, then order the records in the same way as I've asked the SQL data to be ordered. I have to manually enter the percentages in the database at the moment to get it to work.
rvec
Code:
SELECT *, (W/Pld*100) as Percentage FROM tablename WHERE Pld > 0 ORDER BY W DESC , Percentage DESC , PlayerName


and remove the column Percentage from the table.
welshsteve
UPDATE

OK, I have the below now

SELECT *, W/Pld*100 as Perc FROM tablename WHERE Pld > 0 ORDER BY W DESC , Perc DESC , PLD , PlayerName

This works. I then got rid of the excessive decimal places with this.

Code:

echo "<td>" . round($row['Perc'],2) . "%</td>";
rvec
ah yeah that's what I meant Razz
And I've got another thing for you Smile
http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round
works the same as the php function Smile
welshsteve
rvec wrote:
ah yeah that's what I meant Razz
And I've got another thing for you Smile
http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round
works the same as the php function Smile


Thanks, very useful Smile
Related topics
Build an online dictionary by PHP/MySQL
MySQL statement error (got me going)
PHP Tutorial: Basic Shoutbox w/ MySQL
MySQL DB Backup script - minor problem
Inserting data to MYSQL with a PHP script
Defalut Value in MySql
Help with mysql query
phpBB Error, seems to be a MYSQL DB error...
What is useful way in this php+mysql problem?
Working with Excel, PHP & MySQL. Any Ideas
Need help understanding MySQL "Resource" type retu
mySQL - Day, date, month, year, time Output/Input
can we increase mysql field size more than 255 cahrs
Best way to check if BINARY mysql field is zero?
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.