1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: PHP sum from MySQL

Discussion in 'Web Design & Development' started by Damonc, Mar 15, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Hi,
    Hopefully a relatively quick question.. I need to add up some numbers from a mysql database (seems easy enough).. when someone adds numbers to the database it also adds their username into the table..

    How would you go about retrieving the list of users, and adding up each users totals?
     
  2. colinsp

    colinsp

    Joined:
    Sep 5, 2007
    Messages:
    2,290
    First Name:
    Colin
    Let us have your database table structure and we should be able to give you a complete answer.

    Simplistically do a group by username in your query to get all the user numbers together and then with a while loop add them together.
     
  3. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    I think I've worked out an alternative way of doing it.. but I'm now I'm having issues inserting into the database.. its gotta be right in front of me as I've done it several times before - I've probably just been starring at it too long


    The database layout probably isn't the best.. and I'm considering re-doing it as its starting to annoy me

    Column names are dates 16-03-2015 through to 12-04-2015 as well as a username field..

    I'm trying to get the php to update the date column in the database for the current date so a figure is entered and logged with the username..

    However as I'm typing this I'm realising the current layout is probably going to cause some problems.. as i probably should have had columns 'date' and 'username' and had php autofill the date..
     
  4. JiminSA

    JiminSA

    Joined:
    Dec 15, 2011
    Messages:
    3,386
    First Name:
    Jim
    If you are rethinking your db, may I suggest a table with 3 fields(columns) - the internal date as the index key; the username and the number. It should make things easier, programmatically ...
     
  5. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon


    Yep - pretty much exactly what I've done :)

    just changing what I had to work with the new setup
     
  6. JiminSA

    JiminSA

    Joined:
    Dec 15, 2011
    Messages:
    3,386
    First Name:
    Jim
    Just as a matter of interest Damon, what is the function of the number?
     
  7. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Some people at work (including myself) decided to do a 10,000 step challenge - you do 10,000 steps a day for a given period (we've decided on 4 weeks)..

    I wasn't able to find a site that offered the setup that we were after so decided to do one on my own (how hard could it be?)

    so I've got user logins done, users submit their daily step count via the website to the database, the site compares results generates graphs for them etc..

    Although as I'm working on it, I'm discovering more things that i need to cover.. the latest one being that if someone enters their steps at lunchtime, when they go back to add more later in the day, I need to setup the code to either delete or modify their original entry for the day.. Think I know how I'll do that..
     
  8. JiminSA

    JiminSA

    Joined:
    Dec 15, 2011
    Messages:
    3,386
    First Name:
    Jim
    Then your new structure will allow you to identify if a user has made an entry for a specific day and either update or insert accordingly ...
     
  9. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Yep.. I think the new structure is working much better :)

    ..Stupid thing is, the challenge starts tomorrow.. I've been procrastinating for 2 weeks and left almost all of it to the last minute lol
     
  10. JiminSA

    JiminSA

    Joined:
    Dec 15, 2011
    Messages:
    3,386
    First Name:
    Jim
    Ha-ha, but isn't that life?!
     
  11. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Yep - sure is!
     
  12. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Can I get you to have a quick look at this? I'm missing something as its not returning any data..

    I'm wanting to check for data against the users username for the previous day, then if theres data change a button accordingly.. the button is there, but as its not returning data (even tho it exists) the button doesn't change..


    PHP:
                      //catpure username
                    
    $mydata $_SESSION['user_name']; 
                      
    //set timezone
                      
    date_default_timezone_set('Australia\Sydney');
                    
    //date variables 
                    
    $today date("d-m-Y");
                    
    $day1 date('d-m-Y',strtotime("-1 days"));
                    
                      
    $host=""// Host name 
                    
    $username=""// Mysql username 
                    
    $password=""// Mysql password 
                    
    $db_name="jaefc"// Database name 
                    
    $tbl_name="stepdata"// Table name

                    
                    
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
                    
    mysql_select_db("$db_name")or die("cannot select DB");

                    
    $sql="SELECT * FROM `$tbl_name` WHERE username = "$mydata" AND date = '$day1'";
                    
    $result=mysql_query($sql);
                    if (
    $rows['stepcount'] > 0) {
                        echo 
    '<input type="submit" name="day" id=".btn" value="Modify Yesterday" class="standardbutton">';
                    } else {
                        echo 
    '<input type="submit" name="day" id=".btn" value="Yesterday" class="standardbutton">';
                    }
                    
    I know the 'mydata' variable works if I echo that out it works.. and so does the $day1.. but the whole thing doesn't find anything in the database even tho I have an entry there for yesterdays date (which it created)
     
  13. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    It should be possible to do this just with SQL.
    Can I ask what SQL engine (e.g. mySQL) you're running, as they all handle dates differently?
     
  14. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    656
    First Name:
    Damon
    Its Maria DB.. The dates are only stored as text, not in actual date fields..
     
  15. JiminSA

    JiminSA

    Joined:
    Dec 15, 2011
    Messages:
    3,386
    First Name:
    Jim
    It's probably your select. Ensure that the date is being held in the db, in exactly the same format as your $day variable (echo and compare, perhaps)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1144794

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice