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