List:General Discussion« Previous MessageNext Message »
From:Douglas Brantz Date:July 23 2000 8:57pm
Subject:Please help with simple perl/mysql
View as plain text  
I'm building a scheduling package for school and have a page that I want
to display
a table of all classes for that day with classroom numbers running
across the top and time down the left side.  It all works except that I
have five items in the database that it finds for Wed. and 3 of them are
at the same time 8:00 and it repeats the same row three times and then
moves on to the other info.

Thought I could include my code and if anyone could help shed light on
this problem - I'm not a code person by trade and arrays are magic to
me.  I guess I want to find out how I can stop the reps of rows if  the
time in is the same, so it won't query the database again if its already
done 8:00 even if there are 5 items found and 3 of them have the same
timein - so there must be something is mysql to do a query and grab the
number of results but have each result be unique?  This might solve my
problem.

The code is very basic - I just need a way to stop having it query the
database if the time in is the same so it will stop repeating rows with
the number of classes found with the same starting time.

I'm sending this to this list because perhaps there is something in
mysql that will stop this and also I have had nothing but the best help
on this list.

Thanks in advance,

Douglas
------------------------------  code starts here----------------
#!/usr/bin/perl
#


use diagnostics;
use CGI;
use DBI;



#Setup main program
my $query= new CGI;

#Print out a contect type for http/1.0 compatibility
print $query->header("text/html");

#Grab the values from the form

my $D=$query->param("dayofweek");
#
#----- open database on Linux------------
#
my $dbh = DBI->connect("DBI:mysql:database:host","username","password");

#
#-----Create the command---------------
#Begin HTML output
print $query->start_html (-title =>'Database Query
Results',-BGCOLOR=>'#ffffff',-vlink=>'Blue',-alink=>'Blue',-link=>'Blue');

#The \n's in the following code only affect the output of the
#info that comes back on the next screen.
#
if ($D eq "M") {
        $theday="Monday";
}
if ($D eq "T") {
        $theday="Tuesday";
}
if ($D eq "W") {
        $theday="Wednesday";
}
if ($D eq "R") {
        $theday="Thursday";
}
if ($D eq "F") {
        $theday="Friday";
}
if ($D eq "S") {
        $theday="Saturday";
}
if ($D eq "U") {
        $theday="Sunday";
}

print "<font color=\"red\">";
#print data to screen for user to see
print "<h2><center>Scheduled Courses for $theday";
print "</h2></center></font></P>";
print "<center>";
print "<table border=\"1\"><tr><td>(Time/Room)</td>";
#--------------- Grab room info for top of table-------------
my $sth20 = $dbh->prepare("select room from room Order by room") or
bail_out("Cannot Connect");
$sth20->execute;
my @arr=();
while (@arr = $sth20->fetchrow)
{
($room)=@arr;
$theroom=$room;
print "<td align=center>$theroom</td>";
}
$n="0";
print "</tr><tr>";
$change=$tm;
#--------------------------------------select beginning time for all
classes on day
my $sth9 = $dbh->prepare("select timein from courses where
done=\"Yes\" AND schdays LIKE \"\%$D\%\" order by timein") or
bail_out("Cannot Connect");
$sth9->execute;
while (@record = $sth9->fetchrow)
{
($tm)=@record;
if ($change ne $tm) {
print "</tr><tr>";
}
if ($change eq $tm) {
#}
print "";
}
print "<td>$tm</td>";  #-----this prints out the starting time for each
row
#----------------- get room number ----------------
my $sth19 = $dbh->prepare("select room from room Order by room") or
bail_out("Cannot Connect");
$sth19->execute;
while (@thatroom = $sth19->fetchrow)
{
$n=$thetime;
($room)=@thatroom;
$theroom=$room;

#print "<td>@thatroom";
print "<td>";
#---------------------- get main ------------
my $sth1 = $dbh->prepare("select
id,sec,title,schdays,timein,timeout,room
from courses where done=\"Yes\" AND schdays LIKE \"\%$D\%\" AND
room=\"$theroom\" AND timein=\"$tm\" order by timein") or
bail_out("Cannot Connect");
$sth1->execute;
my @arr=();
while (@arr = $sth1->fetchrow)
{
($id,$sec,$title,$schdays,$timein,$timeout,$room)=@arr;
$cid=$id;
$csec=$sec;
$ctitle=$title;
$cschdays=$schdays;
$ctimein=$timein;
$ctimeout=$timeout;
$croom=$room;
#--------------------------------- Print it out----
print "$csec, $ctitle <BR>";
print "$cschdays, $ctimein-$ctimeout<BR>$croom</td>";
}
}
}
print "</td></tr></table>";
print "<A Href=\"select_day.pl\">Back to Day Selection Menu</a>";
#End HTML
print $query->end_html;
$dbh->disconnect;
exit;

------------------------------ End of Code-----------------------




Thread
Please help with simple perl/mysqlDouglas Brantz23 Jul
  • Re: Please help with simple perl/mysqlDouglas Brantz24 Jul