Solved: PHP sum from MySQL

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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?
 

colinsp

Colin
Joined
Sep 5, 2007
Messages
2,348
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.
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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..
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
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 ...
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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 ...


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

just changing what I had to work with the new setup
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
Just as a matter of interest Damon, what is the function of the number?
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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..
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
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 ...
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
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)
 

Ent

Josiah
Retired Trusted Advisor
Joined
Apr 11, 2009
Messages
5,467
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?
 

Damonc

Damon
Thread Starter
Joined
May 9, 2001
Messages
667
Its Maria DB.. The dates are only stored as text, not in actual date fields..
 

JiminSA

Jim
Joined
Dec 15, 2011
Messages
3,407
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)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top