Generating a list/graph based on values associated with dates in SQL table

[rpmorrow]

SQL INFO:

MySQL charset:  UTF-8 Unicode (utf8)

phpMyAdmin 2.6.4-pl3

SQL version: 5.0.37-log

 

Hi,

 

I'm trying to set up some code to get the correct data from DB for drawing graphs of some data vs time (using GraPHPite). My intention is to be able to specify a time frame, then compile the relevant data from the DB into a format for the graph.

For example; $value[$day] could contain a value for each day of a month.

This is pretty straightforward but it is the getting and processing the data i'm finding difficult.

 

The difficulty is the table data in the DB does not have a value for each day and it has multiple values for some days.

So basically I want to run through the table getting values and storing them in an array, adding where there are multiple days, and storing 0 where there is no entry.

 

Here is some code i'm using (which does not work).

$range = 28;

$startDate = "2005-08-06 22:41:40";

$stopDate = "2007-08-06 22:41:40";

 

$query = "SELECT * FROM ShopInvoice WHERE sub_date BETWEEN '{$startDate}' AND '{$stopDate}' AND paid='1' ORDER BY sub_date ASC;";
	DBConnect();
	echo mysql_error();
	$result = mysql_query($query);
		if ($result) {
			if (mysql_num_rows($result) > 0) {
				for ($d=1; $d < $range; $d++){	//loop for number of days in $range
					while ($row = mysql_fetch_assoc($result)) {
						$year = substr($row['sub_date'], 0, 4);   //split rows date into elements
						$month = substr($row['sub_date'], 5, 2);
						$day = substr($row['sub_date'], 8, 2);
						$stamp = $year.$month.$day;				
						if (($stamp == $prev) && ($day - $d == 0)) { // check for same day as previous
							$val[$d] += $row['invoiceTotal'];          //increment total for day $d
						} else if (($day - $d == 0)) {                      //day changed so store value
							$val[$d] = $row['invoiceTotal'];
						}		
						$prev = $stamp;	
					} // End While data is available from table
					if ($val[$d] <= 0) $val[$d] =""; //Prevents invaid graphs
					$DataSet-> addPoint($d, $val[$d]);
					echo $d,": ",$val[$d],"<br />"; //DEBUG
				} // End for range loop
			} else { // else if 0 rows or less
				echo '<p>No Data To Display.</p>';
				$DataSet-> addPoint(1, 1); // allows graph to still load
			} //End If num rows > 0	
		} else { // else if no result
			echo '<p>DataBase Error Try Again Later</p>';
			echo mysql_error();
		} //End if Result
		DBDisconnect();

 

If working my DEBUG line (echo $d,": ",$val[$d],"<br />") should display alist of days with an associated total value for each. i seem to get an empty list or "No Data To Display." if i try changing the dates range part.

I think the way I have done this is not as efficent as it could be. I excpect this is a common thing people need to do, so I'm wondering if there is a better (or at least working) method than trying to scan the whole table for each day I want info for.

 

Can anyone see where my logic is going wrong?

the DB table uses the date format DATETIME if it matters

 

[fenway]

First, you can extract each piece of the date using mysql functions, but I don't see really why you're doing this....

[Barand]

You might find this an easier way of getting the daily invoice totals

 

<?php
$sql = "SELECT DATE_FORMAT(sub_date, '%Y-%m-%d') as subdate, SUM(invoice_total) as invtot
        FROM ShopInvoice 
        WHERE sub_date BETWEEN '{$startDate}' AND '{$stopDate}' 
            AND paid='1' 
        GROUP BY subdate";
?>