Grabbing data to create a Bar Chart

[SaranacLake]

This question is part PHP, part MySQL.

I have a survey_responses table...

	- id
	- survey_id
	- question_id
	- member_id
	- response
	

 

For satisfaction questions (i.e. 1 = Strongly Disagree, 2 = Disagree, 3 = Neither, 4 = Agree, 5 = Strongly Agree), I'm trying to figure out how to get the counts for each of those responses into PHP so I can then feed them to a Bar Chart I hope to create with HTML/CSS.

1.) Should I create some kind of loop in PHP, and look for each value (e.g. 1 = Strongly Disagree) and one at a time plug them into variables called: $stronglyDisagree, $disagree, $neighter, $agree, $stronglyAgree?

 

2.) Or do I first need a "cross-tab" query in MySQL?

 

3.) Would this be easier if the database values were in a PHP array?

 

Not sure where to begin with this, and more importantly, I want to make sure that my data model in MySQL is sufficient so i can do reporting like this!!

 

Thanks.

 

[requinix]

11 minutes ago, SaranacLake said:

3.) Would this be easier if the database values were in a PHP array?

Well you can't do much with them while they're still sitting in the database...

I figure the "right" approach depends on what you have to provide to generate the chart. You probably need to end up with an array, naturally, but in what format? An array of key/value pairs? An array of arrays? Array of objects?
What is the end result as far as the code is concerned?

[SaranacLake]

16 minutes ago, requinix said:

Well you can't do much with them while they're still sitting in the database...

I figure the "right" approach depends on what you have to provide to generate the chart. You probably need to end up with an array, naturally, but in what format? An array of key/value pairs? An array of arrays? Array of objects?
What is the end result as far as the code is concerned?

Well, I don't know exactly what I need since I don't know how to create a bar chart from scratch yet!

After posting, here is what I came up with in my head...

- In MySQL, create a query using COUNT( ) and GROUP BY to get a query-set that contains the 5 stubs and their count values.

	1, Strongly Disagree, 7
	2, Disagree, 10
	3, Neither, 12
	4, Agree, 25
	5, Strongly Agree, 41 
	

- Then pull that into a multi-dimensional array in PHP

- Then iterate through that array / multi-dimensional array and build my bar chart.

 

In general, does that sound like a good start?

 

Again, the main thing I am worried about is not having what I need on the backend.

I have my survey script running now, plus data-validation working, and I have a few data model tweaks in mind, but I want to be sure that I have the tables and relationships to build the graphs/charts/reports that one would expect.

Since I will be (hopefully) displaying all survey results visually using PHP, I think that gives me more flexibility than if i could only present data in a tabular format - thus why I now have 'cross-tab" queries in my mind.

(If a person wanted to describe survey results using only tables, then I think that would be a hellish query since in my case I have disparate question types - Yes/No, Rating Scales, Multiple Choice, Multi-Response, etc.  I think by breaking the data down to a per question basis and using a graph to represent that particular type of data, it will be much easier, and probably not as rigid on the table design side of things.)

Edited February 18, 2021 by SaranacLake
Added comment

[requinix]

1 minute ago, SaranacLake said:

In general, does that sound like a good start?

It's a perfectly valid approach, sure.

 

1 minute ago, SaranacLake said:

Again, the main thing I am worried about is not having what I need on the backend.

As long as you have the data in some form at all then you have what you need. Beyond that the only thing to gain is convenience.

The chart stuff is non-trivial. Are you sure you want to make it yourself? Whatever you come up with won't be of the same quality that you can get with a third-party library.

[SaranacLake]

1 minute ago, requinix said:

It's a perfectly valid approach, sure.

Good to hear!

 

1 minute ago, requinix said:

As long as you have the data in some form at all then you have what you need. Beyond that the only thing to gain is convenience.

Well, not entirely true.  It is possible to collect data, but not have it normalized properly, and end up with a real PITA.

But data modeling is my strength, and I think I have a good data model. 

(My only issue is that I am the king of "scope creep", and every time I build something or fix something - like my survey data validation thanks to @maxxd suggestion - I get the urge to add on more functionality!  I can see a few things in my current data model that could be a pain down the road, so i am thinking a few steps ahead and making sure my database is scalable.)

 

 

1 minute ago, requinix said:

The chart stuff is non-trivial. Are you sure you want to make it yourself? Whatever you come up with won't be of the same quality that you can get with a third-party library.

Valid argument.

Yeah, I am pig-headed and I prefer rolling my own solutions.

I did a quick Google search during supper, and it looks like it isn't too hard to create decent pie charts and bar charts using CSS3.  (I think the hard part is using PHP to grab the data from the database, and dynamically feeding the graph things like adjustable scales so as more people respond the graph adapts.

