Янв
31
2013
31
2013
Строим график Flot с данными из базы Sqlite
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+' '+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+' '+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>