	<!DOCTYPE HTML>
	<html>
	<head>  
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<script src="js/jquery-1.9.1.js" type="text/javascript"></script>
	<link href="https://fonts.googleapis.com/css?family=Droid+Sans" rel="stylesheet"> 
	
	<!--trends in police killings in kenya line_chart-->
	<?php
	$servername = "localhost";
	$dbuser = "root";
	$dbpass = "root";
	$dbname = "deadlyforce";
    ?>
	<script type="text/javascript">
	$(function () {
	$('#trend_line_chart').highcharts({
	chart: {
	events: {
            load: function() {
                this.renderer.image('http://newsplex.nation.co.ke/newsplex_watermark.png', '90%','92%', 60, 29)
                    .add();
            },
        },
	type: 'line',
	backgroundColor: null,
	plotBackgroundColor: null,
	plotBorderWidth: null,
	plotShadow: false,
	style: {
	fontFamily: 'Helvetica'
	},
	},
	plotOptions: {
	line: {
	//pointPadding: 0.2,
	//borderWidth: 0,
	marker: {
	enabled: true,
	//lineWidth: 1,
	//lineColor: null,
	symbol: 'circle',
	radius: 2
	},
	},
	},
	title: {
	text: 'Trends in police killings from January 2015 to August 2016',
	x: -20,//center
	style:{color: '#666',fontSize: '25px'}
	},
	subtitle: {
	text: 'There had been eight more killings by police by the end of August 2016 than at the same period last year.',
	style:{color: '#666',fontSize: '18px'},
	x: -20
	},
	xAxis: {
	labels: {
	style: {
	font: '16px Helvetica'
	}
	},
	categories: [
	'Jan', 
	'Feb', 
	'Mar', 
	'Apr', 
	'May', 
	'Jun',
	'Jul', 
	'Aug', 
	'Sep', 
	'Oct', 
	'Nov', 
	'Dec'
	]
	},
	yAxis: {
	labels: {
	style: {
	font: '16px Helvetica'
	}
	},
	title: {
	text: 'Killed',
	style: {

	fontSize:'18px'
	},
	},
	plotLines: [{
	value: 0,
	width: 1,
	color: '#ee2e2a'
	}]
	},
	credits: {
	enabled: false
	},
	tooltip: {
	style: {
	fontFamily: 'Helvetica',
	fontSize: '18px'
	},
	headerFormat: '{point.key}<table>',
	pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' +
	'<td style="padding:0"><b>{point.y:,.0f}</b></td></tr>',
	footerFormat: '</table>',
	shared: true,
	useHTML: true
	},
	legend: {
	enabled: true,
	floating: false,
	verticalAlign: 'bottom',
	align:'center',
	itemStyle: {
	//color: '#000000',
	//fontWeight: 'bold',
	fontSize: '16px'
	},
	//y:40]
	},
	series: [{
	name: '2016',
	color:'#ee2e2a',
	lineWidth: 3,
	data: [<?php
	//select 2016 killings by month
	try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbuser, $dbpass);
	// set the PDO error mode to exception
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$year2016='2016';
	//group by gender
	$sthandler = $conn->prepare("select month(date_of_incident)as month,year(date_of_incident)as year,count(entry_no) as victims from police_killings where year(date_of_incident)=:year2016 group by month(date_of_incident)");
	$sthandler->bindParam(':year2016', $year2016);
	$sthandler->execute();
	$victims = $sthandler->fetchAll();
	foreach ($victims as $row) {
	$monthNum  = $row['month'];
	$dateObj   = DateTime::createFromFormat('!m', $monthNum);
	$monthName = $dateObj->format('F'); // March
	//echo $monthName;
	//echo "Month=".$row['month']." year=".$row['year']." victims=".$row['victims'].",";
	//echo "<br>";
	echo $row['victims'].",";
	}
	}
	catch(PDOException $e)
	{
	echo "error" . "<br>" . $e->getMessage();
	}
	$conn = null;
	//end 2016 killings by month
	?>
	]
	}, {
	name: '2015',
	color:'#FF0000',
	dashStyle: 'shortdash',
	data: [<?php
	//select 2015 killings by month
	try {
	$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbuser, $dbpass);
	// set the PDO error mode to exception
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$year2015='2015';
	//group by gender
	$sthandler2015 = $conn->prepare("select month(date_of_incident)as month,year(date_of_incident)as year,count(entry_no) as victims from police_killings where year(date_of_incident)=:year2015 group by month(date_of_incident)");
	$sthandler2015->bindParam(':year2015', $year2015);
	$sthandler2015->execute();
	$victims2015 = $sthandler2015->fetchAll();
	foreach ($victims2015 as $row) {
	$monthNum  = $row['month'];
	$dateObj   = DateTime::createFromFormat('!m', $monthNum);
	$monthName = $dateObj->format('F'); // March
	//echo $monthName;
	//echo "Month=".$row['month']." year=".$row['year']." victims=".$row['victims'].",";
	echo $row['victims'].",";
	}
	}
	catch(PDOException $e)
	{
	echo "error" . "<br>" . $e->getMessage();
	}
	$conn = null;
	//end 2015 killings by month
	?>
	]
	}]
	});
	});
	</script>
	<div id="trend_line_chart" style="min-width: 310px; height: 600px; margin: 0 auto"></div>
	<script src="http://code.highcharts.com/stock/highstock.js"></script>
	<script src="http://code.highcharts.com/highcharts-more.js"></script>
	<script src="http://code.highcharts.com/modules/exporting.js"></script>
	</body>
	</html>