Reporting is a v2.0 thing, or at least a v1.1 thing.

But my question for now is more making sure I don't paint myself into a corner that i can't easily fix later.

Sounds like at first inspection you think I'm on the right path.  And, now I just need to figure out how much I want to "gold-plate" my current database design. 🙂

 

 

 

[requinix]

2 hours ago, SaranacLake said:

Well, not entirely true.  It is possible to collect data, but not have it normalized properly, and end up with a real PITA.

Normalization problems can be corrected at any point - though the amount of effort required varies. But data problems can't be corrected retroactively.

[Barand]

TIP: If you are creating home-grown charts, plotting the values is the easy bit. 95% of the coding effort will be in the drawing of chart area, plot area, axes, axis labels, scaling, titles etc.

You can sidestep this with a simple table with horizontal bars. EG

CODE EXAMPLE...

<?php

$values = [
             'Strongly Disagree' => 7,
             'Disagree'          => 10,
             'Neither'           => 12,
             'Agree'             => 25,
             'Strongly Agree'    => 41 
          ];
          
function valueChart(&$values)
{
    $out = "<table class='chartTable'>
              <tr><th>Response</th>
                  <th>Total</th>
                  <th>Percent</th>
              </tr>
           ";
    $totalVal = array_sum($values);
    foreach ($values as $resp => $n) {
        $out .= "<tr><td>$resp</td>
                     <td class='ra'>$n</td>
                     <td>" . bar($n / $totalVal * 100) . "</td></tr>\n";
    }
    $out .= "</table\n";
    return $out;
}

function bar($val=0)
{
    $a = '#3399ff';
    $b = '#e6f2ff';
    $c = '#0066cc';
    $bg = '#eee';
    $width = 300;
    $height = 25;
    $svg = <<<SVG
        <svg width='$width' height='$height' viewBox='0 0 $width $height'>";
        <defs>
        <linearGradient id="bargrad"  x1="0" y1="0" x2="0" y2="1">
            <stop offset="0%" style="stop-color:$a"/>
            <stop offset="25%" style="stop-color:$b"/>
            <stop offset="100%" style="stop-color:$c"/>
        </linearGradient>
        </defs>
        <rect x='0' y='0' width='$width' height='$height' style='fill:$bg' stroke='#999'/>
SVG;
    $w = $val/100 * $width;
    $svg .= "<rect x='0' y='0' width='$w' height='$height' style='fill:url(#bargrad)' />";
    $svg .= "</svg>\n";
    return $svg;
}

?>
<!DOCTYPE html>
<html lang="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Chart Example</title>
<head>
<style type='text/css'>
    .chartTable {
        font-family: arial, sans-serif;
        font-size: 11pt;
    }
    th {
        padding: 4px 16px ;
    }
    td {
        padding: 0 16px;
    }
    .ra {
        text-align: right;
    }
</style>
</head>
<body>
    <?=valueChart($values)?>
</body>
</html>

Hope this helps.

Edited February 18, 2021 by Barand
typo

[SaranacLake]

16 hours ago, Barand said:

TIP: If you are creating home-grown charts, plotting the values is the easy bit. 95% of the coding effort will be in the drawing of chart area, plot area, axes, axis labels, scaling, titles etc.

Yes, I agree that is the harder part.

 

@Barand, 

WOW!  I am blown away by your example below!

That is a super good-looking horizontal bar chart!

As you mentioned, it takes more work to get adjustable scales, titles, etc, but I think what you have below is more than sufficient for my needs.  And I think most users would appreciate a "visual", even if it is a simple one, because that is all I think most people want.

 

16 hours ago, Barand said:

You can sidestep this with a simple table with horizontal bars. EG

CODE EXAMPLE...

<?php

$values = [
             'Strongly Disagree' => 7,
             'Disagree'          => 10,
             'Neither'           => 12,
             'Agree'             => 25,
             'Strongly Agree'    => 41 
          ];
          
function valueChart(&$values)
{
    $out = "<table class='chartTable'>
              <tr><th>Response</th>
                  <th>Total</th>
                  <th>Percent</th>
              </tr>
           ";
    $totalVal = array_sum($values);
    foreach ($values as $resp => $n) {
        $out .= "<tr><td>$resp</td>
                     <td class='ra'>$n</td>
                     <td>" . bar($n / $totalVal * 100) . "</td></tr>\n";
    }
    $out .= "</table\n";
    return $out;
}

