Adatum
Янв
31
2013

Строим график Flot с данными из базы Sqlite

sqldb
Flot — это JavaScript библиотека, основанная на библиотеке jQuery, с простым использованием, привлекательным внешним видом и интерактивными функциями.
Однажды мне понадобилось построить график по данным из базы Sqlite, но к сожалению в интернете не было не чего внятного, как оказалось все довольно легко!

ДемоСкачать


1. Делаем выборку из базы данных adatum.sqlite.

 
 <?php
 
 $db = new PDO('sqlite:./adatum.sqlite'); 
	$st = $db->query('SELECT id,records FROM statement');
	$results = $st->fetchAll(); foreach ($results as $row) 
 	{$dataset[] = array($row['id'],$row['records']);} 		
?>

2. Подставляем в javascript полученный массив в формате json.

 
	$(function () {
		var sqlite = <?php echo json_encode($dataset); ?>;

3. Ну и на по следок весь код с построением различных типов графиков.


<br><h1>График на данных из базы данных Sqlite</h1> <br>
<div class="sqlite-chart" style="height: 230px; width:100%;"></div>
<br><h1>Simple chart</h1> <br>
<div class="simple-chart" style="height: 230px; width:100%;"></div>
<br><h1>Donut simple chart</h1> <br>
 <div class="lines-chart" style="height: 230px;width:100%;"></div>     
<br><h1>Pie simple chart</h1> <br>
  <div class="order-bars-chart" style="height: 230px;width:100%;"></div>
<br><h1>Ordered bars chart </h1><br>
<div class="simple-donut" style="height: 230px;width:100%;"></div>
<br><h1>Stacked bars chart </h1><br>
 <div class="stacked-bars-chart" style="height: 230px;width:100%;"></div>
<br><h1>Lines chart without points</h1><br>						
 <div class="simple-pie" style="height: 230px;width:100%;"></div>
<br><h1>Horizontal bars chart</h1> <br>
	<div class="horizontal-bars-chart" style="height: 230px;width:100%;"></div>
 <br><h1>Auto update chart   </h1> <br>
  <div class="auto-update-chart" style="height: 230px;width:100%;"></div> 
 </div>
 
 
 <?php
	$st = (new PDO('sqlite:./adatum.sqlite'))->query('SELECT id,records FROM statement'); foreach (($st->fetchAll()) as $row) 	{$dataset[] = array($row['id'],$row['records']);} 		
?>

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <script type="text/javascript" src="js/jquery.flot.js"></script>
    <script type="text/javascript" src="js/jquery.flot.grow.js"></script>
    <script type="text/javascript" src="js/jquery.flot.pie.js"></script>
    <script type="text/javascript" src="js/jquery.flot.resize.js"></script>
    <script type="text/javascript" src="js/jquery.flot.tooltip_0.4.4.js"></script>
    <script type="text/javascript" src="js/jquery.flot.orderBars.js"></script>
     <script>                

$(document).ready(function() { 	

	var divElement = $('div'); 

	//sqlite chart 
    if (divElement.hasClass('sqlite-chart')) {
	$(function () {
		var sqlite = <?php echo json_encode($dataset); ?>;
	   
	    //graph options
		var options = {
				grid: {
					show: true,
				    aboveData: true,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: true,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active: false},
		            lines: {
	            		show: true,
	            		fill: false,
	            		lineWidth: 4,
	            		steps: false
		            	},
		            points: {
		            	show:true,
		            	radius: 5,
		            	symbol: "circle",
		            	fill: true,
		            	borderColor: "#fff"
		            }
		        },
		        legend: { position: "se" },
		        colors: chartColours,
		        shadowSize:1,
		        tooltip: true, 
				tooltipOpts: {
					content: "%s : %y.3",
					shifts: {
						x: -30,
						y: -50
					}
				}
		};  
		var plot = $.plot($(".sqlite-chart"),
           [{
    			label: "sqlite", 
    			data: sqlite,
    			lines: {fillColor: "#f2f7f9"},
    			points: {fillColor: "#88bbc8"}
    		}
    		], options);
	});
	}//end if
	
	//Simple chart 
    if (divElement.hasClass('simple-chart')) {
	$(function () {
		var sin = [], cos = [];
	    for (var i = 0; i < 14; i += 0.5) {
	        sin.push([i, Math.sin(i)]);
	        cos.push([i, Math.cos(i)]);
	    }
	    //graph options
		var options = {
				grid: {
					show: true,
				    aboveData: true,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: true,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active: false},
		            lines: {
	            		show: true,
	            		fill: false,
	            		lineWidth: 4,
	            		steps: false
		            	},
		            points: {
		            	show:true,
		            	radius: 5,
		            	symbol: "circle",
		            	fill: true,
		            	borderColor: "#fff"
		            }
		        },
		        legend: { position: "se" },
		        colors: chartColours,
		        shadowSize:1,
		        tooltip: true,
				tooltipOpts: {
					content: "%s : %y.3",
					shifts: {
						x: -30,
						y: -50
					}
				}
		};  
		var plot = $.plot($(".simple-chart"),
           [{
    			label: "Sin", 
    			data: sin,
    			lines: {fillColor: "#f2f7f9"},
    			points: {fillColor: "#88bbc8"}
    		}, 
    		{	
    			label: "Cos", 
    			data: cos,
    			lines: {fillColor: "#fff8f2"},
    			points: {fillColor: "#ed7a53"}
    		}], options);
	});
	}//end if

	//Donut simple chart
    if (divElement.hasClass('simple-donut')) {
	$(function () {
		var data = [
		    { label: "USA",  data: 38, color: "#88bbc8"},
		    { label: "Brazil",  data: 23, color: "#ed7a53"},
		    { label: "India",  data: 15, color: "#9FC569"},
		    { label: "Turkey",  data: 9, color: "#bbdce3"},
		    { label: "France",  data: 7, color: "#9a3b1b"},
		    { label: "China",  data: 5, color: "#5a8022"},
		    { label: "Germany",  data: 3, color: "#2c7282"}
		];

	    $.plot($(".simple-donut"), data, 
		{
			series: {
				pie: { 
					show: true,
					innerRadius: 0.4,
					highlight: {
						opacity: 0.1
					},
					radius: 1,
					stroke: {
						color: '#fff',
						width: 8
					},
					startAngle: 2,
				    combine: {
	                    color: '#353535',
	                    threshold: 0.05
	                },
	                label: {
	                    show: true,
	                    radius: 1,
	                    formatter: function(label, series){
	                        return '<div class="pie-chart-label">'+label+'&nbsp;'+Math.round(series.percent)+'%</div>';
	                    }
	                }
				},
				grow: {	active: false}
			},
			legend:{show:false},
			grid: {
	            hoverable: true,
	            clickable: true
	        },
	        tooltip: true, //activate tooltip
			tooltipOpts: {
				content: "%s : %y.1"+"%",
				shifts: {
					x: -30,
					y: -50
				}
			}
		});
	});
	}//end if

	//Pie simple chart
    if (divElement.hasClass('simple-pie')) {
	$(function () {
		var data = [
		    { label: "USA",  data: 38, color: "#88bbc8"},
		    { label: "Brazil",  data: 23, color: "#ed7a53"},
		    { label: "India",  data: 15, color: "#9FC569"},
		    { label: "Turkey",  data: 9, color: "#bbdce3"},
		    { label: "France",  data: 7, color: "#9a3b1b"},
		    { label: "China",  data: 5, color: "#5a8022"},
		    { label: "Germany",  data: 3, color: "#2c7282"}
		];

	    $.plot($(".simple-pie"), data, 
		{
			series: {
				pie: { 
					show: true,
					highlight: {
						opacity: 0.1
					},
					radius: 1,
					stroke: {
						color: '#fff',
						width: 2
					},
					startAngle: 2,
				    combine: {
	                    color: '#353535',
	                    threshold: 0.05
	                },
	                label: {
	                    show: true,
	                    radius: 1,
	                    formatter: function(label, series){
	                        return '<div class="pie-chart-label">'+label+'&nbsp;'+Math.round(series.percent)+'%</div>';
	                    }
	                }
				},
				grow: {	active: false}
			},
			legend:{show:false},
			grid: {
	            hoverable: true,
	            clickable: true
	        },
	        tooltip: true, //activate tooltip
			tooltipOpts: {
				content: "%s : %y.1"+"%",
				shifts: {
					x: -30,
					y: -50
				}
			}
		});
	});
	}//end if


	//Ordered bars chart
    if (divElement.hasClass('order-bars-chart')) {
	$(function () {
		//some data
		var d1 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d1.push([i, parseInt(Math.random() * 30)]);
	 
	    var d2 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d2.push([i, parseInt(Math.random() * 30)]);
	 
	    var d3 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d3.push([i, parseInt(Math.random() * 30)]);
	 
	    var ds = new Array();
	 
	     ds.push({
	     	label: "Data One",
	        data:d1,
	        bars: {order: 1}
	    });
	    ds.push({
	    	label: "Data Two",
	        data:d2,
	        bars: {order: 2}
	    });
	    ds.push({
	    	label: "Data Three",
	        data:d3,
	        bars: {order: 3}
	    });

		var options = {
				bars: {
					show:true,
					barWidth: 0.2,
					fill:1
				},
				grid: {
					show: true,
				    aboveData: false,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: false,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active:false}
		        },
		        legend: { position: "ne" },
		        colors: chartColours,
		        tooltip: true, //activate tooltip
				tooltipOpts: {
					content: "%s : %y.0",
					shifts: {
						x: -30,
						y: -50
					}
				}
		};

		$.plot($(".order-bars-chart"), ds, options);
	});
	}//end if

	//Stacked bars chart
    if (divElement.hasClass('stacked-bars-chart')) {
	$(function () {
		//some data
		var d1 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d1.push([i, parseInt(Math.random() * 30)]);
	 
	    var d2 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d2.push([i, parseInt(Math.random() * 30)]);
	 
	    var d3 = [];
	    for (var i = 0; i <= 10; i += 1)
	        d3.push([i, parseInt(Math.random() * 30)]);
	 
	    var ds = new Array();
	 
	     ds.push({
	     	label: "Data One",
	        data:d1
	    });
	    ds.push({
	    	label: "Data Two",
	        data:d2
	    });
	    ds.push({
	    	label: "Data Tree",
	        data:d3
	    });

		var stack = 0, bars = true, lines = false, steps = false;

		var options = {
				grid: {
					show: true,
				    aboveData: false,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: true,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active:false},
		        	stack: stack,
	                lines: { show: lines, fill: true, steps: steps },
	                bars: { show: bars, barWidth: 0.5, fill:1}
			    },
		        xaxis: {ticks:11, tickDecimals: 0},
		        legend: { position: "se" },
		        colors: chartColours,
		        shadowSize:1,
		        tooltip: true, //activate tooltip
				tooltipOpts: {
					content: "%s : %y.0",
					shifts: {
						x: -30,
						y: -50
					}
				}
		};

		$.plot($(".stacked-bars-chart"), ds, options);
	});
	}//end if



	//Lines chart without points
    if (divElement.hasClass('lines-chart')) {
	$(function () {

		//some data
		var d1 = [[1, 3+randNum()], [2, 6+randNum()], [3, 9+randNum()], [4, 12+randNum()],[5, 15+randNum()],[6, 18+randNum()],[7, 21+randNum()],[8, 15+randNum()],[9, 18+randNum()],[10, 21+randNum()],[11, 24+randNum()],[12, 27+randNum()],[13, 30+randNum()],[14, 33+randNum()],[15, 24+randNum()],[16, 27+randNum()],[17, 30+randNum()],[18, 33+randNum()],[19, 36+randNum()],[20, 39+randNum()],[21, 42+randNum()],[22, 45+randNum()],[23, 36+randNum()],[24, 39+randNum()],[25, 42+randNum()],[26, 45+randNum()],[27,38+randNum()],[28, 51+randNum()],[29, 55+randNum()], [30, 60+randNum()]];
		var d2 = [[1, randNum()-5], [2, randNum()-4], [3, randNum()-4], [4, randNum()],[5, 4+randNum()],[6, 4+randNum()],[7, 5+randNum()],[8, 5+randNum()],[9, 6+randNum()],[10, 6+randNum()],[11, 6+randNum()],[12, 2+randNum()],[13, 3+randNum()],[14, 4+randNum()],[15, 4+randNum()],[16, 4+randNum()],[17, 5+randNum()],[18, 5+randNum()],[19, 2+randNum()],[20, 2+randNum()],[21, 3+randNum()],[22, 3+randNum()],[23, 3+randNum()],[24, 2+randNum()],[25, 4+randNum()],[26, 4+randNum()],[27,5+randNum()],[28, 2+randNum()],[29, 2+randNum()], [30, 3+randNum()]];
		//define placeholder class
		var placeholder = $(".lines-chart");
		//graph options
		var options = {
				grid: {
					show: true,
				    aboveData: true,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: true,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active:false},
		            lines: {
	            		show: true,
	            		fill: true,
	            		lineWidth: 2,
	            		steps: false
		            	},
		            points: {show:false}
		        },
		        legend: { position: "se" },
		        yaxis: { min: 0 },
		        xaxis: {ticks:11, tickDecimals: 0},
		        colors: chartColours,
		        shadowSize:1,
		        tooltip: true, //activate tooltip
				tooltipOpts: {
					content: "%s : %y.0",
					shifts: {
						x: -30,
						y: -50
					}
				}
		    };   
	
        	$.plot(placeholder, [ 

        		{
        			label: "Visits", 
        			data: d1,
        			lines: {fillColor: "#f2f7f9"},
        			points: {fillColor: "#88bbc8"}
        		}, 
        		{	
        			label: "Unique Visits", 
        			data: d2,
        			lines: {fillColor: "#fff8f2"},
        			points: {fillColor: "#ed7a53"}
        		} 

        	], options);

	});
	}//end if

	//Horizontal bars chart
    if (divElement.hasClass('horizontal-bars-chart')) {
	$(function () {
		//some data
		//Display horizontal graph
    var d1_h = [];
    for (var i = 0; i <= 5; i += 1)
        d1_h.push([parseInt(Math.random() * 30),i ]);

    var d2_h = [];
    for (var i = 0; i <= 5; i += 1)
        d2_h.push([parseInt(Math.random() * 30),i ]);

    var d3_h = [];
    for (var i = 0; i <= 5; i += 1)
        d3_h.push([ parseInt(Math.random() * 30),i]);
                
    var ds_h = new Array();
    ds_h.push({
        data:d1_h,
        bars: {
            horizontal:true, 
            show: true, 
            barWidth: 0.2, 
            order: 1
        }
    });
	ds_h.push({
	    data:d2_h,
	    bars: {
	        horizontal:true, 
	        show: true, 
	        barWidth: 0.2, 
	        order: 2
	    }
	});
	ds_h.push({
	    data:d3_h,
	    bars: {
	        horizontal:true, 
	        show: true, 
	        barWidth: 0.2, 
	        order: 3
	    }
	});


		var options = {
				grid: {
					show: true,
				    aboveData: false,
				    color: "#3f3f3f" ,
				    labelMargin: 5,
				    axisMargin: 0, 
				    borderWidth: 0,
				    borderColor:null,
				    minBorderMargin: 5 ,
				    clickable: true, 
				    hoverable: true,
				    autoHighlight: false,
				    mouseActiveRadius: 20
				},
		        series: {
		        	grow: {active:false},
			        bars: {
			        	show:true,
						horizontal: true,
						barWidth:0.2,
						fill:1
					}
		        },
		        legend: { position: "ne" },
		        colors: chartColours,
		        tooltip: true, //activate tooltip
				tooltipOpts: {
					content: "%s : %y.0",
					shifts: {
						x: -30,
						y: -50
					}
				}
		};

		$.plot($(".horizontal-bars-chart"), ds_h, options);
	});
	}//end if


    if (divElement.hasClass('auto-update-chart')) {
	$(function () {
	
	    var data = [], totalPoints = 300;
	    function getRandomData() {
	        if (data.length > 0)
	            data = data.slice(1);

	        // do a random walk
	        while (data.length < totalPoints) {
	            var prev = data.length > 0 ? data[data.length - 1] : 50;
	            var y = prev + Math.random() * 10 - 5;
	            if (y < 0)
	                y = 0;
	            if (y > 100)
	                y = 100;
	            data.push(y);
	        }

	 	        var res = [];
	        for (var i = 0; i < data.length; ++i)
	            res.push([i, data[i]])
	        return res;
	    }

	
	    var updateInterval = 200;

	  
	    var options = {
	        series: { 
	        	grow: {active:false},
	        	shadowSize: 0, 
	        	lines: {
            		show: true,
            		fill: true,
            		lineWidth: 2,
            		steps: false
	            }
	        },
	        grid: {
				show: true,
			    aboveData: false,
			    color: "#3f3f3f" ,
			    labelMargin: 5,
			    axisMargin: 0, 
			    borderWidth: 0,
			    borderColor:null,
			    minBorderMargin: 5 ,
			    clickable: true, 
			    hoverable: true,
			    autoHighlight: false,
			    mouseActiveRadius: 20
			}, 
			colors: chartColours,
	        tooltip: true, 
			tooltipOpts: {
				content: "Value is : %y.0",
				shifts: {
					x: -30,
					y: -50
				}
			},	
	        yaxis: { min: 0, max: 100 },
	        xaxis: { show: true}
	    };
	    var plot = $.plot($(".auto-update-chart"), [ getRandomData() ], options);

	    function update() {
	        plot.setData([ getRandomData() ]);
	     
	        plot.draw();
	        
	        setTimeout(update, updateInterval);
	    }

	    update();
	});
	}

});


