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 ?

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 ?

• php
• javascript
• graph

• You need a php script that can generate images. Either build one yourself, or choose from the many that exist already. A short google for 'php graph' will reveal enough. Check graphpite.sourceforge or jpgraph/download – Konerak Commented Jan 29, 2012 at 13:14
• 1 You know about R right? You can pull your relevant DB data and then graph it in R... – learner Commented Jan 29, 2012 at 13:17
• Oh, I've tried to Google it, but without PHP, I wasn't sure that I should use PHP or JS, but logically I need PHP to read from the db :) – Roland Commented Jan 29, 2012 at 13:18
• Sorry, brainfart on my part. R is a stats language with good graph making functions, but actually I would go with your scripting language. These libraries look like good places to start. – learner Commented Jan 29, 2012 at 13:23

3 Answers 3

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 inplete. YMMV. Salt to taste.

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.)

If you only need bar graphs, you might be much better of using divs of a calculated height, all anchored to a bottom line. This is quite trivial to write and uses a whole lot less of CPU, RAM and bandwidht.