Oops, typo in there (the percentageChange1 selections in both sides of the
UNION should be pulled from b not a):
$queryData = mysql_query("
SELECT
Date,
PercentageChange,
PercentageChange1
FROM (
SELECT
a.Date,
a.percentageChange,
b.percentageChange AS percentageChange1
FROM Data AS a
LEFT JOIN Data1 AS b ON (a.Date = b.Date)
UNION
SELECT
b.Date,
a.percentageChange,
b.percentageChange AS percentageChange1
FROM Data AS a
RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
) AS x
ORDER BY Date DESC
LIMIT 0, 14
");
On Thursday, April 18, 2013 4:07:35 PM UTC-4, new_prog wrote:
>
> Thanks for the response - this is an improvement as it's executing.. It's
> returning both columns (PercentageChange and PercentageChange1) with the
> same data (data from Table: Data).. Do you know where the error may be?
>
> Thanks again for the help!
>
> On Thursday, 18 April 2013 19:52:20 UTC+1, asgallant wrote:
>>
>> Try this:
>>
>> $queryData = mysql_query("
>> SELECT
>> Date,
>> PercentageChange,
>> PercentageChange1
>> FROM (
>> SELECT
>> a.Date,
>> a.percentageChange,
>> a.percentageChange AS percentageChange1
>> FROM Data AS a
>> LEFT JOIN Data1 AS b ON (a.Date = b.Date)
>> UNION
>> SELECT
>> b.Date,
>> a.percentageChange,
>> a.percentageChange AS percentageChange1
>> FROM Data AS a
>> RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
>> ) AS x
>> ORDER BY Date DESC
>> LIMIT 0, 14
>> ");
>>
>> On Thursday, April 18, 2013 1:47:50 PM UTC-4, new_prog wrote:
>>>
>>> Thank you very much for the reply and for the code.. I am having a
>>> problem with the SQL though:
>>> "#1248 - Every derived table must have its own alias"
>>>
>>> I have tried fiddling around with it but can't get it to work.
>>>
>>> Do you have any suggestions?
>>>
>>> On Wednesday, 17 April 2013 22:19:41 UTC+1, asgallant wrote:
>>>>
>>>> Ok, the first problem is that you are creating a DataTable with 3
>>>> columns here:
>>>>
>>>> $table['cols'] = array(
>>>> array('label' => 'Date', 'type' => 'string'),
>>>> array('label' => 'Percentage Change', 'type' => 'number'),
>>>> array('label' => 'Percentage Change 1', 'type' => 'number')
>>>> );
>>>>
>>>>
>>>> But only populating 2 columns in the while loops:
>>>>
>>>> while($r = mysql_fetch_assoc($queryData)) {
>>>> $temp = array();
>>>> // the following line will used to slice the Pie chart
>>>> $temp[] = array('v' => (string) $r['Date']);
>>>>
>>>>
>>>> //Values of the each slice
>>>> $temp[] = array('v' => (float) $r['PercentageChange']);
>>>> $rows[] = array('c' => $temp);
>>>> }
>>>>
>>>>
>>>> This will cause the line:
>>>>
>>>> var data = new google.visualization.DataTable(<?=$jsonTable?>);
>>>>
>>>>
>>>> to throw an error.
>>>>
>>>> There are two ways you can address the situation:
>>>>
>>>> 1) perform a table join in your SQL to make 1 query and build 1
>>>> DataTable in PHP
>>>> 2) build 2 2-column DataTables in PHP and join them in javascript
>>>>
>>>> Option 1 should be faster to execute than option 2 (and results in
>>>> simpler code), as databases are optimized for things like table joins,
>>>> whereas javascript is not. Try this instead:
>>>>
>>>> <?php
>>>> $connection = mysql_connect(blah);
>>>> $database = mysql_select_db(blah);
>>>>
>>>> // The Chart table contain two fields: Date and PercentageChange
>>>> $queryData = mysql_query("
>>>> SELECT
>>>> Date,
>>>> PercentageChange,
>>>> PercentageChange1
>>>> FROM (
>>>> SELECT
>>>> a.Date,
>>>> a.percentageChange,
>>>> a.percentageChange AS percentageChange1
>>>> FROM Data AS a
>>>> LEFT JOIN Data1 AS b ON (a.Date = b.Date)
>>>> UNION
>>>> SELECT
>>>> b.Date,
>>>> a.percentageChange,
>>>> a.percentageChange AS percentageChange1
>>>> FROM Data AS a
>>>> RIGHT JOIN Data1 AS b ON (a.Date = b.Date)
>>>> )
>>>> ORDER BY Date DESC
>>>> LIMIT 0, 14
>>>> ");
>>>>
>>>> $table = array();
>>>> $table['cols'] = array(
>>>> array('label' => 'Date', 'type' => 'string'),
>>>> array('label' => 'Percentage Change', 'type' => 'number'),
>>>> array('label' => 'Percentage Change 1', 'type' => 'number')
>>>> );
>>>>
>>>> //First Series
>>>> $rows = array();
>>>> while($r = mysql_fetch_assoc($queryData)) {
>>>> $temp = array();
>>>> // the following line will used to slice the Pie chart
>>>> $temp[] = array('v' => (string) $r['Date']);
>>>>
>>>> //Values of the each slice
>>>> $temp[] = array('v' => (float) $r['PercentageChange']);
>>>> $temp[] = array('v' => (float) $r['PercentageChange1']);
>>>> $rows[] = array('c' => $temp);
>>>> }
>>>>
>>>> $table['rows'] = $rows;
>>>> $jsonTable = json_encode($table);
>>>> ?>
>>>> <!DOCTYPE html>
>>>> <html>
>>>> <head>
>>>> <!--Load the AJAX API-->
>>>> <script type="text/javascript" src="https://www.google.com/jsapi
>>>> "></script>
>>>> <script type="text/javascript" src="
>>>> http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js
>>>> "></script>
>>>> <script type="text/javascript">
>>>> // Load the Visualization API and the chart package.
>>>> google.load('visualization', '1', {'packages':['corechart']});
>>>>
>>>> // Set a callback to run when the Google Visualization API is loaded.
>>>> google.setOnLoadCallback(drawChart);
>>>>
>>>> function drawChart() {
>>>> // Create our data table out of JSON data loaded from server.
>>>> var data = new google.visualization.DataTable(<?=$jsonTable?>);
>>>> var options = {
>>>> title: 'Performance',
>>>> width: 800,
>>>> height: 600
>>>> };
>>>> // Instantiate and draw our chart, passing in some options.
>>>> var chart = new
>>>> google.visualization.LineChart(document.getElementById('chart_div'));
>>>> chart.draw(data, options);
>>>> }
>>>> </script>
>>>> </head>
>>>>
>>>> <body>
>>>> <!--Div that will hold the pie chart-->
>>>> <div id="chart_div"></div>
>>>> </body>
>>>> </html>
>>>>
>>>> On Wednesday, April 17, 2013 4:26:35 PM UTC-4, new_prog wrote:
>>>>>
>>>>> Hi all,
>>>>>
>>>>> Am stuck on trying to display two series on a line chart through
>>>>> google charts api.
>>>>>
>>>>> I am getting my data from a database using sql and this is all working
>>>>> fine.
>>>>>
>>>>> I have adapted the code from: PHP MYSQL Google Chart JSON Complete
>>>>> Example<http://stackoverflow.com/questions/12994282/php-mysql-google-chart-json-complete-example>
>>>>>
>>>>> <?php
>>>>> $connection = mysql_connect(blah);
>>>>> $database = mysql_select_db(blah);
>>>>> // The Chart table contain two fields: Date and PercentageChange
>>>>> $queryData = mysql_query("SELECT Date, PercentageChange
>>>>> FROM Data
>>>>> ORDER BY Date DESC
>>>>> LIMIT 0, 14");
>>>>>
>>>>> $queryData1 = mysql_query("SELECT Date, PercentageChange
>>>>> FROM Data1
>>>>> ORDER BY Date DESC
>>>>> LIMIT 0, 14");
>>>>>
>>>>>
>>>>> $table = array();
>>>>> $table['cols'] = array(
>>>>>
>>>>> array('label' => 'Date', 'type' => 'string'),
>>>>> array('label' => 'Percentage Change', 'type' => 'number'),
>>>>> array('label' => 'Percentage Change 1', 'type' => 'number')
>>>>> );
>>>>> //First Series
>>>>> $rows = array();
>>>>> while($r = mysql_fetch_assoc($queryData)) {
>>>>> $temp = array();
>>>>> // the following line will used to slice the Pie chart
>>>>> $temp[] = array('v' => (string) $r['Date']);
>>>>>
>>>>> //Values of the each slice
>>>>> $temp[] = array('v' => (float) $r['PercentageChange']);
>>>>> $rows[] = array('c' => $temp);
>>>>> }
>>>>>
>>>>> $table['rows'] = $rows;
>>>>> $jsonTable = json_encode($table);
>>>>> //echo $jsonTable;
>>>>> //For Data1
>>>>> $rows = array();
>>>>> while($r = mysql_fetch_assoc($queryData1)) {
>>>>> $temp = array();
>>>>> // the following line will used to slice the Pie chart
>>>>> $temp[] = array('v' => (string) $r['Date']);
>>>>>
>>>>> //Values of the each slice
>>>>> $temp[] = array('v' => (float) $r['PercentageChange']);
>>>>> $rows[] = array('c' => $temp);
>>>>> }
>>>>>
>>>>> $table['rows'] = $rows;
>>>>> $jsonTable1 = json_encode($table);
>>>>> echo $jsonTable1;?>
>>>>>
>>>>> <html>
>>>>> <head>
>>>>> <!--Load the AJAX API-->
>>>>> <script type="text/javascript"
>>>>> src="https://www.google.com/jsapi";></script>
>>>>> <script type="text/javascript"
>>>>> src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js";></script>
>>>>> <script type="text/javascript">
>>>>>
>>>>> // Load the Visualization API and the chart package.
>>>>> google.load('visualization', '1', {'packages':['corechart']});
>>>>>
>>>>> // Set a callback to run when the Google Visualization API is loaded.
>>>>> google.setOnLoadCallback(drawChart);
>>>>>
>>>>> function drawChart() {
>>>>>
>>>>> // Create our data table out of JSON data loaded from server.
>>>>> var data = new google.visualization.DataTable(<?=$jsonTable?>);
>>>>> var data1 = new google.visualization.DataTable(<?=$jsonTable1?>);
>>>>> var options = {
>>>>> title: 'Performance',
>>>>> width: 800,
>>>>> height: 600
>>>>> };
>>>>> // Instantiate and draw our chart, passing in some options.
>>>>> var chart = new
>>>>> google.visualization.LineChart(document.getElementById('chart_div'));
>>>>> chart.draw(data, options);
>>>>> }
>>>>> </script>
>>>>> </head>
>>>>>
>>>>> <body>
>>>>> <!--Div that will hold the pie chart-->
>>>>> <div id="chart_div"></div>
>>>>> </body></html>
>>>>>
>>>>> Now, i know it is picking up the correct data from each query but I
>>>>> can't figure out how to put both on the same chart.
>>>>>
>>>>> I assume it is something to do with this line:
>>>>>
>>>>> chart.draw(data, options);
>>>>>
>>>>> And have tried:
>>>>>
>>>>> chart.draw(data, data1, options);
>>>>>
>>>>> But no luck.
>>>>>
>>>>> Could anyone point me in the correct direction?
>>>>>
>>>>> Thanks!
>>>>>
>>>>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at
http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
