I am attempting to create dynamic charts with numerical data from the mysql database but have not been able to figure out how to do this. Here is my attempt using a php snippet (I started with the snippet titled "List node count for each node type" posted here: http://drupal.org/node/233416

I modified the code to this snippet

<?php
function custom_count_pull(){
    $sql = "SELECT type, COUNT(nid) AS number, FROM_UNIXTIME(created, '%Y')  AS createdate FROM {node} GROUP BY type, createdate ORDER BY createdate DESC";
    $result = db_query($sql);
    $article_count = array();

    while ($data = db_fetch_object($result)){
        if (isset($article_count[$data->type])) {
       
            $article_count[$data->type][$data->createdate] = $data->number;
        } else {
            $article_count[$data->type] = array($data->createdate => $data->number);
        }
    }
    $today = date("Y");
    $years = array($today-2,$today-1,$today);

    $output .= "xdata,{$years[0]},{$years[1]},{$years[2]}\r\n";   
    foreach ($article_count as $type => $value) {
        $real_type_name = node_get_types('name', $type);
        $output .= "$real_type_name,";

        foreach ($years as $year) {
            if ($value[$year]):
            $output .= "{$value[$year]}";
            else:
            $output .= "0,";
            endif;
        }
        $output .= "\r\n";
    }
    $output .= "";
    return $output;
}

?>

The csvchart code block looks like this:

[csvdata]
title, Node count by data type
type, bvg
labels,Year,# of nodes

** What do I put here to insert the data???

[/csvdata]

This will be a very useful feature for my site and I'm guessing others will find a solution to this problem useful too.

Bob

p.s. I've already created a custom filter to handle the csvchart module and have successfully created charts with STATIC data as described in the csvchart module documentation. My goal is to find a way to create charts (page nodes) that are dynamically updated as the data in the database changes over time.

Comments

solimeno’s picture

Anyone ... ?

mooffie’s picture

First you need to generate text in the language csvchart understands. Then you need to feed this text to that module, to generate the graphics. These are two steps.

You're writing "what do I put here to insert the data" and this is a confusing sentence because it has different possible meanings: it may mean "I successfully generated here the text cvschart expects and now I need to turn it into graphics", or "I don't know to generate the text cvschart expects", or it may mean something else.

solimeno’s picture

mooffie,

I didn't realize the confusion I built into my question - thanks for pointing that out ...

Your second suggestion is an accurate description of the help I need: "I don't know to generate the text csvchart expects"

I can easily generate any desired chart by entering comma separated value data, but I would like to have some code snippet that can extract data from the mysql database and generate the text that the csvchart module expects.

p.s. I've also posted an issue here: http://drupal.org/node/264627

I am still in need of assistance ... can anyone offer help on this issue?

solimeno’s picture

Whether or not the CSV Chart module is used, how can I generate dynamic charts from data in my drupal site?

Thanks ...

Pierre.Vriens’s picture

Hi Bob,

I realize it's over 6 years later, but as the new maintainer (since today ...) of the CSV Chart module, I want to add this answer to your request here:

  • Things have changed a very little bit in Drupal-land over the past 6 years or so. If today I'd have to answer your original question, I'd definitely recommend you to look at the Forena Reports module (of which I've become a co-maintainer recently also). Your question was about a database in MySQL format, but in case of Forena it might as well be Oracle, MS Sql, Postgress, SQLite, etc ... It even supports using XML files to read from, which is how I first discovered Forena. I'm about sure that your original question about "List node count for each node type" is like a minor variation of one of the sample reports delivered with the module.
  • Even though I'm still in the process of discovering the available features in the D5 version of the csvchart module, I'm about sure that the current version will not help you regarding your goal, ie "My goal is to find a way to create charts (page nodes) that are dynamically updated as the data in the database changes over time." However the forena module does this already for quite some time (years). Part of my plan with the csvchart module, is to investigate how csvchart can be enhanced to process various flavors of csv file formats, and then use the amazing charting facilities available in forena to transform those data in a chart. Forena would become a project dependency, because I don't want to reinvent the wheel ... That would also solve my forena issue detailed in #2359251: Data blocks in CSV format?, because today forena does cannot be used to handle CSV input files.

PS: Funny enough I think the original question is still pretty accurate today ... which is why I created the Comparison of Charting modules recently. To anybody who really wants to get confused about all module variations to consider: go checkout that comparison and enhance/extend it as you see fit.