How can I add a trendline in pchart?

Question

I am creating charts for a admin dashboard in our in-house CRM, however I am having a bit of trouble adding a trend line. I can't seem to find any documentation for a built in function so have attempted to do it using an sql query which is clunky and slow.

    SELECT days.date, 
        (SELECT count(app.app_id) FROM li_appointments.li_appointments as app
            where app_datetime >= date_add(days.date, interval -7 day)
            and app_datetime <= days.date) / 5 as average
    FROM li_appointments.li_days as days
    where date >= date_add(date(now()), interval -30 day)
    and date <= date(now())

It takes about 12 seconds just to grab the data for this one line on the chart. Is there any native way to do this with pChart, or is there a better PHP option available?

Here is the full code I am using right now with no trend line:

   $getapps = "SELECT days.date, app.app_id as scheduled
   ,cnc.app_id as cnc
   ,dnc.app_id as dnc
   ,cancel.app_id as cancel
   ,covered.app_id as covered
   FROM li_appointments.li_days as days
   left Join (SELECT date(app_datetime) as app_datetime
               , count(app_id) as app_id 
               FROM li_appointments.li_appointments
               group by date(app_datetime)) 
       as app on days.date = app.app_datetime
   left Join (SELECT date(app_datetime) as app_datetime
               , count(app_id) as app_id 
               FROM li_appointments.li_appointments
               Where app_id in (select app_id from li_app_cnc)
               group by date(app_datetime)) 
       as cnc on days.date = cnc.app_datetime
   left Join (SELECT date(app_datetime) as app_datetime
               , count(app_id) as app_id 
               FROM li_appointments.li_appointments
               Where app_id in (select app_id from li_app_dnc)
               group by date(app_datetime)) 
       as dnc on days.date = dnc.app_datetime
   left Join (SELECT date(app_datetime) as app_datetime
               , count(app_id) as app_id 
               FROM li_appointments.li_appointments
               Where app_id in (select app_id from li_app_canceled)
               group by date(app_datetime)) 
       as cancel on days.date = cancel.app_datetime
   left Join (SELECT date(app_datetime) as app_datetime
               , count(app_id) as app_id 
               FROM li_appointments.li_appointments
               Where terp_id is not null and terp_id <> ''
               group by date(app_datetime)) 
       as covered on days.date = covered.app_datetime
   Where date > adddate(now(), Interval -30 day)
   and date <= date(now())
   group by date";

   $result = mysql_query($getapps );

   $date = array();
   $scheduled = array();
   $cnc = array();
   $dnc = array();
   $canceled = array();
   $covered = array();

   while($row = mysql_fetch_array($result)){
       array_push($date, $row['date']);
       array_push($scheduled, $row['scheduled']);
       array_push($cnc, $row['cnc']);
       array_push($dnc, $row['dnc']);
       array_push($canceled, $row['cancel']);
       array_push($covered, $row['covered']);
    }

     /* CAT:Line chart */ 

    /* pChart library inclusions */ 
    include("../class/pData.class.php"); 
    include("../class/pDraw.class.php"); 
    include("../class/pImage.class.php"); 

    /* Create and populate the pData object */ 
    $MyData = new pData();   
    $MyData->addPoints($scheduled,"Scheduled"); 
    $MyData->addPoints($cnc,"CNC"); 
    $MyData->addPoints($dnc,"DNC"); 
    $MyData->addPoints($canceled,"canceled"); 
    $MyData->addPoints($covered,"covered"); 
    $MyData->setSerieTicks("Probe 2",4); 
    $MyData->setSerieTicks("Probe 3",4); 
    $MyData->setAxisName(0,"Appointments"); 
    $MyData->addPoints($date,"Labels"); 
    $MyData->setSerieDescription("Labels","Months"); 
    $MyData->setAbscissa("Labels");

Yes, the query being slow is while I am looking for an alternative. The query itself takes 12 seconds the PHP has no delay. — June Lewis, Jul 25 '13 at 17:16
have you looked into optimizing this query? Adding indices may be ? If this query is the only source of data for you (which I am assuming it is), then you can't exclude the query from your code. — Maximus2012, Jul 25 '13 at 17:18
Maximus, thanks for your help. This is only for the trend line. I have added the code for the rest of the chart above. — June Lewis, Jul 25 '13 at 17:37
and do you already have some indices in place ? what does output of explain look like: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html — Maximus2012, Jul 25 '13 at 18:08
This is what I see from explain: 1, PRIMARY, days, range, PRIMARY, PRIMARY, 3, , 31, Using where; Using index 2, DEPENDENT SUBQUERY, app, ALL, index2, , , , 30672, Using where — June Lewis, Jul 25 '13 at 18:23
Thank you! Adding a correct index dropped it down to less than a seccond. If you would write this as an answer I would love to mark it as answered. — June Lewis, Jul 25 '13 at 18:25
on a different note, you might want to update the logic for your $getapps to avoid making joins on date fields as that may affect the performance. — Maximus2012, Jul 25 '13 at 18:46

How can I add a trendline in pchart?

0 Answers0