AdatumЯнв
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>
