Add numbers in a row

[gbodave]

I have a cybergolf website where players report their round scores one at a time. So for four scores the form has to be filled out and submitted four seperate times.

I have a MySQL database with the following fields.

name, round 1, round 2, round 3, round 4, total

I want to display on the web page a table with headings of

name, round 1, round 2, round 3, round 4 and total. The total field needs to be calculated by the db as this information is not input by the player.

I have successfully got to the point of displaying the correct data for name, round1, round 2, round 3 and round 4 (e.g. dave, 68,67,72,69) but I need to know how to display the total of the scores for each player - 276 in the above example.

Any help please.

[SPMuthu]

update 'table' set total="round1+round2+round3+round4" where name = "value"
Otherwise,
just
r1=select round1 from 'table' where name = "value"
r2=select round2 from 'table' where name = "value"
r3=select round3 from 'table' where name = "value"
r4=select round4 from 'table' where name = "value"
assign it on some variables, then
r=r1+r2+r3+r4
update 'table' set total=r where name="value"
Otherwise, I think you can use query within query.
that is what i get from my knowledge.
More suggestions are welcome and I too eager to know this one particularly for my project. Thanks.

[guido2004]

SELECT
    name
  , round1
  , round2
  , round3
  , round4
  , round1+round2+round3+round4 AS total
FROM table

No need to store the total in the database, because you can easily calculate it.

Are you sure you'll never want to store more than 4 rounds?

[gbodave]

because you can easily calculate it.

That's the big issue. I don't want to calculate it, that would defeat the idea of automating this whole thing. Players submit their scores - they don't want to calculate the total either.

I never want more than 4 rounds per player but obviously if 20 players report scores I would have 20 rows.

As I said I'm getting the individual round scores coming through just fine but the total is always "0" because I can't figure how to get that field calculated.


SPmuthu - thanks but as a newbie I don't follow much of what you are saying. Why do you introduce "value" into the mix.

[SPMuthu]

Actually I meant the actual 'username' with the word 'value'
example,
user="alex"

[guido2004]

That's the big issue. I don't want to calculate it, that would defeat the idea of automating this whole thing.

Sorry, I don't understand.
What I'm saying is, there is no need to store the total in the database, because you already have all the single values that make up the total.
So when you retrieve the data from the database to show it (or whatever), all you need to do is calculate the total in the SELECT clause, like I did in my query.

If you really want to have it in the table, you could use an update:

UPDATE tablename
SET total = round1 + round2 + round3 + round4
WHERE name = 'value'

[guido2004]

Guido, the penny dropped and I understood what you were saying !

That's good

Now I have the total of the number of strokes in addition to the individual strokes but it's displaying in the wrong place in my html table.

Need to look at this. Probably will return.

Don't hesitate to return. We'll be here (well, maybe not me, but someone for sure ).

[gbodave]

OOps. Must have been looking somewhere else. The total is NOT displaying in my web page.

Is that because I need a echo statment in the relevant html table cell. If so what would that be.

It might help if I show my page code here.

<?php require_once('Connections/scores.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
Guido's code starts here
$sql = "SELECT\n"
. " player_name\n"
. " , r1\n"
. " , r2\n"
. " , r3\n"
. " , r4\n"
. " , r1+r2+r3+r4 AS total\n"
. "FROM scores LIMIT 0, 30 ";
mysql_select_db($database_scores, $scores);
$query_Recordset1 = "SELECT player_name, r1, r2, r3, r4 FROM scores";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $scores) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>CSS Page Builder Magic: Layout 2</title>
<script type="text/javascript" src="p7csspbm/p7mobile.js"></script>
<link href="p7csspbm/p7csspbm_02.css" rel="stylesheet" type="text/css" />
<link href="p7csspbm/p7csspbm_print.css" rel="stylesheet" type="text/css" media="print" />
<!--[if lte IE 7]>
<style>
.menutop li {display: inline;}
div, .menuside a {zoom: 1;}
.masthead .banner {width: 100%;}
</style>
<![endif]-->
</head>

