PHP Chart data

Hey Bill,

Not entirely sure what the question is here ?

If you want to coerce your data into numbers, then just cast them as int. Maybe this:

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()):
  $arrData["data"][] = [
    "label" => (int)$row["total"],
    "value" => $row["radnew"]
  ];
endwhile;

Select allOpen in new window

I am trying to output the total number of "New Tickets", "In Progress", "Closed" and "No category" as numeric values, based on the sup_contact name,  to use in a chart

OK. Still a little unsure.

Your query currently selects 2 values - total and radnew. total is an aggregate value (count), and radnew is one of your tickets statuses. I don't know what you're planning to use for the charts, but I would guess you'd have the radnew as the label and the total as the value. Because mysqli returns numbers as strings, that's why you need to cast the total to an integer:

$arrData["data"][] = [
  "label" => $row["radnew"],
  "value" =>(int)$row["total"]
];

Select allOpen in new window

Isn't that exactly what you're after ?

The following code is the actual code from the page and it is working perfectly but I need to change - I want to do the following:
This line "$strQuery = "SELECT sup_contact, sup_timespent FROM support WHERE sup_contact='Bill'";"
is selecting numeric values from the database, which is working
BUT
I need to select another field called "radnew" (in place of sup_timespent)  which has one of the following values in the rows associated with the sup_contact name:

New Ticket
In Progress
Closed
No Category

I need the totals of each value in all the rows associated with the particular sup_contact name sent to the following line:
"value" => $row["sup_timespent"]
So it will read: "value" => $row["radnew"]
The idea is that this will then be available for the chart to use as data as it will be numeric
I had this working some time ago but lost the code

Hope I am making sense

<?php

/* Include the `fusioncharts.php` file that contains functions  to embed the charts. */

include_once("includes/fusioncharts.php");

/* The following 4 code lines contain the database connection information. Alternatively, you can move these code lines to a separate file and include the file here. You can also modify this code based on your database connection. */

   $hostdb = "localhost";  // MySQl host
   $userdb = "root";  // MySQL username
   $passdb = "xxxxxx";  // MySQL password
   $namedb = "inventas";  // MySQL database name

   // Establish a connection to the database
   $dbhandle = new mysqli($hostdb, $userdb, $passdb, $namedb);

   /*Render an error message, to avoid abrupt failure, if the database connection parameters are incorrect */
   if ($dbhandle->connect_error) {
    exit("There was an error with your connection: ".$dbhandle->connect_error);
   }
?> 

    <?php
      // Form the SQL query that returns the top 10 most populous countries
      $strQuery = "SELECT sup_contact, sup_timespent FROM support WHERE sup_contact='Bill'";

      // Execute the query, or else return the error message.
      $result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

      // If the query returns a valid response, prepare the JSON string
      if ($result) {
          // The `$arrData` array holds the chart attributes and data
          $arrData = array(
              "chart" => array(
                  "caption" => "Ticket Status for BV",
                  "subCaption" => "Hello sub caption",
                  "showValues" => "1",
                  "theme" => "fusion",
                  "showLegend" => "1",
                  "exportEnabled" => "1",
                  "legendIconScale" => "1"
                )
            );

          $arrData["data"] = array();

  // Push the data into the array
          while($row = mysqli_fetch_array($result)) {
            array_push($arrData["data"], array(
                "label" => $row["sup_contact"],
                "value" => $row["sup_timespent"]
                )
            );
          }

          /*JSON Encode the data to retrieve the string containing the JSON representation of the data in the array. */

          $jsonEncodedData = json_encode($arrData);

  /*Create an object for the column chart using the FusionCharts PHP class constructor. Syntax for the constructor is ` FusionCharts("type of chart", "unique chart id", width of the chart, height of the chart, "div id to render the chart", "data format", "data source")`. Because we are using JSON data to render the chart, the data format will be `json`. The variable `$jsonEncodeData` holds all the JSON data for the chart, and will be passed as the value for the data source parameter of the constructor.*/

          $columnChart = new FusionCharts("pie3D", "myFirstChart7" , 400, 250, "chart-7", "json", $jsonEncodedData);

          // Render the chart
          $columnChart->render();

          // Close the database connection
          $dbhandle->close();
      }
    ?>
    <div id="chart-7"><!-- Fusion Charts will render here--></div>

Select allOpen in new window

Not just MySQL and virtually all databases, but an 'echo' statement in PHP is going to echo the number as text.  Can you show us what you're getting that isn't working?

So it's FusionCharts PHP class that is requiring the numbers?  Have you implemented their demo?

