I duplicated your table and ran the query, and Method 2 looked right to me.
I had to make 1 small change to the SQL to make it run (but that could be
a quirk of my MySQL install), and 1 change to the chart options. The SQL
looked like this:
SELECT
foo.PsiBar,
IF(prodPerct1 = 0, null, foo.prodPerct1) as prodPerct1,
IF(prodPerct2 = 0, null, foo.prodPerct2) as prodPerct2,
IF(prodPerct3 = 0, null, foo.prodPerct3) as prodPerct3
FROM (
SELECT
PsiBar,
SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,
SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,
SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3
FROM tbl_dilution
GROUP BY PsiBar
) AS foo
and I set the "interpolateNulls" chart option to true:
var options = {
title: 'Line Chart Test',
interpolateNulls: true
};
On Tuesday, October 16, 2012 12:51:40 PM UTC-4, Jose wrote:
>
> I gave both a try and neither looked as it should. Method 2 displays the
> chart as it did previous where it was plotting the null '0' values.
>
> On Monday, October 15, 2012 9:38:58 PM UTC-7, asgallant wrote:
>>
>> Hmmm...I can see two ways of handling that. One results in a messy
>> DataTable, and will probably work; the other results in a cleaner
>> DataTable, but might not work.
>>
>> Method 1: change the SQL statement to this:
>> SELECT
>> PsiBar,
>> IF(id_sample = 1, prodPerct, null) AS prodPerct1,
>> IF(id_sample = 2, prodPerct, null) AS prodPerct2,
>> IF(id_sample = 3, prodPerct, null) AS prodPerct3
>> FROM tbl_dilution
>>
>> removing the sums and the group by clause. This will result in more rows
>> of data (with duplicate domain column entries) than necessary.
>>
>> Method 2: this will work only if 0 is not a valid value for your data
>> points to have. Change the SQL to this:
>>
>>
>> SELECT
>> PsiBar,
>> IF(prodPerct1 = 0, null, prodPerct1) as prodPerct1,
>> IF(prodPerct2 = 0, null, prodPerct2) as prodPerct2,
>> IF(prodPerct3 = 0, null, prodPerct3) as prodPerct3
>> FROM (
>> SELECT
>> PsiBar,
>> SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,
>> SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,
>> SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3
>> FROM tbl_dilution
>> GROUP BY PsiBar
>> ) AS foo
>>
>> which tests to see if the sum is 0, and if it is, sets the value to null
>> instead. The DataTable will be cleaner, but it won't work if your values
>> can be 0.
>>
>> On Monday, October 15, 2012 6:45:51 PM UTC-4, Jose wrote:
>>>
>>> asgallant, you are right, I am trying to get three lines plotted for
>>> each 'id_sample'. Within each id group, there are 12 plot points.
>>> I've tried the code you provided, thanks, but it appears to also plot
>>> the null values '0' between each data point. How do I fix this?
>>>
>>> {"cols":[{"label":"PsiBar","type":"number"},{"label":"Series
>>> 1","type":"number"},{"label":"Series 2","type":"number"},{"label":"Series
>>> 3","type":"number"}],"rows":[{"c":[{"v":0.39},{"v":0.36},{"v":0},{"v":0}]},{"c":[{"v":0.5},{"v":0},{"v":0.26},{"v":0.11}]},{"c":[{"v":0.56},{"v":0.49},{"v":0.34},{"v":0}]},{"c":[{"v":0.57},{"v":0},{"v":0},{"v":0.16}]},{"c":[{"v":0.84},{"v":0.56},{"v":0.41},{"v":0.15}]},{"c":[{"v":1.01},{"v":0.62},{"v":0.42},{"v":0}]},{"c":[{"v":1.02},{"v":0},{"v":0},{"v":0.24}]},{"c":[{"v":1.3},{"v":0.66},{"v":0.49},{"v":0}]},{"c":[{"v":1.31},{"v":0},{"v":0},{"v":0.26}]},{"c":[{"v":1.45},{"v":0.66},{"v":0.5},{"v":0.27}]},{"c":[{"v":1.74},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":1.75},{"v":0.68},{"v":0},{"v":0.28}]},{"c":[{"v":2.1},{"v":0},{"v":0},{"v":0.28}]},{"c":[{"v":2.11},{"v":0},{"v":0.52},{"v":0}]},{"c":[{"v":2.12},{"v":0.68},{"v":0},{"v":0}]},{"c":[{"v":2.57},{"v":0},{"v":0.49},{"v":0.27}]},{"c":[{"v":2.58},{"v":0.65},{"v":0},{"v":0}]},{"c":[{"v":3.07},{"v":0},{"v":0},{"v":0.25}]},{"c":[{"v":3.09},{"v":0.6},{"v":0.46},{"v":0}]},{"c":[{"v":3.56},{"v":0.56},{"v":0},{"v":0.23}]},{"c":[{"v":3.57},{"v":0},{"v":0.42},{"v":0}]},{"c":[{"v":4.23},{"v":0},{"v":0},{"v":0.21}]},{"c":[{"v":4.34},{"v":0},{"v":0.39},{"v":0}]},{"c":[{"v":4.36},{"v":0.51},{"v":0},{"v":0}]}]}
>>>
>>> Really appreciate your help on this!
>>>
>>> On Thursday, October 11, 2012 12:43:03 PM UTC-7, asgallant wrote:
>>>>
>>>> You're not charting 3 series there, you have 1 series. Looking at your
>>>> SQL table, I would guess that you want to display one series for each
>>>> sample id, right? If so, then you need to break out the "prodPerct"
>>>> column
>>>> into 3 different columns - 1 for each series. This is probably best
>>>> achieved in SQL, maybe with a query like this:
>>>>
>>>> SELECT
>>>> PsiBar,
>>>> SUM(IF(id_sample = 1, prodPerct, null)) AS prodPerct1,
>>>> SUM(IF(id_sample = 2, prodPerct, null)) AS prodPerct2,
>>>> SUM(IF(id_sample = 3, prodPerct, null)) AS prodPerct3
>>>> FROM tbl_dilution
>>>> GROUP BY PsiBar
>>>>
>>>> and then use this to build the table:
>>>>
>>>> $table['cols'] = array(
>>>> array('label' => 'PsiBar', 'type' => 'number'),
>>>> array('label' => 'Series 1', 'type' => 'number')
>>>> array('label' => 'Series 2', 'type' => 'number')
>>>> array('label' => 'Series 3', 'type' => 'number')
>>>> );
>>>>
>>>> $rows = array();
>>>> while($r = mysql_fetch_assoc($sth)) {
>>>> $temp = array();
>>>> $temp[] = array('v' => (float) $r['psiBar']);
>>>> $temp[] = array('v' => (float) $r['prodPerct1']);
>>>> $temp[] = array('v' => (float) $r['prodPerct2']);
>>>> $temp[] = array('v' => (float) $r['prodPerct3']);
>>>> $rows[] = array('c' => $temp);
>>>> }
>>>>
>>>> On Thursday, October 11, 2012 12:50:04 PM UTC-4, Jose wrote:
>>>>>
>>>>> Hi asgallant,
>>>>>
>>>>> Seeing Diana's example, I tried doing something similar with a Line
>>>>> graph but it's not coming out as I'd like.
>>>>> It displays the three series but links them all together instead of
>>>>> individually displaying them (lineChart.jpg).
>>>>> What I'm trying to achieve, is something similar to how it's displayed
>>>>> in Excel (chart_xls.jpg).
>>>>> If you could help me in the right direction, I'd appreciate it alot as
>>>>> I have been trying various things and the outcome
>>>>> isn't what I'm expecting.
>>>>>
>>>>> José
>>>>>
>>>>> On Wednesday, September 26, 2012 10:11:01 AM UTC-7, asgallant wrote:
>>>>>>
>>>>>> What is throwing that error message? Is it PHP?
>>>>>>
>>>>>> You will have to adjust the data types to the type of data you are
>>>>>> using, so if your first column isn't type string, you need to change it
>>>>>> to
>>>>>> something else in the column definitions (this goes for all columns -
>>>>>> types
>>>>>> must always match). Also, the (string) typecasting in this line:
>>>>>>
>>>>>> $temp[] = array('v' => (string) $r['PLACA']);
>>>>>>
>>>>>> is probably not necessary, unless you have a non-string data type
>>>>>> that you need to specifically convert into a string.
>>>>>>
>>>>>> If you can post a link to the page, I can help debug things on the
>>>>>> javascript end, if it turns out that is where the problem is.
>>>>>>
>>>>>> On Wednesday, September 26, 2012 4:47:20 AM UTC-4, Barbara Gerstl
>>>>>> wrote:
>>>>>>>
>>>>>>> That is what I did... but, when opening goochart2.html, the result
>>>>>>> is the Error-Massage "string".
>>>>>>> I think, it has something to do with the field settings of the
>>>>>>> columns. Do you have any tipps?
>>>>>>>
>>>>>>> Thank you!
>>>>>>>
>>>>>>>
>>>>>>> Am Montag, 24. September 2012 19:26:26 UTC+2 schrieb asgallant:
>>>>>>>>
>>>>>>>> You can extrapolate from the code that the table has 6 columns:
>>>>>>>> PLACA, S1, S2, S3, S4, S5.
>>>>>>>>
>>>>>>>> On Monday, September 24, 2012 10:15:44 AM UTC-4, Barbara Gerstl
>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> Hello Diana!
>>>>>>>>>
>>>>>>>>> Thank you very much for showing the whole process on how to
>>>>>>>>> combine Google Graph API with a MySQL-Database. That is exactly what
>>>>>>>>> I am
>>>>>>>>> looking for.
>>>>>>>>>
>>>>>>>>> I tried to rebuild your example and I am having problems with the
>>>>>>>>> structure of the database/field settings. Can you show me structure
>>>>>>>>> and
>>>>>>>>> field settings of the table "bd_salidas"?
>>>>>>>>>
>>>>>>>>> Thank you for your answer.
>>>>>>>>> Barbara
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Am Mittwoch, 5. September 2012 21:56:35 UTC+2 schrieb Diana Flores:
>>>>>>>>>>
>>>>>>>>>> yeaaaaaaahhhHHHH!!!!, we did it!!!!!!!!!!!!!. well at first i
>>>>>>>>>> tried the .DataTable(jsonData); but it gave me errors but i put the
>>>>>>>>>> JSON.parse(jsonData)); and it works!!!!!!!!!!!!!!!!!!....im so
>>>>>>>>>> happy!!! i
>>>>>>>>>> will attach the files in case someone has the same
>>>>>>>>>> problem!!!!!!!!....really really grateful, cause with your help i
>>>>>>>>>> learned a
>>>>>>>>>> lot of things!!!!....one month ago I was "what its php or
>>>>>>>>>> mysql....JSON
>>>>>>>>>> O_O???" i think its a lot, but thanks!!!!
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/google-visualization-api/-/gHOj7YQabBcJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-visualization-api?hl=en.
