Chart display on page

Asked

Viewed 66 times

0

I have this chart with the following query to the database:

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['bar']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Ano','Janeiro', 'Fevereiro','Marco', 'Abril','Maio', 'Junho','Julho', 'Agosto','Setembro', 'Outubro','Novembro', 'Dezembro'],
          <?php 
                   $query = "SELECT `B`.`DescricaoProd`,
       YEAR(`A`.`Data`) AS `Ano`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 1 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Janeiro`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 2 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Fevereiro`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 3 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Marco`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 4 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Abril`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 5 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Maio`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 6 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Junho`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 7 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Julho`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 8 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Agosto`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 9 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Setembro`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 10 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Outubro`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 11 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Novembro`,
       CAST(SUM(IF(MONTH(`A`.`Data`) = 12 , `A`.`Quantidade`, 0)) AS DECIMAL (15 , 0 )) AS `Dezembro`

FROM(
SELECT `centrodb`.`regSaidahigieneAntigo`.`Produto`,        
       `centrodb`.`regSaidahigieneAntigo`.`DataAtribuicao` AS `Data`,
       `centrodb`.`regSaidahigieneAntigo`.`Quantidade`

FROM `centrodb`.`regSaidahigieneAntigo`

UNION ALL

SELECT `A`.`Produto`,        
       `A`.`DataAtribuicao` AS `Data`,
       `A`.`Quantidade`

FROM `centrodb`.`regSaidahigiene` AS `A` 

UNION ALL

SELECT `centrodb`.`RegSaidaLuvas`.`Produto`,
       `centrodb`.`RegSaidaLuvas`.`DataSaida`,
       `centrodb`.`RegSaidaLuvas`.`QtaHigiene`


 FROM `centrodb`.`RegSaidaLuvas`

 WHERE `centrodb`.`RegSaidaLuvas`.`Produto` IS NOT NULL) AS `A` LEFT OUTER JOIN `centrodb`.`ProdHigiene` AS `B`
ON `B`.`IDProd` = `A`.`Produto`

WHERE YEAR(`A`.`Data`) = EXTRACT(YEAR FROM CURDATE() - INTERVAL 0 YEAR)
      OR
      YEAR(`A`.`Data`) = EXTRACT(YEAR FROM CURDATE() - INTERVAL 1 YEAR)

GROUP BY `B`.`DescricaoProd`,  `Ano`

ORDER BY `B`.`DescricaoProd`, YEAR(`A`.`Data`)";    
                   $exec = mysqli_query($conn,$query);

                   while($row = mysqli_fetch_array($exec)){    
                      echo "['".$row['Ano']."',".$row['Janeiro'].",".$row['Fevereiro'].",".$row['Marco'].",".$row['Abril'].",".$row['Maio'].",".$row['Junho'].",".$row['Julho'].",".$row['Agosto'].",".$row['Setembro'].",".$row['Outubro'].",".$row['Novembro'].",".$row['Dezembro']."],";
                   }
                ?>     
        ]);

        var options = {
          chart: {
            title: 'Company Performance',
            subtitle: 'Sales, Expenses, and Profit: 2014-2017',
          }
        };

        var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

        chart.draw(data, google.charts.Bar.convertOptions(options));
      }
    </script>
  </head>
  <body>
    <div id="columnchart_material" style="width: 2000px; height: 1000px;"></div>
  </body>
</html>

Everything is working correctly with the code, I am not able to present the data as intended. In the images I show how the results appear to me in the graph in two ways:

inserir a descrição da imagem aqui inserir a descrição da imagem aqui

In the first image appears the year and month but does not present the name of the product to which that quantity refers. In the second image appears the product and the month, but does not show the year to which it refers, once I show the result of the current year and the previous year.

What I want to show on the chart is the name of the product, the total spent per month and the year and month to which that total belongs.

php html mysql

  • Have you used Google Charts? Or another Graphics api?

  • I used google Harts

  • Give me the link to the chart you used

  • link

  • Suggest you use another google chart?

  • If the way I suggested does not answer, I think better to use another graphic msm

Show 1 more comment

1 answer

1


Where you name the columns, I think it looks better with the name of the products

var data = google.visualization.arrayToDataTable([
          ['DescricaoProd','Janeiro', 'Fevereiro','Marco', 'Abril','Maio', 'Junho','Julho', 'Agosto','Setembro', 'Outubro','Novembro', 'Dezembro'],

And in the loop where you print, I couldn’t make it possible to "skip line" so I put the year aside, I don’t know if it suits you in the right way.

 while($row = mysqli_fetch_array($exec)){    
                      echo "['".$row['DescricaoProd']." - ".$row['Ano']."',".$row['Janeiro'].",".$row['Fevereiro'].",".$row['Marco'].",".$row['Abril'].",".$row['Maio'].",".$row['Junho'].",".$row['Julho'].",".$row['Agosto'].",".$row['Setembro'].",".$row['Outubro'].",".$row['Novembro'].",".$row['Dezembro']."],";
                   }

If this does not suit you and you want the information "independently", then I suggest you use another graphic

Resp: An example of filter, would make the user before accessing the graph, choose a product, like Alcohol, it goes to a variable ($produto), and when your chart is loaded, Select will exit this way:

$Query = "SELECT * FROM BANCOX.TABELAX WHERE DescricaoProd = '$produto'";

Just one example Energy :D

  • Yes, that’s what I want, I only have another problem, the bars are very thin, I wanted them to be thicker so that the analysis is easier

  • It is leaving small so due to the amount of bars If you harvest a filter from one product displayed at a time, it is better to display the msm

  • and how can I put a filter per product, can put an example?

  • I edited the answer with an example

  • And is it not possible for the user to choose more than 1 product? for example 3 products?

  • it is possible yes, but it depends on your goal is only to show the data, I think one data at a time is enough, now if it is for the purpose of comparison, it depends on how many you want to compare, if it is 2 or more, it depends on you to make the necessary quantity of inputs

  • In the editing part of the filter question, what you put is not enough. I am not able to create the filter just by your example

  • You have to adapt it to your query, and if you need to create an extra page, if you are still having difficulty, I recommend that you create another question with more information than you want and other users can help you

  • But in relation to the example you put to filter by product on the chart you can just put the full example, because you don’t just put the variable in the where

Show 4 more comments

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.