Forum archive 2000-2006

Nandor Sieben - sql query for due dates

Nandor Sieben - sql query for due dates

by Arnold Pizer -
Number of replies: 0
inactiveTopicsql query for due dates topic started 9/14/2005; 1:13:55 PM
last post 9/15/2005; 8:11:44 PM
userNandor Sieben - sql query for due dates  blueArrow
9/14/2005; 1:13:55 PM (reads: 527, responses: 2)
Having several classes using webwork makes it a hard job to post correct due dates on the course websites. It would be nice to just link to a web site containing due dates for a class. These due date web sites could be generated automatically by a cron job doing an sql query. I can probably write this script but I need help with the sql commands needed to get the due dates from the data base. Could someone help me with this?

Nandor

<| Post or View Comments |>

userJohn Jones - Re: sql query for due dates  blueArrow
9/14/2005; 5:48:37 PM (reads: 652, responses: 0)
Hi Nandor,

Assuming sql_single for the database type, and course name Jones_MAT_170_Fall_2005_set, and set name Section_1.4 one can use

 

echo 'select due_date from Jones_MAT_170_Fall_2005_set where set_id="Section_1.4";'
 | mysql -uwebworkRead -pThePassword -Dwebwork
I broke it over two lines to avoid an incredibly long line.

The command above produced for me 

due_date
1106895540
so you just have to discard the first line and then convert the time.

John

<| Post or View Comments |>

userNandor Sieben - Re: sql query for due dates  blueArrow
9/15/2005; 8:11:44 PM (reads: 621, responses: 0)
Here is a script that prints all due dates that are in the future.

#!/usr/bin/perl

# Duedates # Prints due dates # Author: Nandor Sieben

$password = 'write your password here';

sub mysql { my ($sql) = @_; # print "$sql"; qx[mysql -s -u root -p$password webwork << EOF $sql EOF ]; }

@tables= mysql(q[show tables like '%_set';]);

$ctime=time;

foreach $table (@tables) { chop $table; @rows=mysql(qq[select set_id, due_date from $table]); foreach $row (@rows) { $table =~s /.setZ//; if ( $row =~ /(S+)s+(S+)/) { $set=$1; $stamp=$2; if ($stamp < $ctime) { next; } $lt=localtime($stamp); print "$table \t $lt \t $set \n"; } } }

<| Post or View Comments |>