Yes
The chart is working perfectly, as long as the values are numeric, as I mentioned
I just need to be able to pass numeric values rather than text values hence needing to convert the text values to numeric totals
Unless there is another way

You have this in your query:

SELECT COUNT(*) AS `total`, `radnew`
GROUP BY `radnew`

So your query selects 2 columns for each row. One will be called total and will be a the number you're looking for. The other will be called radnew and will contain one of the text values - 'new ticket','in progress','closed','no category', so your query result will look like something like this:

total : radnew
2 : 'new ticket'
7 : 'in progress'
4 : 'closed'
27 : 'no category'

I'm really struggling to see what you need that you're not currently getting.

If you want the number, then access the 'total' column. The 'radnew' column is going to contain the text. You can't 'convert' that text to a number - that just doesn't make any sense !!

I'm clearly missing something here - just can't figure out what.

This code (immediately below) used to work but for some reason it doesn't work on my new WAMP installation
As you can see, it is taking the "radnew" values and grouping/totalling them and giving these values for use in the array
When I access this .php page from the browser, I see all the correct array values presented on the page
Problem is the chart is just not being presented - no error, just an empty space on the page

<?php
//address of the server where db is installed
$servername = "localhost";

//username to connect to the db
//the default value is root
$username = "root";

//password to connect to the db
//this is the value you would have specified during installation of WAMP stack
$password = "xxxxx";

//name of the db under which the table is created
$dbName = "inventas";

//establishing the connection to the db.
$conn = new mysqli($servername, $username, $password, $dbName);

//checking if there were any error during the last connection attempt
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

//the SQL query to be executed
//$query = "SELECT * FROM testtable";

//storing the result of the executed query
//$result = $conn->query($query);


     $query = <<< QUERY
SELECT COUNT(*) AS `total`, `radnew`
FROM `support` 
WHERE
  `sup_contact` = 'Bill' AND 
  `radnew` IN ('new ticket','in progress','closed','no category') 
GROUP BY `radnew`
ORDER BY 
CASE `radnew`
      WHEN 'New Ticket' THEN 0
      WHEN 'In Progress' THEN 1
      WHEN 'Closed' THEN 2
      WHEN 'No Category' THEN 3
   END
QUERY;
$stmt = $conn->prepare($query);
$stmt->execute();
$result = $stmt->get_result();

//initialize the array to store the processed data
$jsonArray = array();

//check if there is any data returned by the SQL Query

  //Converting the results into an associative array
  while($row = $result->fetch_array(MYSQLI_ASSOC)){
    $jsonArrayItem = array();
    $jsonArrayItem['label'] = $row['radnew'];
    $jsonArrayItem['value'] = $row['total'];
    //append the above created object into the main array.
    array_push($jsonArray, $jsonArrayItem);
  }


//Closing the connection to DB
$conn->close();

//set the response content type as JSON
header('Content-type: application/json');
//output the return value of json encode using the echo function. 
echo json_encode($jsonArray);
?>

Select allOpen in new window

The following code successfully builds a chart but the values being entered are numeric values from a different set of row data from the database table
Because the code (immediately above) does not work with my WAMP installation, I wanted to use the code below
to do the same thing - the code structure is working in my WAMP install so that is why I wanted to see if I could adapt it to do what I need it to do

<?php

/* Include the `fusioncharts.php` file that contains functions  to embed the charts. */

include_once("includes/fusioncharts.php");

/* The following 4 code lines contain the database connection information. Alternatively, you can move these code lines to a separate file and include the file here. You can also modify this code based on your database connection. */

   $hostdb = "localhost";  // MySQl host
   $userdb = "root";  // MySQL username
   $passdb = "xxxxxx";  // MySQL password
   $namedb = "inventas";  // MySQL database name

   // Establish a connection to the database
   $dbhandle = new mysqli($hostdb, $userdb, $passdb, $namedb);

   /*Render an error message, to avoid abrupt failure, if the database connection parameters are incorrect */
   if ($dbhandle->connect_error) {
    exit("There was an error with your connection: ".$dbhandle->connect_error);
   }
