Organizing member join dates/member growth data into graphable chunks

Question

I'm dabbling with pChart and would like to start with a simple line graph showing the growth in membership over time.

Y-axis would be # of members
X-axis would be time

For each time datapoint, I need a corresponding total members datapoint.

My user table is structured as: [user_id] [join_date]

The approach I came up with on the bus to work this morning is:

$Q = " SELECT MONTH(join_date), DAY(join_date), COUNT(user_id)"
   . " FROM user_basic_data GROUP BY join_date";
$R = mysql_query($Q);

$dateS = '';
$totalS = '';
$c = 0; // total members counter
while ($row = mysql_fetch_row($R)) {
    $dateS .= $row[0].'-'.$row[1].','; // month-day,month-day,month-day
    $c = $row[2] + $c; // new total for new date
    $totalS .= $c.','; // total1,total2,total3
}
// trim trailing commas
$dateS = substr($dateS, 0, -1);
$totalS = substr($totalS, 0, -1);

echo "<p>$dateS</p>"; // Ex: 8-10,8-15,8-20
echo "<p>$totalS</p>"; // Ex: 12,17,23

Those string formats are how pChart likes the data, and I know the current query would need a year value as well for real use, so please don't get hung up on those points.

I'd like to know if there's a better way to go about getting the changing total members over time. I'm guessing handling it within MySQL would be faster, but I can't think of a way to do that.

Thank you for your time.

Answer 1

To get a running total, use:

SELECT DISTINCT
       DATE(ubd.join_date) AS dt, 
       (SELECT COUNT(*)
          FROM user_basic_data t
         WHERE DATE(t.join_date) <= DATE(ubd.join_date)) AS num_users
  FROM user_basic_data ubd 

DATE returns dates as YYYY-MM-DD; If you still want Month-Day - use DATE_FORMAT by replacing the DATE(udb.join_date) with:

DATE_FORMAT(ubd.join_date), '%m-%d')

You don't need the logic to create the comma separated lists in PHP - just need to populate the two variables:

$Q = " SELECT GROUP_CONCAT(x.dt) AS dates,
              GROUP_CONCAT(x.num_users) AS totals
         FROM (SELECT DISTINCT
                      DATE(ubd.join_date) AS dt, 
                      (SELECT COUNT(*)
                         FROM user_basic_data t
                        WHERE DATE(t.join_date) <= DATE(ubd.join_date)) AS num_users
                 FROM user_basic_data ubd ) x";
$R = mysql_query($Q);

while ($row = mysql_fetch_row($R)) {
   echo "<p>$row[0]</p>"; // Ex: 8-10,8-15,8-20
   echo "<p>$row[1]</p>"; // Ex: 12,17,23
}