The New Year Breaks my Charts

The site works fine as long as there is data to retrieve. When the year rolls over, all the data is reset to zero. I use Google Charts on other sites and at the charts simply show the message “No data” until the new year starts getting input to the database

• What version of the Chart Builder plugin are you using? – Version 20.0.7.1 
• What is your PHP version? PHP Version ea-php82
• What type of chart are you using? Pie charts and Vertical bar charts
• The specific queries being used in the charts. there are nearly 20 charts. The underlying queries are now inaccessible as I mention in my original post. If I place false data into the database the chart query is accessible here is one such sample below. All affected queries are similar in nature.
• Is it connected to an external database? Yes
• Are there any specific errors in the error.log related to our plugin? There has been a critical error on this website.

Sample pie chart query

SELECT haskellc_diary2.findings.risk, COUNT(haskellc_diary2.findings.risk) AS "COUNT"

FROM haskellc_diary2.audit

INNER JOIN haskellc_diary2.findings ON haskellc_diary2.audit.audit_id = haskellc_diary2.findings.aud_id

WHERE haskellc_diary2.audit.audit_group = "Manager" AND YEAR(haskellc_diary2.audit.date) = YEAR(NOW())

GROUP BY haskellc_diary2.findings.risk

Hi Jasmine,

I do not want the prior year data (2024) hence the query YEAR(NOW()). At the new year there will be no data. This is as expected. As I mentioned in my original post, I use Google charts on other dashboards to illustrate only the current years data. Those Google charts function by displaying “No Data” at the new year rather than rendering the page as disabled and showing a critical error message. You have told me in the past that yo are also using Google charts for your plugin. You should be able to get the same functionality. Since this is a WordPress site I am unable to implement the Google charts without using a plugin.

I have pasted the code for a pie chart I use on another dashboard using Google Charts. This chart and many others I have work fine at the new year, displaying “No Data” until the database is populated for the new year. I am not showing the database connection for security reason as you no doubt understand.

I hope you can help me. It is an issue that would require me to find another way to get the chart functioning at the new year. I am about to retire and don’t want to leave my company with a dashboard that breaks on the next new year.

I am using a free chart plugin on my wordpress sites that simply shows a zero in bar charts if there is no data for the new year. Or simply a blank chart in the case of Pie charts. But the page is still accessible to view other items of information. I prefer using your plugin as it is more versatile except for this issue. I must manually go into the wordpress dashboard and unpublish the affected charts in order to gain access to any page that has a Chartify chart on it.

Thank you.

    <script type="text/javascript">

// Load Charts and the corechart package.
      google.charts.load('current', {'packages':['corechart']});

// Draw the pie chart for Risk Rating when Charts is loaded.
      google.charts.setOnLoadCallback(drawRiskRatePieChart);

// Draw the pie chart for Risk Rating2 when Charts is loaded.
      google.charts.setOnLoadCallback(drawRiskRate2Chart);
	 
// Callback that draws the pie chart for RiskRate1.
      function drawRiskRatePieChart() {
		  
		  

// Create the data table for RiskRate1.
        var data = google.visualization.arrayToDataTable([
          ['risk', 'count'],
         <?php
		   // read all rows from database table
            $sql = "SELECT project, data, rank FROM risk_data WHERE project = $project_id ORDER by rank ASC";
            $result = $connection->query($sql);
            //read data of each row
            WHILE($row = $result->fetch_assoc()){ 
				echo  $row["data"] ;               
            }
		  ?>
        ]);

// Set options for RiskRate1 pie chart.
        var options = {
          title: 'Audit Deficiency Found',
		  colors: ['#31B404', '#f9ab32', '#d332f9', '#FF0000'],
		  backgroundColor: 'transparent',
		  width: 450,
		  height: 350,
		  pieHole: 0.3,
		  is3D: true
        };

// Instantiate and draw the chart for RiskRate1.
        var chart = new google.visualization.PieChart(document.getElementById('risk_rate'));

        chart.draw(data, options);
      }
	  
    </script>

Below is the mySQL view query that pulls the data from the database for the chart above.

SELECT
    haskellc_diary2.audit.project AS project,
    haskellc_diary2.audit.findings AS rank,
    haskellc_diary2.audit.date AS date,
    CONCAT(
        '[',
        '\'',
        haskellc_diary2.audit.risk,
        '\'',
        ',',
        ' ',
        COUNT(haskellc_diary2.audit.project),
        ']',
        ','
    ) AS data,
    COUNT(0) AS sort
FROM
    haskellc_diary2.audit
WHERE
    (
        YEAR(haskellc_diary2.audit.date) = YEAR(NOW()))
    GROUP BY
        haskellc_diary2.audit.risk,
        haskellc_diary2.audit.project
    ORDER BY
        haskellc_diary2.audit.risk