# Solved: PHP sum from MySQL



## Damonc (May 9, 2001)

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 (Sep 5, 2007)

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 (May 9, 2001)

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 (Dec 15, 2011)

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 (May 9, 2001)

JiminSA said:


> 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 (Dec 15, 2011)

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


----------



## Damonc (May 9, 2001)

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 (Dec 15, 2011)

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 (May 9, 2001)

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 (Dec 15, 2011)

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


----------



## Damonc (May 9, 2001)

Yep - sure is!


----------



## Damonc (May 9, 2001)

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 '';
				} else {
					echo '';
				}
```
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 (Apr 11, 2009)

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 (May 9, 2001)

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


----------



## JiminSA (Dec 15, 2011)

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)


----------



## Damonc (May 9, 2001)

JiminSA said:


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


I thought that initially..

its stored in the database as 14-03-2015

the $day1 variable returns the exact same..


----------



## JiminSA (Dec 15, 2011)

Just noticed your select - you have unnecessary quotes around your php variables - so it would be trying to find the literals $mydata and $day1 (plus you've broken the select statement with the use of double quotes around $mydate)


----------



## Damonc (May 9, 2001)

So.. should be more like this?


```
$sql="SELECT * FROM `$tbl_name` WHERE username = $mydata AND date = $day1"
```
I've been staring at this thing pretty much all day trying to get it finished.. which I really dont think is helping me lol


----------



## Damonc (May 9, 2001)

OK so.. I just ditched the variables, typed in exactly what I wanted and it still didn't return anything.. pasted the code into a blank php doc (just to make sure nothing was conflicting with it).. and same thing..

Wonder if the database service itself is playing up..


----------



## Ent (Apr 11, 2009)

I haven't got the database to test it, but as I said SQL alone should do it. 
To get a count of everyone, something like 
SELECT COUNT(*), username
FROM 
(SELECT DISTINCT date, username FROM table)
GROUP BY username

Or to just get a count of the one person, identified by the variable un, something like
SELECT COUNT(*)
FROM (SELECT DISTINCT date FROM table WHERE username=un)

Actually, I think I've misunderstood your structure. If so, never mind.


----------



## colinsp (Sep 5, 2007)

You need your php variables in single quotes in php so


```
$sql="SELECT * FROM '$tbl_name' WHERE username = '$mydata' AND date = '$day1'"
```


----------



## JiminSA (Dec 15, 2011)

woops my bad. Colin is right single quotes does the trick ...


----------



## Damonc (May 9, 2001)

Thanks guys.. still not working, I'm just rebooting the server thats running Apache/MySQL.. wondering if that has something to do with nothing being found..


----------



## colinsp (Sep 5, 2007)

Make sure you have some dummy data for testing you can always delete it before go live.


----------



## Damonc (May 9, 2001)

colinsp said:


> Make sure you have some dummy data for testing you can always delete it before go live.


Yeah I've got a line in there that I'm trying to get this bit of code to return for me.. but just doesn't want to seem to have a bar of it..


----------



## JiminSA (Dec 15, 2011)

Try changing your column name from "date" to, say "input_date" - I seem to remember that "date" is a keyword in sql


----------



## Damonc (May 9, 2001)

JiminSA said:


> Try changing your column name from "date" to, say "input_date" - I seem to remeber that "date" is a keyword in sql


OK.. I'll change it and report back shortly


----------



## Damonc (May 9, 2001)

Nope.. No change


----------



## JiminSA (Dec 15, 2011)

Take a screen shot of your structure in phpMyadmin and post it please Damon ...


----------



## JiminSA (Dec 15, 2011)

woops - and show us the current select you are using, please ...


----------



## Damonc (May 9, 2001)

OK.. screenshot of the table structure is attached - there's other tables in the database, and they work ok..so i know its not the database overall.

and heres the code.. I simplified the WHERE condition just until things start working.. 

```
<?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="localhost"; // Host name 
				$username=""; // Mysql username 
				$password=""; // Mysql password 
				$db_name="jaefc"; // Database name 
				$tbl_name="stepdata"; // Table name

				// Connect to server and select database.
				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 recdate = '$day1'";
				$result=mysql_query($sql);

				echo $rows['stepcount'];
				echo $rows['recdate'];

				?>
```


----------



## Damonc (May 9, 2001)

and heres a screenshot showing the data in the table..


----------



## JiminSA (Dec 15, 2011)

Ha-ha - we didn't set $rows up! Hence empty echo's


----------



## Damonc (May 9, 2001)

Wheres something hard I can hit my head against... repeatedly..


----------



## JiminSA (Dec 15, 2011)

I know that feeling well my mate!


----------



## Damonc (May 9, 2001)

OK I added this.. but I think Im missing something (aside from a caffine hit haha)


```
while($rows=mysql_fetch_array($result)){
...
}
```


----------



## JiminSA (Dec 15, 2011)

I presume that "..." represents the echoes which you've re-positioned??


----------



## Damonc (May 9, 2001)

Yep - thats correct.


----------



## JiminSA (Dec 15, 2011)

... and still nothing displays!? That is most strange! Can you re-post your script, please?


----------



## Damonc (May 9, 2001)

I just re-wrote it... I think its working.. just want to check a few different ways..


----------



## Damonc (May 9, 2001)

yep - its working.. with the full WHERE conditions...


----------



## Damonc (May 9, 2001)

I Owe you yet again 

Thanks to the others that helped out as well... I was going crazy, had just been looking at all this code for waaay to long today I think..


----------



## JiminSA (Dec 15, 2011)

"... wood for the trees". Glad we could help ...


----------

