How to build a statistics graph?

Question

I want to build a statistic graph that shows how many users have registered per day and maybe some other data. I have a MySql table in which I store the date they registered and usernames and etc.

How would I build such a graph ? What do I need for it ?

Answer 1

You don't always need to do things using real graphics.

<?php

// mysql connection setup
// ...

// Get the dates in a single SELECT.  2592000 seconds = 30 days
$result = mysql_query("SELECT regdate FROM users WHERE regdate > NOW()-2592000");

foreach ($row = mysql_fetch_row($result)) {
  $output[date("Y-m-d", strtotime($row['regdate']))]++;
}

$fmt = '  <tr><td>%s</td><td width="%s" background="#FF0000"> </td></tr>' . "\n";

?>
<table border="0" cellspacing="0" cellpadding="0"><tr height="100">
<?php

for ($date = time()-2592000; $date < time(); date += 86400) {
  $thisdate = date("Y-m-d", $date);
  printf($fmt, $thisdate, $output[$thisdate]);
}

?>
</tr></table>

?>

Untested, obviously. Possibly incomplete. YMMV. Salt to taste.

Answer 2

There are many ways to build a graph. I can think of a few methods, use one you think best depending on your knowledge.

In every case you need to query your database. So basics of MySQL.

Then you can either create graph on server side by looping trough result set and creating graph by simple HTML divs or using GD library.

Or you can send result set as JSON object and create graph on client side using simple HTML divs or canvas tag.

Server side graphs are much simpler but cant be animated or updated without page refresh. Client side graphs require additional knowledge (JSON, security etc.)