Multiple Columns for MySQL output

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I have a table of courses with Course_ID. Each course has multiple sessions for the academic year. These "sessions" are whats called "team learning" for medical students. Each course has different sessions and number of session held.

I would like to construct a page with a column of Course Names in the first column and each row of Course Name listing all the TL sessions created for that course .

Anatomy may have as many as 12 Team Learning sessions.

Example

Course Name
Human Development------session 1------session 2------session 3------session 4
Principle of Disease---------session 1------session 2------session 3
Neuroscience----------------session 1------session 2------session 3------session 4------session 5

The session names (session 1) will be linked to another page so that all the grades given out for the different tests during a TL session are displayed.

I could easily count the Session_ID's for each Course then stick it in some kind of loop to construct each column, or get the max(count) for all courses and create the number of columns that way. I would just have empty cells in the table where no session exist for those courses.

If anybody has seen this done and can give me an example it would be mucho appreciated.

Seeing if I can count the max number of sessions for any course. This way I would know how many columns I would need.

I'm running MySQL 4.1.13

Code:




$sessioncount = $db->sql_query("SELECT MAX(count) FROM (SELECT COUNT (Session_ID) as maxsession FROM atlas_tl_session) GROUP BY Session_ID");


while($info = $db->sql_fetchrow($sessioncount)) {


$maxsession = $info[maxsession];


}





echo "$maxsession";

I know some versions of MySQL don't support sub queries.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

How is the course name record laid out? Is there one record with 12 session fields or is there one record for each course name and session? Let me know.

Posted: Thu Dec 06, 2007 10:22 am

#
# Table structure for table `atlas_courses`
#

`Course_ID` varchar(25) NOT NULL default '0',
`Course_Name` varchar(255) NOT NULL default '',

#
# Table structure for table `atlas_tl_session`
#

`Session_ID` int(11) NOT NULL auto_increment,
`Course_ID` varchar(25) NOT NULL default '',
`Director_ID` varchar(25) NOT NULL default '',
`Session_Name` varchar(50) NOT NULL default '',

Course_ID exists many times in the tl_session table.

Posted: Thu Dec 06, 2007 10:31 am

Steve,

I am having a Narcolepsy nap attack and I have to sleep - quickly. I will answer this when I wake up.

Posted: Thu Dec 06, 2007 8:12 pm

Steve,

You don't really need to know how many ahead of time because the HTML processor will adjust to the maximum number of columns before rendering the table in the browser. So, if I'm understanding what you are after, the code would look something like this. Now I haven't done any testing. I just wrote this code off the top of my head so try working with it to see if it gets you started.

Code:

<?


   /* Assuming $prefix = 'atlas' */


   // Build sql


   $sql      = 'SELECT c.Course_Name, s.Session_Name FROM '.$prefix.'_tl_courses AS c, '.$prefix.'_sessions AS s ';


   $sql     .= 'WHERE c.Course_ID = s.Course_ID ';


   $sql     .= 'ORDER BY c.Course_Name, s.Session_Name ';





   // Attempt to query database


   $result   = $db->sql_query($sql);





   // Error routine based on $result if desired





   // Switch to trigger Course Name change


   $lastCourse_Name = '';





   // Start building page code.  This is faster than doing individual echo statements


   $htmlCode = '<table><tr>';  // Start table and first row





   // Cycle through table


   while(list($Course_Name, $Session_name) = $db->sql_fetchrow($result)) {


   // End row and start new row when Course Name Changes but not on first pass


      if (!empty($lastCourse_Name) && $lastCourse_Name != $Course_Name) $htmlCode .= '</tr><tr>';


   // Build table columns


      if ($lastCourse_Name != $Course_Name) $htmlCode .= '<td>'.Course_Name.'</td>';


      $htmlCode .= '<td>'.$Session_Name.'</td>';


   // Remember Course Name for next pass to start new row if needed


      $lastCourse_Name = $Course_Name;


   }


   // End cycle





   // End last row and table


   $htmlCode .= '</tr></table>';





   // Display HTML to Browser


   echo $htmlCode;


   $db->sql_close($result);


?>

Posted: Thu Dec 06, 2007 10:28 pm

WOW!!!

This place is well worth any donations I send your way. Smile

Posted: Fri Dec 07, 2007 12:22 am

If you expect me to disagree with that statement, it ain't a gonna happen killing me

Did it work?

Posted: Fri Dec 07, 2007 12:26 am

I saw a punctuation error in the script and I corrected it. Also be aware that if the 2 table keys don't find a match the record(s) will be skipped because we are doing a natural join. If you need to show mismatches then you will need to look into a left or right outer join.

Posted: Fri Dec 07, 2007 6:04 am

I don't know.. I'm at home right now and taking Friday off for a doctors appointment. I'll have to implement it on Monday and test.

Thanks for your help on this.

Posted: Mon Dec 10, 2007 8:12 am

For the most part this works except for needing the Course_Name to be displayed on the first row and with every new row.

Now only the Session_Name is displayed for each row.

Each row does have those sessions that belong to a new course, but the Course_Name is not displayed in the first column.

...savy?

Posted: Mon Dec 10, 2007 9:11 am

// Build table columns
$htmlCode .= '<td>'.$Course_Name.'</td><td>'.$Session_Name.'</td>';

Posted: Mon Dec 10, 2007 9:20 am

Well I thought about that but I need the Course_Name listed once in the first column with the following columns showing all the Session_Names.

A new row has a new Course_Name.

Posted: Mon Dec 10, 2007 9:34 am

Since I don't have the data to test with I am just making educated guesses here.

// Build table columns
if ($lastCourse_Name != $Course_Name) $htmlCode .= '<td>'.Course_Name.'</td>';
$htmlCode .= '<td>'.$Session_Name.'</td>';

Posted: Mon Dec 10, 2007 9:57 am

Outstanding.

Now if I can only get each Session_Name linked.

Something like...

Code:

<a href='".$admin_file.".php?op=TLSessionGrades&amp;Session_Name=$Session_Name>'$Session_Name</a>

Posted: Mon Dec 10, 2007 10:03 am

I think this will work.

Code:




$htmlCode .= "<td><a href='".$admin_file.".php?op=TLSessionGrades&amp;Session_Name=$Session_Name'>$Session_Name</a></td>";

I would rather use Session_ID but I can use Session_Name just as well.

Posted: Mon Dec 10, 2007 12:55 pm

Have fun!