[wplug] MySQL help

[Bill Moran]

In response to Kevin Squire <gentgeen at wikiak.org>:

> sorry for the vague title, but not even sure how to title this one
> (maybe that is why Google is failing me right now also)
> 
> 
> We are building out an "attendance" module.  Everytime a kid hits the
> "attendance" page (first page after login) the student's username, IP
> address and Date are recorded to a DB table titled "cur_month"   The
> "cur_month" table is made of 4 fields:
>   username VARCHAR(124),
>   dstamp DATE, 
>   ipaddress VARCHAR(124)
>   tstamp TIMESTAMP
>   (PRIMARY is username+dstamp)
> 
> What I need some insight for is: I need to create a "report" that would
> be more teacher friendly. (think teachers gradebook)  At any time, I
> need to create output (csv, web, sql query, what ever) that would have
> one column for each day of the month, one row for each username.  Is
> there a single MySQL command that I could use for that? Or would this
> be a loop in PHP to loop through the days of the the month?

If you just do:
SELECT * FROM cur_month ORDER BY username, dstamp;

You can then do a PHP loop like:
$cur_uname = null;
$next = array();
while ($row = mysql_fetch_assoc()) {
 if ($row['username'] != $cur_uname) {
  // Store or output the old value of $next (which is a complete CSV row)
  $next = array();
  $next['username'] = $row['username'];
  $cur_name = $row['username'];
 }
 $next[$dstamp] = true;
}

So when the if statement fires, you have a complete row with all the
records for that particular username, and you can do whatever you want
to do with it (output, save to a file, put in another array ... whatever)

In that way, you'll build all the date for each username.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/