<body>
<div class="content-wrapper">
<div class="masthead">
<div class="logo">
<h1>Logo Placeholder</h1>
</div>
<div class="banner"><img src="p7csspbm/img/main-picture.jpg" alt="" /></div>
</div>
<p>&nbsp;</p>
<div class="columns-wrapper">
<div class="content">
<form action="FTGsettings/gboscores.php" method="post" enctype="multipart/form-data" name="scoresreport" id="scoresreport">
<legend><br />
</legend>
<table width="95%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="top"><table width="80%" border="1" align="center" cellpadding="0" cellspacing="0">
<tr>
<th colspan="2">GBO round reporter </th>
</tr>
<tr>
<td>Player name</td>
<td><input name="playername" type="text" id="playername" size="45" maxlength="45" /></td>
</tr>
<tr>
<td>GBO number </td>
<td><input name="gbonumber" type="text" id="gbonumber" size="10" maxlength="10" /></td>
</tr>
<tr>
<td>Your e-mail address </td>
<td><input name="email" type="text" id="email" size="45" maxlength="45" /></td>
</tr>
<tr>
<td>Course played </td>
<td><input name="course" type="text" id="course" size="45" maxlength="45" /></td>
</tr>
<tr>
<td>Round played </td>
<td><label for="select"></label>
<select name="select" size="1" id="select">
<option>Select below</option>
<option>R1</option>
<option>R2</option>
<option>R3</option>
<option>R4</option>
</select></td>
</tr>
<tr>
<td>Score</td>
<td><input name="score" type="text" id="score" size="3" maxlength="3" /></td>
</tr>
<tr>
<td>Attach game file</td>
<td><input type="file" name="file" /></td>
<td><label for="Submit"></label></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit" />
<input name="reset" type="reset" id="reset" value="Reset!" /></td>
</tr>
</table></td>
</tr>
</table>
</form>
<p>&nbsp;</p>
<p>&nbsp;</p>
<?php do { ?>
<table width="610" border="1" align="center">
<tr>
<td width="91"><?php echo $row_Recordset1['player_name']; ?></td>
<td width="169"><?php echo $row_Recordset1['r1']; ?></td>
<td width="73"><?php echo $row_Recordset1['r2']; ?></td>
<td width="75"><?php echo $row_Recordset1['r3']; ?></td>
<td width="73"><?php echo $row_Recordset1['r4']; ?></td>
<td width="89"></td> html table cell where total needs to go
</tr>

</table>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
<h1 class="page-topper">&nbsp;</h1>
<h1 class="page-topper">&nbsp;</h1>
</div>
</div>
<div class="footer">
<p class="copyright">&nbsp;</p>
</div>
</div>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

[guido2004]

Please put the appropriate code tags around any code you post. It makes it so much easier to read.

$sql = "SELECT\n"
. " player_name\n"
. " , r1\n"
. " , r2\n"
. " , r3\n"
. " , r4\n"
. " , r1+r2+r3+r4 AS total\n"
. "FROM scores LIMIT 0, 30 "; 

What are all those \n and concatenations? You can format a query on multiple lines like this (makes it easier to read):

$sql = "
  SELECT
      player_name
    , r1
    , r2
    , r3
    , r4
    , r1+r2+r3+r4 AS total
  FROM scores
  LIMIT 0, 30 
"; 

But anyway, in your code you never execute that query. Instead, you execute this one:

$query_Recordset1 = "SELECT player_name, r1, r2, r3, r4 FROM scores"; 

So right now, there is no total to display at all.
Unless you calculate it in PHP.

[gbodave]

I deleted this

$query_Recordset1 = "SELECT player_name, r1, r2, r3, r4 FROM scores";

and put this in its place

$query_Recordset1 = " SELECT player_name , r1 , r2 , r3 , r4 , r1+r2+r3+r4 AS total FROM scores LIMIT 0, 30 ";

and got this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1

[guido2004]

$query_Recordset1 = " SELECT player_name , r1 , r2 , r3 , r4 , r1+r2+r3+r4 AS total FROM scores LIMIT 0, 30 ";

and got this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1

It looks like that error is coming from another query. The one you posted has "LIMIT 0, 30" while the error says "LIMIT 0, 10".