Jump to content

Sum Values by Day

hkalan2007

By hkalan2007
in PHP Coding Help

 Share

Recommended Posts

hkalan2007 Newbie

hkalan2007

Posted
Posted

Hello, I have read tutorials, searched forums for 2 weeks, and not found the assistance I require. I now jump in to ask my questions with the assistance from those much wiser then I...

 

I am new to this arena, so I honor your understanding to my situation.

 

I have 2 Solar Inverters that I have at my home, and I have a RS232 data port on each that is now connected to my home network via an IP converter to upload the data of the power they make each hour to a simple website I made, so I can see what is going on durring the hours I am at work. The information goes to my FTP site and the data falls into the MySQL table perfectly. I am trying to get the PHP and MySQL code to list the data into the forms/tables on the web page.

 

For this project I have set up Dreamweaver CS5 and a test server on my home PC, to get this working. All the data on the web host MySQL table is the same as in the test server (other then the host, name, and password but it works fine).

 

I have a very basic MySQL Named "inverters", and the table below named "feed"...

 

+---------+----------+------------+---------+------------+----------+

| Unit ID | Date | Time | Power | Volts | Current |

+---------+----------+------------+---------+------------+----------+

| 1 |YYYY-MM-DD| 12:00:00 | 560 | XXXX | XXXX |

+---------+----------+------------+---------+------------+----------+

| 1 |YYYY-MM-DD| 13:00:00 | 565 | XXXX | XXXX |

+---------+----------+------------+---------+------------+----------+

| 1 |YYYY-MM-DD| 14:00:00 | 890 | XXXX | XXXX |

+~~~~~~+~~~~~~+~~~~~~~~+~~~~~~+~~~~~~~+~~~~~~~+

 

Etc....

 

The 'Time' has 24 rows for each day, each 'Date" shows the same date for each of the 24 rows of 'Time".

 

The 'Power', is assigned as double the Volts and Current are "float".

 

I am able to SUM the Total "Power" of all the "Power" Rows with...

 

<?

$con = mysql_connect("localhost","NAME","PASWORD");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("inverters", $con);

$qry = mysql_query(" SELECT SUM(Power) AS total FROM feed ");

$row = mysql_fetch_assoc($qry);

echo $row['total'];

mysql_close($con);

?>

 

I have been trying for weeks to get "the CURRENT DAY power", Week Power, the month power etc to show the data on the webpage with no luck.

 

A very simple table on the website... As seen on the website.

 

+-----------+----------------+-----------------+-----------------+

| Today | This Month | This Year | Lifetime Power |

+-----------+----------------+-----------------+-----------------+

|????????| ??????????|???????????|----working----|

+-----------+----------------+-----------------+-----------------+

 

For the Current Day, I have tried this, but it does not work...

 

$query = mysql_query("SELECT(date), SUM(Power) AS 'day_total' FROM feed WHERE (date) = CURDATE() GROUP BY (date) ORDER BY (date) ");

$result = mysql_fetch_array($query);

$daytotal = $result["day_total"];

echo $row['day_total'];

mysql_close($con);

 

For the Month, I have tried this, but it does not work...

 

$query = mysql_query("SELECT MONTH(date), SUM(Power) AS month_total FROM feed GROUP BY Month(date)");

$result = mysql_fetch_array($query);

echo $row['month_total'];

mysql_close($con);

 

For the YEAR, I have tried this, but it does not work...

 

$query = mysql_query("SELECT date, SUM(Power) AS total FROM feed GROUP BY date");

$result = mysql_fetch_array($query);

echo $row['year_total'];

mysql_close($con);

 

I am reading and reading. Online I see how to get user names, and the time they register, and how many 'visitor' hits per year or month etc, but none have been informative to my situation. I think the more I read on the PHP and MySQL website manual, the more stupid I have become...

 

Using Google to seach "how to get the SUM of Current Date" only showes me how to enter the current date on a webpage or in a table row, not how to SUM the values of a table of the current day.

 

I also want to have a PHP graph (I have PHPgraphLIB now) on another page, and control the graph images by using a simple "Date Picker" or calendar, but that ran into the same type of issues as anything I write does not generate or show the results. I spent days using Prototype.js calendars and AJAX to connect the server side to the graphs and calendar etc..., but that is a different issue... hahaha

 

I would greatly honor any assistance to my learning and functioning of this.

 

Thank You,

 

Alan

 

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/
Share on other sites
Adam Advanced Member

Adam

Posted
Posted

For this day:

select sum(power) from feed where `date` = curdate()

 

For this week:

select sum(power) from feed
where week(`date`) = week(curdate())
and year(`date`) = year(curdate());

 

For this month:

select sum(power) from feed
where month(`date`) = month(curdate())
and year(`date`) = year(curdate());

 

For this year:

select sum(power) from feed where year(`date`) = year(curdate());

 

