[prog] php & mysql

[Jacinta Richardson]

On Tue, 24 Feb 2004, wolf wrote:

> Hi, I'm trying to make a nice looking room chart that shows where 
> classes are held, which room number, what class, and so on.
> I have this query that I was given:

> what I'd like the code to do is produce a table that looks more like 
> this:
> 
> ______________________Period 3______________________________
> 
> Room     Mon                 Tues                   Wed              
> Thur                  Fri
> 
>     14        ACCT I             ACCT I               ACCT I             
> ACCT I         ACCT I
>                     Wilt                   Wilt                       
> Wilt                    Wilt               Wilt
> 
> 
> does anyone happen to see a way I could either re-write the query or a 
> way to re-write the php code so that the table would
> appear like the second example?

What you're going to need to do is parse the schedule and then,
once you've gotten everything out of the database, print out your
table.  This will result in code something like the following.
I'm a Perl coder and I don't have the time to work out what all
the equivalent functions are in php, so I'm going to use perl
jargon.  If you have any trouble translating it back into php let
me know.

<?php
    while($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {

          # split the schedule up on commas and put into an array
        @split = split( ",", $row_Recordset1['schedule']);

          # for each item in the schedule
        foreach $value (@split) {
              # split it into day and hour
            ($day, $time) = split ("/", $value);

            $room = $row_Recordset1['room_code'];

              # add data to day_time structure [1]
              # first check we're not overwriting anything
            if(exists $day_time{$room}{$day}{$time}) {
                warn "$room is double booked on $day at $time between ".
                     "$row_Recordset1['course_title'] - ".
                     "$row_Recordset1['l_name'] and ".
                     "$day_time{$room}{$day}{$time}{course} - ".
                     "$day_time{$room}{$day}{$time}{faculty}\n";
            }
            else
            {
                   # add course, faculty to this room/day/time.
                 $day_time{$room}{$day}{$time} = {
                     course => $row_Recordset1['course_title'],
                     faculty => $row_Recordset1['l_name']
                 };
            }
        }
    }

      # Now we print out our data structure in a nice table
    foreach $room (1 .. 14) {    # assuming rooms 1 - 14 {
        print "<tr>";
        $tr = 1;
        print "<td>$room</td>";

        foreach $time ( "9", "12", "3" ) {  # assuming 3 periods
                # Start each new time on a new row except the first
                # pad out the room value so that everything lines up
            if($tr == 0) {
                print "<tr>";
                print "<td>&nbspl</td>";
            }
            print "<td>$time</td>";
            $tr = 0;

            foreach $day ("Mon", "Tues", "Wed", "Thur", "Fri") {
                if(exists $day_time{$room}{$day}{$time}) {
                    print "<td>$day_time{$room}{$day}{$time}{course}<br>".
                          "
$day_time{$room}{$day}{$time}{faculty}</td>";
                } else {
                    print "<td>&nbsp;</td>";
                }
            }
            print "</tr>";
        }
   }
?php>

[1]  our day_time structure would be called a hash of hashes (of
hashes of hashes) in Perl.  In php there is no distinction
between hashes and arrays so you'll need to change all of my
curly braces around array elements to square ones.

This data structure maps rooms to days and that to times and that
to course and faculty.  The data structure might look like this
if you were to print it out.

Room       Day       Time
   1  ->   Mon   ->  9  ->  course   -> "Mathematics"
                            faculty  -> "Science"
                    12  ->  course   -> "English"
                            faculty  -> "Arts"
                     3  ->  course   -> "Accounting"
                            faculty  -> "Commerce"
           Tues  ->  12 ->  course   -> "Mathematics"
                            faculty  -> "Science"

etc.  That is:  $day_time is a hash of rooms.
                $day_time{$room} is a hash of days
                $day_time{$room}{$day} is a hash of times
                $day_time{$room}{$day}{$time} is a hash of course
                   information  containting course name and faculty.

I'm sure that you've got to be able to build up structures like
this in php.
array day_time = array( 1 => array( Mon => array( 9 =>
       array(course=>"Mathematics", faculty => "Science" ))));

or something like that.  It's probably easy enough to build them
up on the fly like I did too.

-----------------------

You'll probably need to adjust the rooms and the times above, but
I'm sure that'll be easy. 

I hope this helps.

     Jacinta

--
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +613 9354 6001         |  
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
(il),-''  (li),'  ((!.-'              |   www.perltraining.com.au   |