How to pull data from a MySQL table and display it using High Charts

JavaScript
1

Hey everyone,

I’m looking for some guidance on using High Charts on my website. I’ve been using it in the past in its most basic format, by adding individual data points to the JavaScript section.

I have now created a form which posts data to a SQL table (see below) and now want to pull this data and display it in several charts. The support for this is far too advanced for me, and was wondering if someone could help me crack it.

Below is my current file which means I have to type in each data point to the file:

<DOCTYPE!>
<html>

<head>
	<link rel="stylesheet" type="text/css" href="welcome.css">
	<meta name="viewport" content="width=device-width, initial-scale=1.0"/>  
	<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
	<script src="http://code.highcharts.com/highcharts.js"></script>
	<script>$(function () { 
    $('#frequency').highcharts({
        chart: {
            type: 'bar'
        },
        title: {
            text: 'Behaviour Frequency'
        },
        xAxis: {
            categories: [ 'Persistant Refusal', 'Verbally Abusive', 'Disruption  to Lesson', 'Property Damage', 'Pushing Pulling', 'Hitting', 'Kicking	Spitting', 'Biting/ Pinching', 'Head Butting', 'Self harm', 'Absconding', 'Aggression', 'Strangling', 'Snatching Objects']
        },
        yAxis: {
            title: {
                text: 'Number of Occurances'
            }
        },
        series: [{
            name: 'Child ID: 1',
            data: [1,2,1,0,2,0,0,1,0,0,0,0,0,3]
        }]
    });
});
</script>
<script>
	$(function () {
    var chart;
    
    $(document).ready(function () {
    	
    	// Build the chart
        $('#days').highcharts({
            chart: {
                plotBackgroundColor: null,
                plotBorderWidth: null,
                plotShadow: false
            },
            title: {
                text: 'Days Frequency'
            },
            tooltip: {
        	    pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
            },
            plotOptions: {
                pie: {
                    allowPointSelect: true,
                    cursor: 'pointer',
                    dataLabels: {
                        enabled: false
                    },
                    showInLegend: true
                }
            },
            series: [{
                type: 'pie',
                name: 'Days Frequency',
                data: [
                    ['Monday',   4],
                    ['Tuesday',       2],
                    ['Wednesday',    1],
                    ['Thursday',     1],
                    ['Friday',   2]
                ]
            }]
        });
    });
    
});
</script>

<script>
	$(function () {
    $('#time').highcharts({
        chart: {
            plotBackgroundColor: null,
            plotBorderWidth: 0,
            plotShadow: false
        },
        title: {
            text: 'Time',
            align: 'center',
            verticalAlign: 'middle',
            y: 50
        },
        tooltip: {
            pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
        },
        plotOptions: {
            pie: {
                dataLabels: {
                    enabled: true,
                    distance: -50,
                    style: {
                        fontWeight: 'bold',
                        color: 'white',
                        textShadow: '0px 1px 2px black'
                    }
                },
                startAngle: -90,
                endAngle: 90,
                center: ['50%', '75%']
            }
        },
        series: [{
            type: 'pie',
            name: 'Time Frequency',
            innerSize: '50%',
            data: [
                ['AM',   45.0],
                ['PM',       26.8],
            ]
        }]
    });
});
    	
</script>

	<script>$(function () { 
    $('#strategy').highcharts({
        chart: {
            type: 'bar'
        },
        title: {
            text: 'Strategy Employed'
        },
        xAxis: {
            categories: [ 'Verbal advice & Support', 'Distraction', 'Time Out Offered', 'Staff change over', 'Reassurance	', 'Time out directed', 'Success reminder', 'Contingent touch', 'Calm script	option offered', 'Planned ignoring', 'Choices/Consequences', 'Transfer/change adult', 'Positive handling Occurances']
        },
        yAxis: {
            title: {
                text: 'Times employed'
            }
        },
        series: [{
            name: 'Child ID: 1',
            data: [1,2,1,0,2,0,0,1,0,0,0,0,7,]
        }]
    });
});
</script>


	<title>Behaviour Insight</title>
</head>

<body>
	<div class="container">
		<a href="home.html"><div class="header">
			<img class="logo" src="images/logo.png"></a>
		</div><!--end header -->
					<div id="frequency"></div>
					<div id="days"></div>
					<div id="time"></div>
					<div id="strategy"></div>
	</div><!--end container -->
</body>

</html>

That gives the following output which is perfect:

I would like to do this without typing the individual numbers in and on the HighCharts website, it advises to add the following to the relevant HTML file:

<?php
while ($row = mysql_fetch_array($result)) {
   $data[] = $row['value'];
}
?>
var chart = new Highcharts.Chart({
      chart: {
         renderTo: 'container'
      },
      series: [{
         data: [<?php echo join($data, ',') ?>],
         pointStart: 0,
         pointInterval
      }]
});

I’m just wondering how I direct this to the relevant database and pull through the correct table/fields. I have scoured Google for guides and answers but everything suggested is way above my skill level.

Any help or direction would be much appreciated

Cheers

2

There are several steps, but there should be no need to “hard code” in the values all the time.
Things you’ll need to figure out are
How the database is set up so you’ll know how to get what you want from it
How to code the PHP to get that information and pass it along to the JavaScript.during page generation.
-or- How to use XHR to fetch the values from a server-side PHP file.

So I guess the first question is
What are the tables you’ll be wanting to get the datasets from?

3

Hi @Mittineagueand thanks for the reply,

I plan to access the relevant database just using the below (I hope! But let me know if this poses an issue)

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("behaviourinsight", $con);
?>

There is only one table contained in the database, and this is “records”. At the moment, I can pump information into the table using the below, and this is the information I need to pull back out and display using High Charts:

$sql="INSERT INTO Test (date1, start, end, event, incident, strategy, other)
VALUES
('$_POST[date]','$_POST[start]','$_POST[end]','$_POST[event]','$_POST[incident]','$_POST[strategy]','$_POST[other]')";
 
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

I’m afraid the below is over my head, but I will use it as a starting point and see if I can find anything

How to code the PHP to get that information and pass it along to the JavaScript.during page generation.
-or- How to use XHR to fetch the values from a server-side PHP file.

I’m really new to PHP, I’m only confident with HTML and CSS at the moment, so this is a good learning exercise. I’ve only really used PHP before for simple forms, submitting and retrieving basic information from/to database tables.

Thanks again for the reply

4

You plan on usiing all of these in the chart(s)?
date1, start, end, event, incident, strategy, other

I think some fields could be used to put bounds on the returned dataset, no?