?> 

    <?php
      // Form the SQL query that returns the top 10 most populous countries
      $strQuery = "SELECT sup_contact, sup_timespent FROM support WHERE sup_contact='Bill'";

      // Execute the query, or else return the error message.
      $result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

      // If the query returns a valid response, prepare the JSON string
      if ($result) {
          // The `$arrData` array holds the chart attributes and data
          $arrData = array(
              "chart" => array(
                  "caption" => "Ticket Status for BV",
                  "subCaption" => "Bill Vallins Ticket Status Time Spent",
                  "showValues" => "1",
                  "theme" => "fusion",
                  "showLegend" => "1",
                  "exportEnabled" => "1",
                  "legendIconScale" => "1"
                )
            );

          $arrData["data"] = array();

  // Push the data into the array
          while($row = mysqli_fetch_array($result)) {
            array_push($arrData["data"], array(
                "label" => $row["sup_contact"],
                "value" => $row["sup_timespent"]
                )
            );
          }

          /*JSON Encode the data to retrieve the string containing the JSON representation of the data in the array. */

          $jsonEncodedData = json_encode($arrData);

  /*Create an object for the column chart using the FusionCharts PHP class constructor. Syntax for the constructor is ` FusionCharts("type of chart", "unique chart id", width of the chart, height of the chart, "div id to render the chart", "data format", "data source")`. Because we are using JSON data to render the chart, the data format will be `json`. The variable `$jsonEncodeData` holds all the JSON data for the chart, and will be passed as the value for the data source parameter of the constructor.*/

          $columnChart = new FusionCharts("pie3D", "myFirstChart" , 400, 250, "chart-1", "json", $jsonEncodedData);

          // Render the chart
          $columnChart->render();

          // Close the database connection
          $dbhandle->close();
      }
    ?>
    <div id="chart-1"><!-- Fusion Charts will render here--></div>

Select allOpen in new window

I must have some java script problem somewhere which stops the first code from working

New code:

<?php

/* Include the `fusioncharts.php` file that contains functions  to embed the charts. */

include_once("includes/fusioncharts.php");

/* The following 4 code lines contain the database connection information. Alternatively, you can move these code lines to a separate file and include the file here. You can also modify this code based on your database connection. */

   $hostdb = "localhost";  // MySQl host
   $userdb = "root";  // MySQL username
   $passdb = "xxxxxxx";  // MySQL password
   $namedb = "inventas";  // MySQL database name

   // Establish a connection to the database
   $dbhandle = new mysqli($hostdb, $userdb, $passdb, $namedb);

   /*Render an error message, to avoid abrupt failure, if the database connection parameters are incorrect */
   if ($dbhandle->connect_error) {
    exit("There was an error with your connection: ".$dbhandle->connect_error);
   }
?> 

    <?php
      // Form the SQL query that returns the top 10 most populous countries
      $strQuery = "SELECT sup_contact, radnew FROM support WHERE sup_contact='Bill'";

      // Execute the query, or else return the error message.
      $result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

      // If the query returns a valid response, prepare the JSON string
      if ($result) {
          // The `$arrData` array holds the chart attributes and data
          $arrData = array(
              "chart" => array(
                  "caption" => "Ticket Status for BV",
                  "subCaption" => "Bill Vallins Ticket Status Time Spent",
                  "showValues" => "1",
                  "theme" => "fusion",
                  "showLegend" => "1",
                  "exportEnabled" => "1",
                  "legendIconScale" => "1"
                )
            );

          $arrData["data"] = array();

  // Push the data into the array
          while($row = mysqli_fetch_array($result)) {
            array_push($arrData["data"], array(
                "label" => $row["radnew"],
                "value" => $row["total"]
                )
            );
          }

          /*JSON Encode the data to retrieve the string containing the JSON representation of the data in the array. */

          $jsonEncodedData = json_encode($arrData);

  /*Create an object for the column chart using the FusionCharts PHP class constructor. Syntax for the constructor is ` FusionCharts("type of chart", "unique chart id", width of the chart, height of the chart, "div id to render the chart", "data format", "data source")`. Because we are using JSON data to render the chart, the data format will be `json`. The variable `$jsonEncodeData` holds all the JSON data for the chart, and will be passed as the value for the data source parameter of the constructor.*/

          $columnChart = new FusionCharts("pie3D", "myFirstChart" , 400, 250, "chart-1", "json", $jsonEncodedData);

          // Render the chart
          $columnChart->render();

          // Close the database connection
          $dbhandle->close();
      }
    ?>
    <div id="chart-1"><!-- Fusion Charts will render here--></div>

Select allOpen in new window

