Select for Stacked bars Graph

[Raissam]

Hi guys,

 

Im trying to use the library for graphics Rgraph to show some data from mysql

 

My exit from mysql is

 

year  | amount |   type

2000 |      3      |  open

2001 |      1      |  open

2010 |      1      |  close

2010 |      2      |  open

2011 |      1      |  open

 

Rgraph need the data like this to show right  [[3],[1],[1],[1,2],[1]].

 

At the end the graph will stay that way:

 

             |             |    ~1~    |

   _3_    |   _1_    |    _2_    |   _1_

------------------------------------------ 

   2000  |  2001   |   2010   |   2011            

 

Can u please help to format this on php..

 

tks

[Psycho]

Try

 

$query = "SELECT year, amount FROM table_name ORDER BY year, amount";
$result = mysql_query($query);

$data = array();
while($row = mysql_fetch_assoc($result))
{
    $data[$row['year']][] = $row['amount'];
}

//Post process the data
foreach($data as &$year)
{
    $year = '[' . implode(',', $year) . ']';
}
$finalData = '[' . implode(',', $data) . ']';

[Barand]

I'd order by year, type to get close value before the open value

[Raissam]

Thanks Psycho, works perfectly!

 

Edited March 11, 2014 by Raissam

[Raissam]

Good afternoon,

I put the solution presented there and ran up to the test, but there was another problem, that is when there is no data, so I talked with the developer Rgraph and he suggested that I enter a 0 which is blank.

At the end should have the following string [[0,1,0,1],[0,1,0,3],[0,1,0,4],[0,1,1,5],[1,2,0,1],[1,0,1,0]].

I tried changing the suggested but did not succeed code follows below:

My exit from mysql is

year  | amount |  type
2000 |      1      |  orange
2000 |      1      |  banana
2001 |      1      |  orange
2001 |      3      |  banana
2003 |      1      |  orange
2003 |      4      |  banana
2005 |      1      |  orange
2005 |      1      |  pear
2005 |      5      |  banana
2010 |      1      |  apple
2010 |      2      |  orange
2010 |      1      |  banana
2011 |      1      |  apple
2011 |      1      |  pear

[apple,orange,pear,banana]

Rgraph need the data like this to show right [[0,1,0,1],[0,1,0,3],[0,1,0,4],[0,1,1,5],[1,2,0,1],[1,0,1,0]].
 

These were changes I made to work but it did not work

  

 while($row = mysql_fetch_assoc($result))
    {
           $data[$row['year2']][] = $row['amount'];
           $data[][$row['amount']] = $row['amount'];
    }
 foreach($data as &$year)
    {
        $year2 = $row['year2'];
        foreach($year as $key=>$year_2)
        {
        $amount = $row['amount'];
            if(!empty($year2))
            {        
                   $year = implode(',', $year);
               }
               else{
                      $year = '[' . 0 . ']' ;
            }
        }
    }    
    $finalData = '[' . implode(',', $data) . ']';

I know it's annoying to keep asking but I am no expert on the subject and I'm trying to do something that my boss wants and I have no skill for it.

 

TKSS!!
 

[Barand]

try something like this

$datakeys = array('apple','orange','pear','banana');

$sql = "SELECT year, type, amount
    FROM mytable
    ORDER BY year";
$res = $mysqli->query($sql);
$curryear = 0;
$data = array();
while (list($year, $type, $amount) = $res->fetch_row()) {
    if ($year != $curryear) {
        $data[$year] = array_fill_keys($datakeys, 0);
        $curryear = $year;
    }
    $data[$year][$type] = $amount;
}