Solved: PHP sum from MySQL

[Damonc]

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]

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]

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]

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]

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]

Just as a matter of interest Damon, what is the function of the number?

 

[Damonc]

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]

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]

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

 

[JiminSA]

Ha-ha, but isn't that life?!

 

[Damonc]

Yep - sure is!

 

[Damonc]

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..

		  		//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]

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]

Its Maria DB.. The dates are only stored as text, not in actual date fields..

 

[JiminSA]

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)