Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Ashley Crossland
Ashley Crossland
10,868 Points
Posted by Ashley Crossland
Ashley Crossland
10,868 Points

JQwidgets stacked column graph and JSON file from MySQL Database help needed!

I have run into a problem creating a stacked column graph, with data coming from a database. Here is a link to the charts I am using: http://www.jqwidgets.com/jquery-stacked-columns-chart/

I have managed to create a working chart, but not exactly how I want it.

Here is how it is currently looking:

Stacked Graph

What I am trying to achieve, is to have each column as a question, split up into High, Medium, Low, Not Applicable and Don't know. The Key/Legend would then be the values High, Medium etc.

My JSON file which makes up the graph currently looks like this (I have added hard returns to make it easier to read):

[{"Question Answer":"Dont Know", "Question One Count":"1", "Question Two Count":"4", "Question Three Count":"3", "Question Four Count":"1"},

{"Question Answer":"High", "Question One Count":"3", "Question Two Count":"1", "Question Three Count":"1", "Question Four Count":"1"},

{"Question Answer":"Low", "Question One Count":"4", "Question Two Count":"4", "Question Three Count":"2", "Question Four Count":"3"},

{"Question Answer":"Medium", "Question One Count":"2", "Question Two Count":"1", "Question Three Count":"1", "Question Four Count":"3"},

{"Question Answer":"Not Applicable", "Question One Count":"1", "Question Two Count":"1", "Question Three Count":"4", "Question Four Count":"3"}]

Basically I need to get all of the question one values into the first section, then all of the question two values into the second section and so on...

The PHP to make this JSON is:

<?php 

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $results_submit_code = trim($_POST["results_submit_code"]);
    }


//if(isset($_POST['submit']))
//{


$con=mysqli_connect("localhost","root","root","ruralvibesresults");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$queryone = "SELECT COUNT(*) AS questionone FROM resultstable GROUP BY questionone HAVING ( COUNT(questionone) > 0 )";
$querytwo = "SELECT questionone, COUNT(*) FROM resultstable GROUP BY questionone HAVING ( COUNT(questionone) > 0 )";
$querythree = "SELECT COUNT(*) AS questiontwo FROM resultstable GROUP BY questiontwo HAVING ( COUNT(questiontwo) > 0 )";
$queryfour = "SELECT questiontwo, COUNT(*) FROM resultstable GROUP BY questiontwo HAVING ( COUNT(questiontwo) > 0 )";
$queryfive = "SELECT COUNT(*) AS questionthree FROM resultstable GROUP BY questionthree HAVING ( COUNT(questionthree) > 0 )";
$querysix = "SELECT questionthree, COUNT(*) FROM resultstable GROUP BY questionthree HAVING ( COUNT(questionthree) > 0 )";
$queryseven = "SELECT COUNT(*) AS questionfour FROM resultstable GROUP BY questionfour HAVING ( COUNT(questionfour) > 0 )";
$queryeight = "SELECT questionfour, COUNT(*) FROM resultstable GROUP BY questionfour HAVING ( COUNT(questionfour) > 0 )";

$resultsvaluesone = mysqli_query($con, $queryone);
$resultsone = mysqli_query($con, $querytwo);
$resultsvaluestwo = mysqli_query($con, $querythree);
$resultstwo = mysqli_query($con, $queryfour);
$resultsvaluesthree = mysqli_query($con, $queryfive);
$resultsthree = mysqli_query($con, $querysix);
$resultsvaluesfour = mysqli_query($con, $queryseven);
$resultsfour = mysqli_query($con, $queryeight);

while($rowsvalues = mysqli_fetch_array($resultsone) and 
      $rowsvaluestwo = mysqli_fetch_array($resultsvaluesone) and
      $rowsvaluesthree = mysqli_fetch_array($resultstwo) and 
      $rowsvaluesfour = mysqli_fetch_array($resultsvaluestwo) and
      $rowsvaluesfive = mysqli_fetch_array($resultsthree) and 
      $rowsvaluessix = mysqli_fetch_array($resultsvaluesthree) and
      $rowsvaluesseven = mysqli_fetch_array($resultsfour) and 
      $rowsvalueseight = mysqli_fetch_array($resultsvaluesfour)
      )


 {
    $values[] = array(
      'Question One Answer' => $rowsvalues['questionone'],
      'Question One Count' => $rowsvaluestwo['questionone'],
      'Question Two Count' => $rowsvaluesfour['questiontwo'],
      'Question Three Count' => $rowsvaluessix['questionthree'],
      'Question Four Count' => $rowsvalueseight['questionfour'],
      );
 }

echo json_encode($values);

mysqli_close($con);

?>

Any help would be gratefully received.

Thank you.