SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Team Build With Calculation

    I'd like to learn how to develop this project:
    1. Users log in to create a Team Name.
    2. After they log in, they choose from a list of available Activities that they participated in. Each Activity has a Point value attached to it. Single activities can be applied to the same Team multiple times, adding more and more Points to their profile.
    3. As the user adds activities to their profile, the 'Points' adds up, and a total is displayed.
    4. There will be a page for displaying an activity log with Dates so timestamps on record insertions will be required.
    5. Non registered users will be able to see a list of the Team names on the home page followed by their Total Points.

    My theory is using a one-to-many table relationship with the following:

    Table: TEAM_MEMBERS
    mem_id name email pwd
    1 Buttmunch email@email.com ********

    Table: ACTIVITIES
    act_id type points
    1 Bus Ride 2
    2 Carpool 4
    3 Ride Bike 9

    Table: TEAM_TOTAL
    team_id mem_id name total_points
    1 1 Buttmunch 15

    Table: ACTIVITY_LOG
    log_id mem_id date
    1 1 Date Timestamp

    Will this kind of system work?

    Thanks!
    toad78

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you betcha, yes it will

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    OK. Here's the tricky part that I don't know how to to do:
    How do I calculate the totals of the points in the activities chosen and have the totals post in the TEAM_TOTAL table?

    I'm sure it must be a simple addition, but I'm clueless.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, please show several rows of real data in each table, not the sample rows you made up

    do you also have a teams table?

    and what's a user profile?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I can't give you real user data, but I'll fudge something up and get back to you.

  6. #6
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by r937 View Post
    okay, please show several rows of real data in each table, not the sample rows you made up

    do you also have a teams table?

    and what's a user profile?
    OK. I had to make some modifications to the table. Attached is the document of how the tables are set up. What I need is how to calculate the total points from a team that is selected. For example, if someone selects 'Red Team', the page will come up with the listing of the activities the Red Team did (which I don't have a problem getting that information to display) with the total points that were earned from those activities (that's where I'm stuck). There will be many more activities listed with more points.

    Have any suggestions on how I should begin making these calculations?

    Thanks for looking into this for me.
    toad78
    Attached Files Attached Files

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    here's the data (no need to use a PDF for text) --
    Code:
    TABLE: activity 
    activityID  activityName  activityValue 
        1       Bicycling         10 
        2       Carpooling         5 
     
    TABLE: entry 
    entryID  date       teamID  activityID  
      1      2009-04-12    1        1 
      2      2009-04-12    2        2 
      3      2009-04-13    1        2  
      4      2009-04-13    2        1  
      5      2009-04-14    1        1 
      6      2009-04-14    2        2 
     
    TABLE: team 
    teamID  teamName 
      1     Blue Team  
      2     Red  Team
    okay, given a specific team, the following query returns that team's activities --
    Code:
    SELECT team.teamName
         , entry.date
         , activity.activityName
         , activity.activityValue
      FROM team
    INNER
      JOIN entry
        ON entry.teamID = team.teamID
    INNER
      JOIN activity
        ON activity.ID = entry.activityID
     WHERE team.teamID = 2
    if you are printing the results on a web page, you would use your application language (php or whatever) to calculate the total points

    you could also get the total points with SQL --
    Code:
    SELECT SUM(activity.activityValue) AS total_points
      FROM entry
    INNER
      JOIN activity
        ON activity.ID = entry.activityID
     WHERE entry.teamID = 2
    but this would either be a separate query, or you could "shoehorn" it into a UNION query with the first query --
    Code:
    SELECT teamName
         , date
         , activityName
         , activityValue
         , CASE WHEN activityName IS NULL
                THEN 'totals'
                ELSE 'details' 
            END AS sortkey
      FROM (
           SELECT team.teamName
                , entry.date
                , activity.activityName
                , activity.activityValue
             FROM team
           INNER
             JOIN entry
               ON entry.teamID = team.teamID
           INNER
             JOIN activity
               ON activity.ID = entry.activityID
            WHERE team.teamID = 2
           UNION ALL
           SELECT NULL
                , NULL
                , NULL
                , SUM(activity.activityValue) 
             FROM entry
           INNER
             JOIN activity
               ON activity.ID = entry.activityID
            WHERE entry.teamID = 2
           ) AS u
    ORDER
        BY sortkey
         , date
    helps?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thank you! YES! This does help!

    Why use JOIN? I was able to display the results without a JOIN. Does JOIN make a difference? Or is it a 'just-in-case-there's-a-blank-record' reason?

    Thanks for the SELECT SUM(); I was looking into that, but wasn't sure if I had to create other variables to get the calculation to work correctly.

    THANK YOU!
    toad78

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by toad78 View Post
    I was able to display the results without a JOIN.
    you were probably using the old style of joins, where the tables are listed in the FROM clause and the join conditions are all in the WHERE clause

    that's still a join

    switch over to using explicit JOIN syntax -- it's much better
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do explicit JOINs just consolidate the old method?

    Still learning.
    toad78

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that all depends on what you mean by "consolidate"

    you can't do outer joins with the old style syntax, for example

    at least, not without using the stilted (+) or * syntax developed by microsoft and oracle -- and i find it impossible to remember which is which, and whether it goes at the beginning or the end, or next to the equals sign

    with table1 LEFT OUTER JOIN table2, though, there is no such confusion
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    I originally used this method:
    Code:
    SELECT *
    FROM entry, team, activity
    WHERE entry.teamID = team.teamID AND entry.activityID = activity.activityID AND team.teamID = var1
    ORDER BY entry.`date`
    and it worked. I'll try your JOIN method so I can understand how JOIN works. I may have to start learning how to use this method because I do have some empty fields in records of my tables. As I understand it, JOINs help to take care of that problem when selecting filtered records.

    toad78

  13. #13
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    Excuse me for being the rookie here, but can you explain to me this section.

    Quote Originally Posted by r937 View Post
    Code:
    SELECT teamName
         , date
         , activityName
         , activityValue
         , CASE WHEN activityName IS NULL
                THEN 'totals'
                ELSE 'details' 
            END AS sortkey
      FROM (
           ...
           UNION ALL
           SELECT NULL
                , NULL
                , NULL
                , SUM(activity.activityValue) 
             FROM entry
           INNER
             JOIN activity
               ON activity.ID = entry.activityID
            WHERE entry.teamID = 2
           ) AS u
    ORDER
        BY sortkey
         , date
    I don't understand what 'CASE WHEN..., UNION ALL' section and the extra 'INNER JOIN activity' at the bottom. I also don't understand the 'ORDER by softkey'.

    I appreciate the time you put into this and could use a little education on this. I wouldn't mind using this sample if I could understand what many of the commands are for.

    Thank you again!
    toad78

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    when trying to figure out a complex query, always start at the "inside" and work your way out

    in this particular instance, start with the two SELECTs of the inner UNION query

    i explained how each of the SELECTs works in the first part of post #7, and then showed how they would be combined in the UNION

    the only part i did not mention is why the second SELECT needed the NULLs -- this was because of how UNIONs work, they have to have the exact same number of columns

    now, typically, whenever you have a query that returns both details and totals, you want to make sure that the totals row comes last, yeah?

    unfortunately, if we sort simply on date, then the totals row would come up first, becase NULL sorts ahead of all other values

    so we have to make the totals row come last, and in the outer query, that's what the CASE expression does -- it utilizes the fact that the totals row has NULL in the activityName column, while the details rows don't, to create an additional column in the outer query's result set

    this additional column, in effect, "tags" each of the rows produced by the inner UNION query

    then the final result is sorted on this column as the major sort key, with the result that all the details rows come first and the totals row comes last -- this is because the string value 'details' sorts ahead of the value 'totals'

    the secondary sort key, date, ensures that all the details rows are in date sequence

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for taking the time to explain that.

    Sheesh! Do I have a lot to learn!

  16. #16
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    How would this work:
    After the entries are totaled, how can we post them to a different table with the following:

    teamTotals
    teamID total_points
    1 345

    To break it down, there's a page that allows the team to choose the item they used (entries.php), and they click on an Insert button. I was wondering how after the Insert button is pressed that it not only posts the value to the 'entry' table (which has been solved), but also automatically calculates the total from the 'entry' table and Inserts/Updates (obviously the totals are going to be updated) the total in the 'teamTotals' table at the same time (that's the pickle)?

    We currently have this as our 'entries.php' page (I've cut back on the unnecessary code):
    PHP Code:
    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
      
    $insertSQL sprintf("INSERT INTO entry (entryID, `date`, teamID, activityID) VALUES (%s, %s, %s, %s)",
                           
    GetSQLValueString($_POST['entryID'], "int"),
                           
    GetSQLValueString($_POST['date'], "date"),
                           
    GetSQLValueString($_POST['teamID'], "int"),
                           
    GetSQLValueString($_POST['activityID'], "int")); 

    HTML Code:
      <table align="center">
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">Date:</td>
          <td><input type="text" name="date" value="" size="32" /></td>
        </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">TeamID:</td>
          <td><select name="teamID">
            <?php 
    do {  
    ?>
            <option value="<?php echo $row_team['teamID']?>" ><?php echo $row_team['teamName']?></option>
            <?php
    } while ($row_team = mysql_fetch_assoc($team));
    ?>
          </select>
          </td>
        </tr>
        <tr> </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">ActivityID:</td>
          <td><select name="activityID">
            <?php 
    do {  
    ?>
            <option value="<?php echo $row_activity['activityID']?>" ><?php echo $row_activity['activityName']?></option>
            <?php
    } while ($row_activity = mysql_fetch_assoc($activity));
    ?>
          </select>
          </td>
        </tr>
        <tr> </tr>
        <tr valign="baseline">
          <td nowrap="nowrap" align="right">&nbsp;</td>
          <td><input type="submit" value="Insert record" /></td>
        </tr>
      </table>
      <input type="hidden" name="entryID" value="" />
      <input type="hidden" name="MM_insert" value="form1" />
    The page then forwards (after the Insert button is pressed) to a different page showing that specific team their list of entries, value points, and total points on their profile page. Getting that specific team's total points isn't a problem, thanks to your help. Our major problem is getting the points total to post to a database after the 'Insert' button is pressed.

    Would I need to create the following and insert it after the first INSERT query? If so, after the query, how do I insert the total SUM into the 'teamTotals' table 'total_points' field?:

    $query_getTotal = sprintf("SELECT SUM(activity.activityValue) AS total_points FROM entry INNER JOIN activity ON activity.activityID = entry.activityID WHERE entry.teamID = %s", GetSQLValueString($var1_getTotal, "int"));

    Thank you again!

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why do you need a teamtotals table?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    We're going to list the top three teams on a web page.

    The teamTotals table will hold the team's id and their total points. We will pull information from that table, joining with the 'team' table and post it on a web page.

    I know how to do everything with the exception of getting the total_points to post on the teamTotals table and Insert/Update after the entry has been 'Insert'ed.

    toad78

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by toad78 View Post
    We're going to list the top three teams on a web page.
    Code:
    SELECT team_id
         , SUM(...) AS some_total
      FROM entry
    GROUP
        BY team_id
    ORDER
        BY some_total DESC LIMIT 3
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    Sorry. But I'm getting an error. Let me see if I can post the tables, and you can tell me if I got this right or not.

    Table: TEAM
    teamID teamName
    1 Buttmunch

    Table: activity
    activityID activityValue
    1 40

    Table: entry
    entryID teamID activityID
    1 1 1
    2 1 2

    OK. I have web page called 'teamtotals.php'. Using your method, I get an error, but I'll try to see if I can make sense of it.

    SELECT entry.team_id, SUM(activity.activityValue) AS some_total FROM entry,
    GROUP
    BY entry.team_id
    ORDER
    BY some_total DESC LIMIT 3

    Would this be right?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by toad78 View Post
    Would this be right?
    it would, once you remove the dangling comma in your FROM clause

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    DOH!

    OK. I'll give this a try and let you know within an hour (sweating with pressure).

  23. #23
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    I have this:

    SELECT entry.teamID, team.teamName, SUM(activity.activityValue) AS some_total
    FROM entry, team, activity
    WHERE entry.teamID=team.teamID
    GROUP BY entry.teamID
    ORDER BY some_total DESC LIMIT 3

    but am still receiving a bloated number in some_total. It's displaying the correct team and id.
    Last edited by toad78; May 11, 2009 at 08:47.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    dude, now you have a dangling comma in front of the FROM clause

    here's your last query, re-written with the leading comma convention --
    Code:
    SELECT entry.teamID
         , SUM(activity.activityValue) AS some_total
         , 
      FROM entry
         , activity
    GROUP 
        BY entry.teamID
    ORDER 
        BY some_total DESC LIMIT 3
    can you see the dangling comma more easily now?

    you're still not out of the woods yet, because now you have a bad join -- when you remove the dangling comma, you'll find that the totals are grossly inflated
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by r937 View Post
    you're still not out of the woods yet, because now you have a bad join -- when you remove the dangling comma, you'll find that the totals are grossly inflated
    You're right. (sigh). At this point I would need to join all of the teamIDs in order to group them and sort them?


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
  •