randNum = function(){
	
	return (Math.floor( Math.random()* (1+40-20) ) ) + 20;
}

var chartColours = ['#88bbc8', '#ed7a53', '#9FC569', '#bbdce3', '#9a3b1b', '#5a8022', '#2c7282'];


var positive = [1,5,3,7,8,6,10];
var negative = [10,6,8,7,3,5,1]
var negative1 = [7,6,8,7,6,5,4]

$('#stat1').sparkline(positive,{
	height:15,
	spotRadius: 0,
	barColor: '#9FC569',
	type: 'bar'
});
$('#stat2').sparkline(negative,{
	height:15,
	spotRadius: 0,
	barColor: '#ED7A53',
	type: 'bar'
});
$('#stat3').sparkline(negative1,{
	height:15,
	spotRadius: 0,
	barColor: '#ED7A53',
	type: 'bar'
});
$('#stat4').sparkline(positive,{
	height:15,
	spotRadius: 0,
	barColor: '#9FC569',
	type: 'bar'
});
//sparkline in widget
$('#stat5').sparkline(positive,{
	height:15,
	spotRadius: 0,
	barColor: '#9FC569',
	type: 'bar'
});

$('#stat6').sparkline(positive, { 
	width: 70,
	height: 20,
	lineColor: '#88bbc8',
	fillColor: '#f2f7f9',
	spotColor: '#e72828',
	maxSpotColor: '#005e20',
	minSpotColor: '#f7941d',
	spotRadius: 3,
	lineWidth: 2
});


            
</script>  

Похожие статьи

Оставить комментарий