List:General Discussion« Previous MessageNext Message »
From:Douglas Brantz Date:July 24 2000 3:13am
Subject:Re: Please help with simple perl/mysql
View as plain text  
> My problem is my select statement creates an @array with the following times:

I get the following when I print the array-
08:0008:0008:0009:301600

And this is correct because there are five courses at this time, but is causing
the first
row to be repeated 3 times and I just need 1 row of each start time.

Is there a way in mysql to select all starting times that are unique? so that I
get;
08:0009:3016:00
Or is there a way to stop or delete repetitions in the array?

Hope this makes sense?
The area I am talking about is at $sth9 in my code below.

Thanks again,

Douglas

>
> Subject: Please help with simple perl/mysql
>
> > 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 classses that day
> > 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-----------------------
> >
> >
> >
> >
> >
> > --
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/php/manual.php" before
> > posting. To request this thread, e-mail mysql-thread45080@stripped
> >
> > To unsubscribe, send a message to:
> >     <mysql-unsubscribe-rhopkins=mail.waytech.com.tw@stripped>
> >
> > If you have a broken mail client that cannot send a message to the above
> address(Microsoft Outlook), you can use
> http://lists.mysql.com/php/unsubscribe.php

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