I've not got suitable data laying around to test these, but they should work fine. To get the sum for a particular day/week/month/year, not the current, you can just pass the value like this:

 

select sum(power) from feed
where month(`date`) = month('2010:01:01')
and year(`date`) = year('2010:01:01')

 

Also you'll notice the back ticks (`) around the date field name; that's because 'date' is a reserved word in MySQL, as is 'time', that you should avoid naming tables to prevent any ambiguity.

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1130665
Share on other sites
  • 2 weeks later...
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Thanks so much for the info... I got everything going in the single cels on the page.

 

My next task is to use 2 jquery datepickers select a start and end date to then post report.php and make a report tapble on the website with the selected daterange. You can see my meaning at http://www.pvmonitor.000a.biz/sw-report.php

 

I seem to have the same issues getting the PHP written right. Here is what I am trying to get to work right.

 

<?
$start = (isset($_POST['start'])) ? date("Y-m-d",strtotime($_POST['start'])) : date("Y-m-d");
$end   = (isset($_POST['end'])) ? date("Y-m-d",strtotime($_POST['end'])) : date("Y-m-d");
$con = mysql_connect('localhost', 'root', 'XXXXXXXX); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("inverters", $con); 

$sql='select * FROM feed AS genreport '  
.'BETWEEN [start] and [end]'; 

$res = mysql_query($sql); 
while ($row = mysql_fetch_assoc($res)) 
echo $row['genreport']; 
echo "<table border='1'> 
<tr> 
<th>Date</th> 
<th>Hour</th> 
<th>Power</th> 
<th>Volt</th> 
<th>Current</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['Date'] . "</td>"; 
  echo "<td>" . $row['Time'] . "</td>"; 
  echo "<td>" . $row['Power'] . "</td>"; 
  echo "<td>" . $row['Volt'] . "</td>"; 
  echo "<td>" . $row['Current'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>"; 

mysql_close($con); 
?>

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134328
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hello,

 

I made the changes in the PHP to be...

 

<?
$start = (isset($_POST['start'])) ? date("Y-m-d",strtotime($_POST['start'])) : date("Y-m-d");
$end   = (isset($_POST['end'])) ? date("Y-m-d",strtotime($_POST['end'])) : date("Y-m-d");
$con = mysql_connect('localhost', 'root', 'mackie1604'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("inverters", $con); 

$sql = "select * FROM feed AS genreport wWHER `date` BETWEEN '{$start}' and '{$end}'";
$res = mysql_query($sql); 
while ($row = mysql_fetch_assoc($res)) 
echo $row['genreport']; 
echo "<table border='1'> 
<tr> 
<th>Date</th> 
<th>Hour</th> 
<th>Power</th> 
<th>Volt</th> 
<th>Current</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['Date'] . "</td>"; 
  echo "<td>" . $row['Time'] . "</td>"; 
  echo "<td>" . $row['Power'] . "</td>"; 
  echo "<td>" . $row['Volt'] . "</td>"; 
  echo "<td>" . $row['Current'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>"; 

mysql_close($con); 
?>

 

Now I need to wrap my head around the Jquery to make it show in the POST

 

 

<script type="text/javascript">			
    $(document).ready(function () {
       
    var start1 = $('#start1');      
	var end1 = $('#end1');       

	start1.datepicker({ onClose: clearEndDate });      
	end1.datepicker({ beforeShow: setMinDateForEndDate });       

	function setMinDateForEndDate() {          
	var d = start1.datepicker('getDate');          
	if (d) return { minDate: d }      
	}      
	function clearEndDate(dateText, inst) {          
	end1.val('');      
	}  
	$('#button').click(function(dateText, inst) {
	})  
      $.post('report1.php', {start: start1.val(), end: end1.val()}, function(data) {
	    $('#genreport').html(data);
  });	

});		
</script>

 

 

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134482
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hello,

 

Here is what PHP script should do from a button click function on the web page (this topic is 100% about PHP that starts from a JQuery fuction)

 

The botton is clicked and takes 2 dates from a "start" and "end" datepicker, then calls the report.php file to go to work...

 

$('#button').click(function(dateText, inst) {
})  
$.post('report.php', {start: start1.val(), end: end1.val()}, function(data) ;
$('#genreport').html(data);

 

The report.php file see's the [start] and [end] date with this code, and puts the date in a format the PHP/MySQL can read.

 

$start = (isset($_POST['start'])) ? date("Y-m-d",strtotime($_POST['start'])) : date("Y-m-d");
$end   = (isset($_POST['end'])) ? date("Y-m-d",strtotime($_POST['end'])) : date("Y-m-d");

 

We tell what database and the login information is as so

 

$con = mysql_connect('localhost', 'root', 'mackie1604'); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 
mysql_select_db("inverters", $con);

 

The sql is told to select the rows and sort between the dates that come from the jquery datepickers. I have changed it to show the rows I want. The "Between" is telling what date range [start] and [end] needs to be selected.

 

$sql = 'SELECT `date`, time ,power FROM feed AS genreport BETWEEN [start] AND [end]';
$res = mysql_query($sql); 
while ($row = mysql_fetch_assoc($res))

 

The last thing is that the PHP will show the data in a tidy table on the webpage as this, and then I must have the close statement to finish.

 

echo $row['genreport']; 
echo "<table border='1'> 
<tr> 
<th>Date</th> 
<th>Hour</th> 
<th>Power</th> 
<th>Volt</th> 
<th>Current</th> 
</tr>"; 

while($row = mysql_fetch_array($result)) 
  { 
  echo "<tr>"; 
  echo "<td>" . $row['Date'] . "</td>"; 
  echo "<td>" . $row['Time'] . "</td>"; 
  echo "<td>" . $row['Power'] . "</td>"; 
  echo "<td>" . $row['Volt'] . "</td>"; 
  echo "<td>" . $row['Current'] . "</td>"; 
  echo "</tr>"; 
  } 
echo "</table>"; 

mysql_close($con);

 

My understanding is that this should work, but it does not... where am I going wrong in the PHP and MySQL ?

 

Thanks, ALan

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134791
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hello,

 

Yes I do... I use IE, FF, and Safari. All have the same result as when I do the live in Dreamweaver.

 

I may upload to the host server to to see.

 

I was also thinking that maybe I need to write in a "Alert" because this may be starting on page load.

 

 

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134905
Share on other sites
Adam Advanced Member

Adam

Posted
Posted

No I meant accessing the PHP script through the browser, cutting out the jQuery middle man. That may display an error, blank page, wrong output.. something that would give you a clue as to why it's not working. Once you're 100% sure the PHP works fine, and if you still have errors, then start debugging the jQuery.

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134937
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hi,

 

If I remove the <? ?> from the PHP file. Everything on the page is in the DIV on the page without clicking the button... I do not understand that... If you look at the http://www.pvmonitor.000a.biz/se-report.php that is live right now, I only have the table script on the report.php file, and you see the table on page load... I think that is odd...

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1134940
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hello,

 

One thing I learned from some additional reading, is that I need to change [start] AND [end] to...

 

$sql = 'SELECT `date`, time , power, volt, current FROM feed AS genreport BETWEEN $start AND $end';

 

It still does not change anything, but the manual said the sql now knows what it is looking for...

 

Thanks,

 

Alan

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1135269
Share on other sites
hkalan2007 Newbie

hkalan2007

Posted
  • Author
Posted

Hello,

 

I have the Jquery working, but the PHP is still an issue... here is the new PHP script that should work... BUT... It is not...

 

<?php
2.        if(isset($_POST['start1']) && isset($_POST['end1'])){
3.	$start = (isset($_POST['start1'])) ? date("Y-m-d",strtotime($_POST['start1'])) : date("Y-m-d");
4.	$end   = (isset($_POST['end1'])) ? date("Y-m-d",strtotime($_POST['end1'])) : date("Y-m-d");
5.	$con = mysql_connect('localhost', 'root', 'xxxxxxxxxx'); 
6.	if (!$con)  { 
7.	       die('Could not connect: ' . mysql_error()); 
8.	 } 
9.	mysql_select_db("inverters", $con); 
10.				 
11.	$sql = "SELECT * FROM report WHERE date BETWEEN '$start' AND '$end'";
12.

13.	echo "<table border='1'> 
14.	      <tr> 
15.		<th>Date</th> 
16.		<th>Hour</th> 
17.		<th>Power</th> 
18.		<th>Volt</th> 
19.		<th>Current</th> 
20.		</tr>"; 
21.				 
22.	$res = mysql_query($sql) or die(__LINE__.' '.$sql.' '.mysql_error());
23.	while($row = mysql_fetch_array($res)){
24.				 
25.		echo "<tr>"; 
26.		 echo "<td>" . $row['Date'] . "</td>"; 
27.		 echo "<td>" . $row['Time'] . "</td>"; 
28.		 echo "<td>" . $row['Power'] . "</td>"; 
29.		echo "<td>" . $row['Volt'] . "</td>"; 
30.		 echo "<td>" . $row['Current'] . "</td>"; 
31.		 echo "</tr>"; 
32.	 } 
33.	echo "</table>"; 
34.				 
35.	mysql_close($con); 
36.			
37.       }
38. ?>

 

I am thinking the problem is because every "Day" has 24 of the same in the "Date" because the "Time" is the reason for that

 

2010-11-17 | 00:00:00

2010-11-17 | 01:00:00

2010-11-17 | 02:00:00

ETC....

 

HELP !!!!!

Link to comment
https://forums.phpfreaks.com/topic/217844-sum-values-by-day/#findComment-1135414
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.