date, unix timestamp, graph plot

jacko_162 · January 5, 2014

ok so i want to start designing some graphs to show data from a range of dates and test values (upto x14 seperate tests each with its own visual graph)

so i looked into things and found FLOT graphs, seems to be able to view what i need and show it how i want to.

this is my database formate after using a SELECT query converting the "date" row into a unix timestamp * 1000 to show milliseconds which im told is needed for flot to plot the xaxis correctly.

i have the following code at the moment and it seems to be pulling the data;

<?php	
//session_start();

//include('Includes/auth.php');
//require_once('header.php');
include('Includes/connect.php');

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="cs" lang="cs">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf8"/>
<title>Index</title>
<script src="Includes/jquery-1.8.3.js"></script>
<script src="Includes/jquery.flot.js"></script>
<script src="Includes/jquery.flot.time.js"></script>

</head>
<?php
// Main query to pull data from 'tests' table
	$sql = "SELECT UNIX_TIMESTAMP(`date`)*1000 AS unixDate,`date`, `test1`, `test2`, `test3`, `test4`, `test5`, `test6`, `test7`, `test8`, `test9`, `test10`, `test11`, `test12`, `test13`, `test14` FROM `tests` WHERE member_id = '1' ORDER by `date` ASC";
	$result = mysql_query($sql) or die ("no query");
	
//  Dataset1
while($row = mysql_fetch_assoc( $result ))  {
    $dataset1[] = array(
                    $row['unixDate'],
                    sprintf( "%.3f", $row['test1'] )
                );
}
?>
<div id="chart1" style="width:600px;height:400px;"></div>

<script type="text/javascript">
    //Chart1
    var chart1Options = {
    	xaxis: { mode: "time",  timeformat: "%Y-%m-%d"},
      	lines: { show: true },
        points: { show: true },
		grid: {
          backgroundColor: { colors: ["#4ca8fa", "#2887da"] },
		  bordercolor: "#fff",
		  borderwidth: "60",
          hoverable: true }
	};
	
	var dataset1 = { label: "result", data: <?php echo json_encode($dataset1); ?>,};

	$.plot($("#chart1"), [ dataset1 ], chart1Options);
</script>
<?php 
//echo json_encode($dataset1);
echo print_r($dataset1); ?>

<?php foreach( $dataset1 as $date => $results ): ?>
<h2><?= $date ?></h2>
    <ul>
    	<?php foreach( $results as $results ): ?>
    	<li><?= $results ?></li>
    	<?php endforeach; ?>
    </ul>
<?php endforeach; ?>
</body>
</html>

The problem seems to be that the graph isnt plotting ther points across the grid, prob due to the fact the dates are wrong on the x axis.

which is it showing the wrong dates?

http://www.myreeftests.com/graphs2.php (link to the current graph using the above data!)

i been on this for over a day now and its giving me a headache. can anyone please help me out and try to explain what i need to do to resolve these issues, i can then just duplicate the working graph for the remaining test results.

thank you

jazzman1 · January 5, 2014

What result you're expecting if the member_id = 1 ?

Ch0cu3r · January 5, 2014

The x-axis is being plotted correctly.

The problem you have is a 24 month gap in your data. The first data point is for 2011-05 the reset are for 2013-05. If you remove the first data point the graph is plotted correctly.

jacko_162 · January 5, 2014

aaaaah.... sometuimes the small things,

this begs the question though how did the other values on the xaxis come about if i had the gap? does flot graph just incriment the ticks on the xaxis?

or can i specificy to only use ticks found in the "date" data?

Sign In

date, unix timestamp, graph plot

Recommended Posts

jacko_162

Link to comment

Share on other sites

jazzman1

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

jacko_162

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information