CODEWITHSUNDEEP
google-visualizationrelationship
Skot
Skot

Reputation: 112

Google Chart API table format for charts

Hopefully this makes sense. I will probably just keep mulling this over, until i figure it. I have a table, that is formatted in such as way, that a specific date may have more than one record assigned. Each record is a plant, so the structure of that table looks like the pinkish table in the image below. However when using the Google chart API the data needs to be in the format in the blue table for a line chart. Which I have working.

I am looking to create a graph in the Google chart api similar to the excel graph, using the pink table. Where at one date e.g. 01/02/2003 there are three species recorded A,B,C with values 1,2,3. I thought possibly using a scatter but that didn't work either.

What ties these together is the CenterID all these records belong to XXX CenterID. Each record with its species has an SheetID that grouped them together for example SheetID = 23, all those species were recorded on the same date.

Looking for suggestions, whether google chart API or php amendments. My PHP is below (I will switch to json_encode eventually).

$sql  = "SELECT * FROM userrecords";
$stmt = $conn->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();

foreach ($data as $row)
 {
  $dateArray = explode('-', $row['eventdate']);
    $year = $dateArray[0];
    $month= $dateArray[1] - 1;
    $day= $dateArray[2];

    $dataArray[] = "[new Date ($year, $month, $day), {$row['scientificname']}, {$row['category_of_taxom']}]";

Graph example

Upvotes: 1

Views: 272

Answers (1)

asgallant
asgallant

Reputation: 26340

To get that chart, where the dates are the series instead of the axis values, you need to change the way you are pulling your data. Assuming your database is structured like the pink table, you need to pivot the data on the date column instead of the species column to create a structure like this:

| Species | 01/02/2003 | 01/03/2003 | 01/04/2003 |
|---------|------------|------------|------------|
| A       | 1          | 2          | 3          |
| B       | 3          | 1          | 4          |
| C       | 1          | 3          | 5          |

Upvotes: 1

Related Questions