Average of database column

[JD26]

Hi everyone,

I’ve been trying this for days now with the example code, but I have no idea how this could work.. would really appreciate some help with this.

This is my database:

I want to show the average of each column in a column chart, like the “Column Charts with Multiple Axes” example.
So the average of PD1 in blue and of PD2 in red, SVK1 in blue, SVK2 in red and so on. I know I can get the average with the SQL AVG() function but then I don’t know how to put them into the dataPoints array.

Can anyone help?

[JD26]

Been trying something like this:

foreach ($link->query('Select AVG(PD1), AVG(PD2) from table') as $row) {
        $PD1 = $row['AVG(PD1)'];
        $PD2 = $row['AVG(PD2)'];
        array_push($dataPoints,"$PD1","$PD2");
        array_push($dataPoints2, array("y"=> $dataPoints));
    };

but it doesn’t work

[Shashi Ranjan]

@jd26,

Can you kindly create a sample project reproducing the issue and share it over Google-Drive or Onedrive along with sample database so that we can look into the code, run it locally to understand the scenario better and help you resolve?

From what we have observed, sometimes things get delayed mostly when we are not able to reproduce the issue or not able to understand the exact requirements or the solution that we provide may not work properly due to the variation in chart-options being used by you and us.

Having a sample project helps us in figuring out the issue and suggesting an appropriate solution accordingly.

—
Shashi Ranjan
Team CanvasJS

[JD26]

@Sashi Ranjan

I created a sample project :)
https://drive.google.com/drive/folders/18kKteS7UA9l-ruLJYA5HcOlYKJbkei0A?usp=sharing

I appreciate your help!

[JD26]

@sashiranjan

Do you still want to help me? Would really appreciate it

[Shashi Ranjan]

@jd26,

Column chart accepts only one y-value for a dataPoint. Hence, passing just one y-value to dataPoints should work fine in your case. Please take a look at this updated sample project for the same.

—
Shashi Ranjan
Team CanvasJS

[JD26]

Thank you so much, it works!

Now I just want to add labels to the graph. Could you tell me how I can do this? This doesn’t work:

[Shashi Ranjan]

@jd26,

Pushing the label and y-value into the dataPoints array using this array_push($dataPoints1, array("label" => "PD", "y"=>"$PD1")); approach and passing the encoded array to the dataPoints as dataPoints: <?php echo json_encode($dataPoints2, JSON_NUMERIC_CHECK); ?> should work fine in your case. Please take a look at this updated sample project for the same.

—
Shashi Ranjan
Team CanvasJS

[JD26]

Thank you!!

I’m sorry I’m asking so much.. now I want to add more column pairs. I tried to make a multidimensional array but it doesn’t work.

Could you take a look at the project again?
https://drive.google.com/open?id=18kKteS7UA9l-ruLJYA5HcOlYKJbkei0A

[JD26]

okay nevermind, I managed to loop it correctly. Chart looks good now!
Thank you so much for your help!

[Shashi Ranjan]

@jd26,

Glad you figured it out :)

—
Shashi Ranjan
Team CanvasJS

[JD26]

New problem.. I want to change the title of the chart with PHP (set a variable as the title) but then my chart isn’t displayed anymore. How can I use php in the title?

I tried
text: < ? php $title ? >
and
text:< ? php echo $title; ? >

(without the unnecessary spaces obviously, it won’t let me post the code here otherwise)

[JD26]

okay so this worked (I feel stupid)
text: < ?php echo json_encode($title);? >,

[Shashi Ranjan]

@jd26,

Glad you figured it out :)

—
Shashi Ranjan
Team CanvasJS