[sf-perl] Bay Area Tech Gnus [ot]

[Rick Moen]

Quoting David Fetter (david at fetter.org):

> Is the raw data available?  I'd like to see about munging it into an
> iCal feed, preferably without scraping your page :)

I keep thinking of iCal (and RSS), myself.  Just haven't gotten to it, and
not sure how best to do it.

The raw data are in MySQL tables, queryable only from localhost, and of
course maintained on an ongoing basis by yrs truly.  What you're seeing
in the calendar portion of the (PHP) page is rows drawn from table
"events".  The group descriptions below the calendar are in table
"groups".  Once a month, a cronjob runs that populates a new month of
"events" rows, based on data parsed from table "eventtemplate".


mysql> show tables;
+------------------+
| Tables_in_events |
+------------------+
| events           | 
| eventtemplate    | 
| groups           | 
+------------------+
3 rows in set (0.00 sec)

mysql> show columns from events;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| eventid     | int(11)     | NO   | PRI | NULL    | auto_increment | 
| ename       | varchar(40) | YES  |     | NULL    |                | 
| ecity       | varchar(40) | YES  |     | NULL    |                | 
| ecounty     | varchar(40) | YES  |     | NULL    |                | 
| edate       | varchar(40) | YES  |     | NULL    |                | 
| starttime   | int(11)     | YES  |     | NULL    |                | 
| endtime     | int(11)     | YES  |     | NULL    |                | 
| description | text        | YES  |     | NULL    |                | 
| gcode       | varchar(25) | YES  |     | NULL    |                | 
+-------------+-------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

mysql> show columns from eventtemplate;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| gcode       | varchar(25) | NO   | MUL |         |       | 
| weekday     | int(11)     | YES  |     | NULL    |       | 
| weekofmonth | int(11)     | YES  |     | NULL    |       | 
| starttime   | int(11)     | YES  |     | NULL    |       | 
| endtime     | int(11)     | YES  |     | NULL    |       | 
| description | text        | YES  |     | NULL    |       | 
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> show columns from groups;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| gname       | varchar(40) | YES  |     | NULL    |       | 
| gcity       | varchar(40) | YES  |     | NULL    |       | 
| gcounty     | varchar(40) | YES  |     | NULL    |       | 
| description | text        | YES  |     | NULL    |       | 
| gcode       | varchar(25) | NO   | PRI |         |       | 
| active      | tinyint(4)  | NO   |     | 1       |       | 
| outofarea   | tinyint(4)  | NO   |     | 0       |       | 
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

Here's the most important part of index.php, the bit that does the
calendar section:


// make the dates for the months of the events
                                                                                
for ($i=0; $i< ($nummonths + 1); $i++)
{                                                                               
        $t = mktime(0,0,0,date("m")+$i,1,  date("Y"));
                                                                                
        $months[$i] = date("M", $t);
        $years[$i] = date("Y", $t);                                             
        $dates[$i] = date("Y-m-d", $t);
}                                                                               

// now put in the html for the tables                                           

                                                                                
for ($i=0; $i<$nummonths; $i++)
{                                                                               
        require("monthhead.inc");
                                                                                
        $j = $i + 1;
                                                                                
        $query  = "select edate, starttime, endtime, ecity, ename, ";
        $query .= "description from events where edate >= '$dates[$i]'
";       
        $query .= "and edate <= '$dates[$j]' order by edate, starttime";
                                                                                
        $res = mysql_query($query);
                                                                                
        while(list($edate, $start, $end, $city, $name, $descrip)=mysql_fetch_row($res))                                                                         
        {
                                                                                
                echo "<tr><td";
                                                                                
                echo $start == 0 ? "><em>" : " class=\"str\">";
                                                                                
                echo dateExpand($edate);
                                                                                
                echo $start == 0 ? "</em></td><td" : "</td><td";
                                                                                
                if ($start == 0)
                {                                                               
                        echo "><center>-</center>";
                }                                                               
                else
                {                                                               
                        echo " class=\"str\">";
                        echo timeExpand($start, true);                          
                        echo "-";
                        echo timeExpand($end, true);                            
                        echo "";
                }                                                               
                echo "</td><td>$city</td><td>$descrip</td></tr>\n";
                                                                                
        }
                                                                                
        echo "</table>\n\n\n";
}