SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    56
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Capture.PNG

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

    PHP Code:
    <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:

    1.PNG

    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 Code:
    <?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. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,039
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Nov 2012
    Posts
    56
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi @Mittineague and 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 Code:
    <?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:

    PHP Code:
    $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. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,039
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •