Solved

Google Charts

Posted on 2014-02-05
10
298 Views
Last Modified: 2014-02-07
Hello Experts, I've spent countless nights trying to figure out a solution to this probably simple problem I have. Maybe someone can show me what I'm doing wrong or a simpler way to accomplish what im trying to succeed. What I have is 2 pages one PHP page that pulls the sum of all transactions per month and sends that information to the Javascript page when then takes that data as a JSON object and renders it inside the google charts array My php example is below here. if someone can look through it and let me know if im doing something wrong please let me know I need all the help i can get!!

$view       = filter_input(INPUT_POST, 'view');
$log        = new diary();
if (!$view) {
    return false;
} else {
    if ($view == 'MonthlyProfits') 
        {
        $year = filter_input(INPUT_POST, 'year');
        $amount = array();
        for ($i=0; $i < 11; $i++)
            {
            $query = "SELECT sum(transaction_amount) as amount FROM finanical_transactions WHERE (transaction_date BETWEEN '$year-$i-1' AND  '$year-$i-31' AND transaction_type_code = '12000')";
            $FetchProfits = new explore('accounting');
            $profits = $FetchProfits->fetch($query);
            $amount = new stdClass();
            $amount->type    = 'string';
            $amount->month   = date("M", $i);
            $amount->amount  = $profits[$i]['amount'];
            }
       $j_son = json_encode($amount);
       $log->problem($j_son);
       echo $j_son;
       return false;
            
        }
}            

Open in new window



Once the page loads it takes that echod $j_son var and renders it through the arrayToDataTable() function google gives us. The Code is below this line.

function ProfitChart(year) {
    connect_ajax();
    xmlhttp.onreadystatechange = function() {
	if (xmlhttp.readyState===4 && xmlhttp.status===200)
            {
            console.log(JSON.parse(xmlhttp.responseText));
            var data = new google.visualization.arrayToDataTable(JSON.parse(xmlhttp.responseText));    
            var options = {
                    title:'Qtr Profit Loss',
                    vAxis: {title: 'Year', titleTextStyle: {color: 'gold'}}
                    };
            var chart = new google.visualization.ColumnChart(document.getElementById('profitlossbar'));
                chart.draw(data, options);
            }
    };
var file = "http://" + u + "/members/Sales";     
var values = "view=MonthlyProfits&year=" + year;
xmlhttp.open("POST", file, true);
xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xmlhttp.send(values);
}

Open in new window


The Final result should be a query of each month sumed up and displayed in a column chart. Thanks for the help experts!!

Chris
0
Comment
Question by:Easyrider43
10 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39838144
Don't know what's the problem with your code, but if all you want is to display graphs with php, the easiest library I know of is JpGraph: http://jpgraph.net/

HTH,
Dan
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39838149
Hey Chris, can you please show us the data?  We already know the code doesn't work, so looking at that is not really helpful.  If we can see the inputs you have and the outputs you need we can usually help bridge the gap.

Also, I second the recommendation of JpGraph.  Great library!
0
 
LVL 1

Author Comment

by:Easyrider43
ID: 39840663
I've tried jpGraphs and i keep getting a blank image also I don't like using the GD Library im not fimuliar with that yet but to answer your question..

$FetchProfits = new explore('accounting'); //querys the database
$profits = $FetchProfits->fetch($query); // Fetches the results and stores them
$amount = new stdClass();
$amount->type    = 'string'; // This is the type of variable I am out putting
$amount->month   = date("M", $i); // This Sets the Month that we are fetching
$amount->amount  = $profits[$i]['amount']; // This would display the amount profit for the month


The Result would be a Column Bar Chart Displaying Each Month and that would be displayed horizontally below. I think my main problem is setting up the array cause when I type the numbers in manually it works fine. maybe if someone knows a example of how to dynamically get info from the database and send it to JavaScript using j son and send it to Google charts in the format that they need
0
 
LVL 1

Author Comment

by:Easyrider43
ID: 39843316
So Here is a new route im trying to do but now i get a different error Message Saying "Table Has No Columns" Code is Posted Below.

function ProfitChart2() {
    google.load('visualization', '1', {packages:['corechart'], callback: loadData}); 
}
            
function drawChart(json) {
    var data = new google.visualization.DataTable(json);
    var options = {
        vAxis: {minValue: 0}
    };
    var chart = new google.visualization.AreaChart(document.getElementById('profitlossbar'));
    chart.draw(data, options);
}

function loadData() {
    connect_ajax();
    //var request=new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                drawChart(xmlhttp.responseText);
                //xmlhttp.responseText;
        }
    };
    var file = "http://" + u + "/members/Sales";     
    var values = "view=MonthlyProfits";
        xmlhttp.open("POST", file, true);
        xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        xmlhttp.send(values);
}

Open in new window


PHP is Posted Here

$view       = filter_input(INPUT_POST, 'view');
$log        = new diary();
if (!$view) {
    $log->problem("No View found");
    return false;
} else {
    if ($view == 'MonthlyProfits') 
        {
        //$year = filter_input(INPUT_POST, 'year');
        $month = array('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jly', 'Aug', 'Sept', 'Oct', 'Nov', 'Dec');
        $year = '2013';
        //$monthlyP = array();
        $monthlyp['cols'][0] = (object) array('id' => '1', 'label' => 'Month', 'type' => 'string');
        $monthlyP['cols'][1] = (object) array('id' => '2', 'label' => 'Homes Profit', 'type' => 'number');
        $monthlyP['cols'][2] = (object) array('id' => '3', 'label' => 'Electric Profit', 'type' => 'number');
        
        for ($i=0; $i < 12; $i++)
             {
             $query1 = "SELECT sum(transaction_amount) as amount FROM finanical_transactions WHERE (transaction_date BETWEEN '$year-$i-1' AND  '$year-$i-31' AND transaction_type_code = '12000' AND parentAssoc = '658265')";
             $query2 = "SELECT sum(transaction_amount) as amount FROM finanical_transactions WHERE (transaction_date BETWEEN '$year-$i-1' AND  '$year-$i-31' AND transaction_type_code = '12000' AND parentAssoc = '658266')";
             $FetchProfits = new explore('accounting');
             $profitsE = $FetchProfits->fetch($query1);
             $profitsH = $FetchProfits->fetch($query2);
             $monthlyP['rows'][$i] = (object) array("v" => $month[$i], "v" => $profitsE[0]['amount'], "v" => $profitsH[0]['amount']);
             //$log->problem($i . ' Is the var now');
             
             }  
        echo json_encode($monthlyP, JSON_NUMERIC_CHECK);
        return false;
       } 
       $log->problem("Did not excute properly");
       return false;
}

$log->problem("You didnt send a view statement");
return false;

Open in new window


Anybody PLease Help this is day 6 i've been working on this and im about to pull my hair out..

Chris S
0
 
LVL 1

Author Comment

by:Easyrider43
ID: 39843357
Oh and here is what the data returns

{"cols":{"1":{"id":2,"label":"Homes Profit","type":"number"},"2":{"id":3,"label":"Electric Profit","type":"number"}},"rows":[{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null},{"v":null}]} 

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:Easyrider43
ID: 39843365
Im sorry, This is what it returns

{"cols":{"1":{"id":2,"label":"Homes Profit","type":"number"},"2":{"id":3,"label":"Electric Profit","type":"number"}},"rows":[{"Month":"Jan","SecureE":null,"SecureH":null},{"Month":"Feb","SecureE":null,"SecureH":null},{"Month":"Mar","SecureE":null,"SecureH":null},{"Month":"Apr","SecureE":null,"SecureH":null},{"Month":"May","SecureE":null,"SecureH":null},{"Month":"Jun","SecureE":null,"SecureH":null},{"Month":"Jly","SecureE":31056.16,"SecureH":null},{"Month":"Aug","SecureE":8929.46,"SecureH":null},{"Month":"Sept","SecureE":11484.49,"SecureH":null},{"Month":"Oct","SecureE":10198.85,"SecureH":null},{"Month":"Nov","SecureE":3994.48,"SecureH":null},{"Month":"Dec","SecureE":null,"SecureH":null}]} 

Open in new window



the ones with the value of null is correct this is a test database so I didnt input anything numbers in that
0
 
LVL 1

Author Comment

by:Easyrider43
ID: 39843373
And I get the Error "Table Has No Columns"
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39843530
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 500 total points
ID: 39843599
Test page : http://jsfiddle.net/ymN9z/

function ProfitChart(year) {
	connect_ajax();
	xmlhttp.onreadystatechange = function() {
		if (xmlhttp.readyState===4 && xmlhttp.status===200)
		{
			var data = JSON.parse(xmlhttp.responseText);

			// FORMATTING YOUR DATA...
			// TO GET DATA LIKE THE FIRST EXAMPLE HERE :
			// https://developers.google.com/chart/interactive/docs/gallery/columnchart
			var d = [["Month", "Home Profit", "Electric Profit"]]; // WE CAN GET THIS FROM THE DATA TOO...
			for(var i=0;i<data.rows.length;i++) {
			    var row = [ data.rows[i].Month, data.rows[i].SecureE*1, data.rows[i].SecureH*1 ];
			    d.push(row);
			}
			// END OF FORMATTING
			var data = new google.visualization.arrayToDataTable(d);

			var options = {
				title:'Qtr Profit Loss',
			 	vAxis: {title: 'Year', titleTextStyle: {color: 'gold'}}
			};
			var chart = new google.visualization.ColumnChart(document.getElementById('profitlossbar'));
			chart.draw(data, options);

		}
	};
	var file = "http://" + u + "/members/Sales";
	var values = "view=MonthlyProfits&year=" + year;
	xmlhttp.open("POST", file, true);
	xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
	xmlhttp.send(values);
}

Open in new window

0
 
LVL 1

Author Closing Comment

by:Easyrider43
ID: 39843647
Wow, I owe you the biggest Thank you.. I've been fighting that for 6 days. Thank you very much for helping me.

Chris
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

OverviewThis article demonstrates a simple search form using AJAX. The purpose of the article is to demonstrate how to use the same code to render a page and javascript (JQuery) and AJAX to make subsequent calls to refine the results. The princip…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now