HTML graphs from SQL query

Question

rtipton 0 Newbie Poster

9 Years Ago

I am trying to make a graph from data that i have in my sql database. Below is the exmaple of the data that i am pulling from my data base.

Region LC_count MC_count NC_count B_count LL_count EL_count LR_count CR_count
MOUNTAIN 0 722 542 0 7155 0 0 1444
NO.CAL/NEVADA 0 0 0 13 0 0 0 0
PACIFIC NORTHWEST 25 0 0 24 0 4848 0 414
SOUTHERN_CALIFORNIA 0 0 14514 0 245 3044 0 1470
SOUTHWEST 0 618 0 1 2958 0 0 1319

Here is the sql data that i am using

mysql_select_db($database_TechSupport, $TechSupport);
$query_Atotal = sprintf("SELECT `MailingList`.`Region`,
Sum(CASE WHEN(`CellLoad`.`Vendor` = 'Lucent' AND `CellLoad`.`Event` = 'Cell' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS LtotalC,
Sum(CASE WHEN(`CellLoad`.`Vendor` = 'Moto' AND `CellLoad`.`Event` = 'Cell' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS MtotalC,
Sum(CASE WHEN(`CellLoad`.`Vendor` = 'Nortel' AND `CellLoad`.`Event` = 'Cell' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE` ) THEN Total1 ELSE 0 END) AS NtotalC,
Sum(CASE WHEN(`CellLoad`.`Vendor` = 'Lucent' AND `CellLoad`.`Event` = 'BWM' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE` ) THEN Total1 ELSE 0 END) AS LtotalB,
Sum(CASE WHEN(`CellLoad`.`Event` = 'Lucent_LTE' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS LtotalL,
Sum(CASE WHEN(`CellLoad`.`Event` = 'Ericsson_LTE' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS EtotalL,
Sum(CASE WHEN(`CellLoad`.`Event` = 'Lucent_Router' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS LtotalR,
Sum(CASE WHEN(`CellLoad`.`Event` = 'Cisco_Router' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS CtotalR,
Sum(CASE WHEN(`CellLoad`.`Event` = 'Cell' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE` ) THEN Total1 ELSE 0 END) AS Tcell,
Sum(CASE WHEN(`CellLoad`.`Event` = 'BWM' AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE` ) THEN Total1 ELSE 0 END) AS Tbwm,
Sum(CASE WHEN((`CellLoad`.`Event` = 'Lucent_LTE' OR `CellLoad`.`Event` = 'Ericsson_LTE') AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS Tlte,
Sum(CASE WHEN((`CellLoad`.`Event` = 'Lucent_Router' OR `CellLoad`.`Event` = 'Cisco_Router') AND `CellLoad`.`Switch` = `MailingList`.`Switch` AND `CellLoad`.`NE` = `MailingList`.`NE`) THEN Total1 ELSE 0 END) AS Trouter
FROM CellLoad,MailingList 
WHERE YEAR(`CellLoad`.`Date_Start`) = '2014' AND `MailingList`.`Area` = 'WEST' AND `CellLoad`.`Put_on_Schedule` = ('Email' or 'Done')
GROUP BY `MailingList`.`Region`
ORDER BY `MailingList`.`Region`", GetSQLValueString($col_Rtotal, "text"));
$Atotal = mysql_query($query_Atotal, $TechSupport) or die(mysql_error());
$row_Atotal = mysql_fetch_assoc($Atotal);
$totalRows_Atotal = mysql_num_rows($Atotal);

Here is the code for the for the graph

  // get form data
  if(count($_REQUEST)) foreach($_REQUEST as $name => $val) eval('$' . $name . ' = "' . $val . '";');

  // initialize values
  if(!$graphCreate) 
    {
                $graphType = 'vBar';
                $graphShowValues = 1;
                $graphLabels = $row_Atotal['Region'];
                $graphValues = $row_Atotal['LtotalC'].','.$row_Atotal['MtotalC'].','.$row_Atotal['NtotalC'].','.$row_Atotal['LtotalB'].','.$row_Atotal['LtotalL'].','.$row_Atotal['EtotalL'].','.$row_Atotal['CtotalR'].','.$row_Atotal['LtotalR'];
                $graphBarWidth = 20;
                $graphBarLength = '1.0';
                $graphLabelSize = 12;
                $graphValuesSize = 12;
                $graphPercSize = 12;
                $graphPadding = 10;
                $graphBGColor = '#ABCDEF';
                $graphBorder = '1px solid blue';
                $graphBarColor = '#A0C0F0';
                $graphBarBGColor = '#E0F0FF';
                $graphBarBorder = '2px outset white';
                $graphLabelColor = '#000000';
                $graphLabelBGColor = '#C0E0FF';
                $graphLabelBorder = '2px groove white';
                $graphValuesColor = '#000000';
                $graphValuesBGColor = '#FFFFFF';
                $graphValuesBorder = '2px groove white';
  }
    else 
    {
        if($graphBarWidth == '') $graphBarWidth = 0;
        if($graphBarLength == '') $graphBarLength = 0;
        if($graphLabelSize == '') $graphLabelSize = 0;
        if($graphValuesSize == '') $graphValuesSize = 0;
        if($graphPercSize == '') $graphPercSize = 0;
        if($graphPadding == '') $graphPadding = 0;
    }

how do i get my data into a graph. I am using HTML-Graphs

html-css php

4 Contributors
6 Replies
175 Views
4 Days Discussion Span
Latest Post 9 Years Ago Latest Post by rtipton

David_50 13 Junior Poster in Training

9 Years Ago

I didn;t know html had a graph side, but there is always the use of characters in a fixd pitch area like <PRE>. I did Google up this: http://www.selenic.com/pipermail/mercurial-devel/2014-June/059722.html

http://www.gerd-tentler.de/tools/phpgraphs/

Edited 9 Years Ago by David_50

Taywin 312 Posting Virtuoso

9 Years Ago

Do you need to tail your $graphLabels array element with a comma? Wouldn't it be easier to use join() as echo join(",", $graphLabels);??? The same way with your $graphValues variable...

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rtipton 0 Newbie Poster · Answer 1 · 2014-12-08T15:00:34+00:00

there is a free program call HTML GRAPH. What i am trying to do is get my sql data into the 2 fileds that would build the graph.

     $graphLabels = $row_Atotal['Region'];
    $graphValues = $row_Atotal['LtotalC'].';'.$row_Atotal['MtotalC'].';'.$row_Atotal['NtotalC'].';'.$row_Atotal['LtotalB'].';'.$row_Atotal['LtotalL'].';'.$row_Atotal['EtotalL'].';'.$row_Atotal['CtotalR'].';'.$row_Atotal['LtotalR']',';

the $graphLabels must be , seperated
the $graphValues must be ; seperated for every value that equals $row_Atotal['Region'] then a , at the end

for example of the data that was provided

$graphLabels = MOUNTAIN,NO.CAL/NEVADA,PACIFIC NORTHWEST,SOUTHERN_CALIFORNIA,SOUTHWEST

$graphValues = 0;722;542;0;7155;0;0;1444,0;0;0;13;0;0;0;0,25;0;0;24;0;4848;0;414,0;0;14514;0;245;3044;0;1470,0;618;0;1;2958;0;0;1319

rtipton 0 Newbie Poster · Answer 2 · 2014-12-08T20:45:17+00:00

so far i have figured out how to set the values that i need. The problem I am having know is that not all the data is getting into the array. The first row from my example is not getting into the array.
Here is the code that i am using

$graphLabels = array();
$graphValues = array();
while ($row_Atotal = mysql_fetch_assoc($Atotal))
{
    $graphLabels[]= ($row_Atotal['Region'].",");
    $graphValues[]= ($row_Atotal['LtotalC'].";".$row_Atotal['MtotalC'].";".$row_Atotal['NtotalC'].";".$row_Atotal['LtotalB'].";".$row_Atotal['LtotalL'].";".$row_Atotal['EtotalL'].";".$row_Atotal['LtotalR'].";".$row_Atotal['CtotalR'].",");
}
echo $graphLabels;
echo $graphValues;

matrixdevuk 71 6t9.me Founder · Answer 3 · 2014-12-09T01:44:31+00:00

Well, if you want to know a good way to do this...
Use ChartJS; get your data into the Javascript and you should be good to go. I use it almost every week. It's excellent with amazing documentation.
Downside: HTML5 only!

rtipton 0 Newbie Poster · Answer 4 · 2014-12-09T13:08:12+00:00

I have figured it out
here is the code that i used for the data

$graphLabels = array();
$graphValues = array();
while ($row_Gtotal = mysql_fetch_assoc($Gtotal))
{
    $graphLabels[]= ($row_Gtotal['Region'].",");
    $graphValues[]= ($row_Gtotal['LtotalC'].";".$row_Gtotal['MtotalC'].";".$row_Gtotal['NtotalC'].";".$row_Gtotal['LtotalB'].";".$row_Gtotal['LtotalL'].";".$row_Gtotal['EtotalL'].";".$row_Gtotal['LtotalR'].";".$row_Gtotal['CtotalR'].",");
}
$graphLabels;
$graphValues;
                $graphType = 'vBar';
                $graphShowValues = 1;
                $graphBarWidth = 20;
                $graphBarLength = '1.0';
                $graphLabelSize = 12;
                $graphValuesSize = 12;
                $graphPercSize = 12;
                $graphPadding = 10;
                $graphBGColor = '#ABCDEF';
                $graphBorder = '1px solid blue';
                $graphBarColor = '#A0C0F0';
                $graphBarBGColor = '#E0F0FF';
                $graphBarBorder = '2px outset white';
                $graphLabelColor = '#000000';
                $graphLabelBGColor = '#C0E0FF';
                $graphLabelBorder = '2px groove white';
                $graphValuesColor = '#000000';
                $graphValuesBGColor = '#FFFFFF';
                $graphValuesBorder = '2px groove white';

        if($graphBarWidth == '') $graphBarWidth = 0;
        if($graphBarLength == '') $graphBarLength = 0;
        if($graphLabelSize == '') $graphLabelSize = 0;
        if($graphValuesSize == '') $graphValuesSize = 0;
        if($graphPercSize == '') $graphPercSize = 0;
        if($graphPadding == '') $graphPadding = 0;

Here is the code for the web page

<?php
    include('../../Graph/graphs.inc.php');
    $graph = new BAR_GRAPH($graphType);
    $graph->labels = $graphLabels;
    $graph->titles = "Region,Count,Percentage";
    $graph->values = $graphValues;
    $graph->legend = "Lucent Cell,Moto Cell,Erricson Cell,Lucent BWM,Lucent LTE,Erricson LTE,Lucent Router,Cisco Router";
    $graph->showValues = $graphShowValues;
    $graph->barWidth = $graphBarWidth;
    $graph->barLength = $graphBarLength;
    $graph->labelSize = $graphLabelSize;
    $graph->absValuesSize = $graphValuesSize;
    $graph->percValuesSize = $graphPercSize;
    $graph->graphPadding = $graphPadding;
    $graph->graphBGColor = $graphBGColor;
    $graph->graphBorder = $graphBorder;
    $graph->barColors = $graphBarColor;
    $graph->barBGColor = $graphBarBGColor;
    $graph->barBorder = $graphBarBorder;
    $graph->labelColor = $graphLabelColor;
    $graph->labelBGColor = $graphLabelBGColor;
    $graph->labelBorder = $graphLabelBorder;
    $graph->absValuesColor = $graphValuesColor;
    $graph->absValuesBGColor = $graphValuesBGColor;
    $graph->absValuesBorder = $graphValuesBorder;
    echo $graph->create();

Thanks for the HELP