SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Add numbers in a row

    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.

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by SPMuthu; Jun 14, 2011 at 00:31. Reason: spelling mistakes

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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?

  4. #4
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2011
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually I meant the actual 'username' with the word 'value'
    example,
    user="alex"

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by gbodave View Post
    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:
    Code:
    UPDATE tablename
    SET total = round1 + round2 + round3 + round4
    WHERE name = 'value'

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by gbodave View Post
    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 ).

  8. #8
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);
    ?>

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Please put the appropriate code tags around any code you post. It makes it so much easier to read.

    PHP Code:
    $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):
    PHP Code:
    $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:
    PHP Code:
    $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.

  10. #10
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by gbodave View Post
    $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".


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •