4 Replies Latest reply on Aug 21, 2007 3:24 PM by fragerdaz

    Charting with event.result

    fragerdaz Level 1
      I have a database table with 2 fields (name, date).
      Using a remote object (amfphp), I retrieve all rows from the table.

      Using a line chart, I want to represent the data with dates on the xAxis and the number of rows for each date on the yAxis.

      I can get the xAxis working perfectly where it lists, without repeating, all the dates in the table.
      However, I can't find a way to get the yAxis to represent the length (count) of rows for each date.
      (Using the yField="name" attribute is, of course, useless in this situation)

      Any ideas much appreciated!
        • 1. Charting with event.result
          fragerdaz Level 1
          I've started fiddling with the idea of a loop, here's what I've quickly drawn up:

          quote:

          private var aLengths:Array = new Array;

          private function getLengths():void {
          for (var j:int = 0; j < event.result.length; j++) {
          while (event.result[j + 1].date == event.result[j]) {
          var k:int = 1;
          aLengths[event.result.date] = k++;
          }
          }
          }

          As you can surely tell, I'm not very familiar with loops...

          Am I on the right track or is this overly complicated considering Flex Charting functions ?
          • 2. Re: Charting with event.result
            flexPro Level 1
            A better way to do this would be to change the SQL on the server to figure this out ahead of time. Assuming your table is named posts and you're using mysql, to get this data for a given name, try something like:

            SELECT date, count(name) FROM posts WHERE name = ? GROUP BY date;

            This will return a set of rows containing each distinct date where the name that replaces the question mark (don't forget the quotes around it either) shows up and the number of times that name appeared coupled with that date.

            Here's a simple example (with integers representing dates) I mocked up in MySql to make sure I wasn't leading you astray:

            mysql> select * from posts;
            +------+------+
            | name | date |
            +------+------+
            | Ted | 1 |
            | Ted | 1 |
            | Ted | 1 |
            | Ted | 2 |
            | Ted | 2 |
            | Ted | 4 |
            | Mike | 4 |
            | Mike | 4 |
            | Mike | 4 |
            | Mike | 3 |
            | Mike | 3 |
            | Mike | 3 |
            | Mike | 2 |
            | Mike | 1 |
            | Mike | 1 |
            +------+------+
            15 rows in set (0.00 sec)

            mysql> SELECT count(name), date FROM posts WHERE name = 'Mike' GROUP BY date;
            +-------------+------+
            | count(name) | date |
            +-------------+------+
            | 2 | 1 |
            | 1 | 2 |
            | 3 | 3 |
            | 3 | 4 |
            +-------------+------+
            4 rows in set (0.00 sec)

            mysql> SELECT count(name), date FROM posts WHERE name = 'Ted' GROUP BY date;
            +-------------+------+
            | count(name) | date |
            +-------------+------+
            | 3 | 1 |
            | 2 | 2 |
            | 1 | 4 |
            +-------------+------+

            If you'd like to get this info from all users at once, try:

            mysql> SELECT name, count(name), date FROM posts GROUP BY name, date;
            +------+-------------+------+
            | name | count(name) | date |
            +------+-------------+------+
            | Mike | 2 | 1 |
            | Mike | 1 | 2 |
            | Mike | 3 | 3 |
            | Mike | 3 | 4 |
            | Ted | 3 | 1 |
            | Ted | 2 | 2 |
            | Ted | 1 | 4 |
            +------+-------------+------+

            You can even order the results by date:

            SELECT name, count(name), date FROM posts GROUP BY name, date ORDER BY date;

            Or by name, then date:

            SELECT name, count(name), date FROM posts GROUP BY name, date ORDER BY name, date;

            This will make the db query faster, the results you send from the server smaller, and you can hand the data returned directly over to your chart as a dataprovider.
            • 3. Re: Charting with event.result
              fragerdaz Level 1
              mysql> SELECT count(name), date FROM posts GROUP BY date ORDER BY date DESC;

              That's the exact formula I needed. Thanks for your help! :-)
              • 4. Re: Charting with event.result
                fragerdaz Level 1
                Quick extra question...

                Can I also use the MySQL query to add up the "count(name)" as they go through the dates ?

                ie:
                date | count(name) | what I want
                0 | 2 | 2
                1 | 3 | 5
                2 | 1 | 6
                3 | 7 | 13
                ...

                Or is that only possible through a for loop with the resultset?