I get the following message. Isn't that because the code above is not replicating the code which was working in the previous code page (which was using java) but which is not working now?
I need the values from the radnew rows to be fed into the array as numeric totals so they can be used by the chart. As it stands, the "total" does not exist. I need the selected values from the query to be totalled as follows (as in the code which does not work at present with the java setup but which does dump the correct data:
I need to be able to use some of the following code in the code you just suggested  immediately above (I also tried switching the radnew and total references around in the "push the data into the array"):

 $query = <<< QUERY
SELECT COUNT(*) AS `total`, `radnew`
FROM `support` 
WHERE
  
  `radnew` IN ('new ticket','in progress','closed','no category') 
GROUP BY `radnew`
ORDER BY 
CASE `radnew`
      WHEN 'New Ticket' THEN 0
      WHEN 'In Progress' THEN 1
      WHEN 'Closed' THEN 2
      WHEN 'No Category' THEN 3
   END
QUERY;

Select allOpen in new window

This should then convert the text values to numeric values
This is the error message I get when running your suggestion:

This is the array I get when using the .PHP page which incorporates the following code (in the java page which is not showing the chart successfully):

 
  `radnew` IN ('new ticket','in progress','closed','no category') 
GROUP BY `radnew`
ORDER BY 
CASE `radnew`
      WHEN 'New Ticket' THEN 0
      WHEN 'In Progress' THEN 1
      WHEN 'Closed' THEN 2
      WHEN 'No Category' THEN 3
   END
QUERY;

Select allOpen in new window

Chris
Absolutely spot on - works perfectly
I am sure I tried to insert the code ($strQuery = <<< QUERY ......) when I first started looking at the project but I think I missed changing the the following:
"label" => $row["radnew"],
"value" => $row["total"]
I also see the query error - using the sup_contact in the wrong place
Thanks so much for your persistence on this - you come through every time!!!!

Code working:

<?php

/* Include the `fusioncharts.php` file that contains functions  to embed the charts. */

include_once("includes/fusioncharts.php");

/* The following 4 code lines contain the database connection information. Alternatively, you can move these code lines to a separate file and include the file here. You can also modify this code based on your database connection. */

   $hostdb = "localhost";  // MySQl host
   $userdb = "root";  // MySQL username
   $passdb = "xxxxxxx";  // MySQL password
   $namedb = "inventas";  // MySQL database name

   // Establish a connection to the database
   $dbhandle = new mysqli($hostdb, $userdb, $passdb, $namedb);

   /*Render an error message, to avoid abrupt failure, if the database connection parameters are incorrect */
   if ($dbhandle->connect_error) {
    exit("There was an error with your connection: ".$dbhandle->connect_error);
   }
?> 

    <?php

$strQuery = <<< QUERY
SELECT COUNT(*) AS `total`, `radnew`
FROM `support` 
WHERE`sup_contact` = 'Bill' AND
    `radnew` IN ('new ticket','in progress','closed','no category') 
GROUP BY `radnew`
ORDER BY 
CASE `radnew`
      WHEN 'New Ticket' THEN 0
      WHEN 'In Progress' THEN 1
      WHEN 'Closed' THEN 2
      WHEN 'No Category' THEN 3
   END
QUERY;

      // Execute the query, or else return the error message.
      $result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

      // If the query returns a valid response, prepare the JSON string
      if ($result) {
          // The `$arrData` array holds the chart attributes and data
          $arrData = array(
              "chart" => array(
                  "caption" => "Ticket Status for BV",
                  "subCaption" => "Bill Vallins Ticket Status Time Spent",
                  "showValues" => "1",
                  "theme" => "fusion",
                  "showLegend" => "1",
                  "exportEnabled" => "1",
                  "legendIconScale" => "1"
                )
            );

          $arrData["data"] = array();

  // Push the data into the array
          while($row = mysqli_fetch_array($result)) {
            array_push($arrData["data"], array(
                "label" => $row["radnew"],
                "value" => $row["total"]
                )
            );
          }

          /*JSON Encode the data to retrieve the string containing the JSON representation of the data in the array. */

          $jsonEncodedData = json_encode($arrData);

  /*Create an object for the column chart using the FusionCharts PHP class constructor. Syntax for the constructor is ` FusionCharts("type of chart", "unique chart id", width of the chart, height of the chart, "div id to render the chart", "data format", "data source")`. Because we are using JSON data to render the chart, the data format will be `json`. The variable `$jsonEncodeData` holds all the JSON data for the chart, and will be passed as the value for the data source parameter of the constructor.*/

          $columnChart = new FusionCharts("pie3D", "myFirstChart7" , 400, 250, "chart-7", "json", $jsonEncodedData);

          // Render the chart
          $columnChart->render();

          // Close the database connection
          $dbhandle->close();
      }
    ?>
    <div id="chart-7"><!-- Fusion Charts will render here--></div>
  

Select allOpen in new window

Here you go Chris - after all your hard work
The chart data now populating:

Awesome !!

So glad we got there in the end. Good effort, and the charts look great :)

Once again Chris - I can't thank you enough
You are an incredibly valuable resource for Experts exchange and the main reason why I renewed my membership as most of my questions revolve around PHP and websites