function bar($val=0)
{
    $a = '#3399ff';
    $b = '#e6f2ff';
    $c = '#0066cc';
    $bg = '#eee';
    $width = 300;
    $height = 25;
    $svg = <<<SVG
        <svg width='$width' height='$height' viewBox='0 0 $width $height'>";
        <defs>
        <linearGradient id="bargrad"  x1="0" y1="0" x2="0" y2="1">
            <stop offset="0%" style="stop-color:$a"/>
            <stop offset="25%" style="stop-color:$b"/>
            <stop offset="100%" style="stop-color:$c"/>
        </linearGradient>
        </defs>
        <rect x='0' y='0' width='$width' height='$height' style='fill:$bg' stroke='#999'/>
SVG;
    $w = $val/100 * $width;
    $svg .= "<rect x='0' y='0' width='$w' height='$height' style='fill:url(#bargrad)' />";
    $svg .= "</svg>\n";
    return $svg;
}

?>
<!DOCTYPE html>
<html lang="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Chart Example</title>
<head>
<style type='text/css'>
    .chartTable {
        font-family: arial, sans-serif;
        font-size: 11pt;
    }
    th {
        padding: 4px 16px ;
    }
    td {
        padding: 0 16px;
    }
    .ra {
        text-align: right;
    }
</style>
</head>
<body>
    <?=valueChart($values)?>
</body>
</html>

Hope this helps.

 

I will put your code in my hat to use after I go live with my website.

Thanks for the headstart!  👍

 

 

[kicken]

If you do decide to step things up a level I highly suggest just finding a good library to do your chart rendering.  Chart.js is my tool of choice currently.  It offloads the chart drawing to the client so all you have to do on the server side is generate a json structure.

 

[SaranacLake]

14 hours ago, kicken said:

If you do decide to step things up a level I highly suggest just finding a good library to do your chart rendering.  Chart.js is my tool of choice currently.  It offloads the chart drawing to the client so all you have to do on the server side is generate a json structure.

 

 

VERY impressive, I must say!

 

1.) Is that open-source?  (I might not feel so bad "cheating" and using someone else's library as long as it is NOT from Google or some other evil corporation!)

 

2.) So I guess I'd need to know how to do Javascript, right?

 

3.) And I'd need to know how to use JSON?  (Have heard of that but have no clue what it is?!)

 

Thanks for the tip!  👍

[kicken]

3 hours ago, SaranacLake said:

1.) Is that open-source?

Yes, as indicated on the home page.  The code is over on Github if your interested.

3 hours ago, SaranacLake said:

2.) So I guess I'd need to know how to do Javascript, right

Only the basics if you stick to what the library can natively do given it's options.   The more JS you know the more fancy/custom stuff you can do with it, but for basic chart rendering you only really need to include the library and write:

new Chart(document.getElementById('where'), jsonConfiguration);

Here's something to make it even easier if you stick to the basics:

window.addEventListener('DOMContentLoaded', function(){
  var chartElements = document.querySelectorAll('div[data-chart]');
  chartElements.forEach(function(div){
    var configuration = JSON.parse(div.dataset.chart);
    var canvas = document.createElement('canvas');
    div.appendChild(canvas);
    new Chart(canvas, configuration);
  });
});

That will look for any div tags with a data-chart attribute and initialize a chart in that element using the configuration in the value of the data attribute.

4 hours ago, SaranacLake said:

3.) And I'd need to know how to use JSON?  (Have heard of that but have no clue what it is?!)

Not really.  For the most part you can just generate the configuration in PHP as an array and json_encode it.  So you first would generate your configuration as a PHP array using the data from your database:

$configuration = [
  'type' => 'bar',
  'data' => [
    'labels' => ['January', 'February', 'March', 'April', 'May', 'June', 'July'],
    'datasets' => [
      [
        'label' => 'Group A',
        'backgroundColor' => '#84999a',
        'borderColor' => '#ff0000',
        'borderWidth' => 1,
          //your values from the DB go here
        'data' => [81072, 14498, 20460, 14651, 34036, 20056, 27270]
      ]
    ]
  ],
  'options' => [
    'responsive' => true,
    'legend' => [
      'position' => 'top'
    ],
    'title' => [
      'display' => true,
      'text' => 'Income'
    ]
  ]
];

Then, if your using the function above to initialize the charts just attach it to a div tag in a data-chart attribute using json_encode (and htmlspecialchars for protection).

<div data-chart="<?=htmlspecialchars(json_encode($configuration));?>"></div>

 

[SaranacLake]

On 2/19/2021 at 8:11 PM, kicken said:

Only the basics if you stick to what the library can natively do given it's options.   The more JS you know the more fancy/custom stuff you can do with it, but for basic chart rendering you only really need to include the library and write:

Good to know.

I suppose that I will open many new doors once I learn Javascript?!

Hopefully I can get to that later this year or next...