[RESOLVED] Multiple Columns for MySQL output

[SFDonovan]

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 and each row of Course Name listing all the sessions created.

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.

[SFDonovan]

Is this right?

$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";

[SFDonovan]

I forgot to add that I'm running MySQL 4.1.13 as I remember reading that certain versions of MySQL don't support sub queries.

[aftershock2020]

SFDonovan wrote:

I forgot to add that I'm running MySQL 4.1.13 as I remember reading that certain versions of MySQL don't support sub queries.

I use that same version of MySql. Don't worry about using sub-queries at all. Get around them by using " table joining ", which is the method of pulling from multiple rows and/or tables straight from the database within a single query statement, having a much more effective batching result.

Here's a couple of tutorials on the topic:

http://php.about.com/od/learnmysql/ss/mysql_joins.htm

http://www.tizag.com/mysqlTutorial/mysqljoins.php

Hope this helps,

Aftershock

[SFDonovan]

This is how I did it.

   // Build sql
   $sql      = 'SELECT c.Course_Name, s.Session_Name, s.TLDate  FROM '.$prefix.'_tl_courses AS c, '.$prefix.'_tl_session AS s ';
   $sql     .= 'WHERE c.Course_ID = s.Course_ID ';
   $sql     .= 'ORDER BY c.Course_Name, s.TLDate';

   // Attempt to query database
   $result   = $db->sql_query($sql);

   // I may need error routine based on $result

   // Switch to trigger Course Name change
   $lastCourse_Name = '';

   // Start building page code.  This is faster than doing individual echo statements
   $htmlCode = '<table width=\'100%\' border=\'1\'><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 width=\'15%\'>'.$Course_Name.'</td>';
$htmlCode .= "<td><a href='".$admin_file.".php?op=TLSessionGrades&amp;Session_Name=$Session_Name'>$Session_Name</a></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;

[aftershock2020]

If I am reading you right, and feel free to correct me if I'm wrong, you are looking to represent what you are calling " sessions " as the variables for the time duration of the course for a given section of it, correct?

If this is the case, what I recommend is creating a drop menu, supporting the given time session the user wants to select. This will prevent anyone from seeing the dead areas you have for any given selection.

With that said, I recommend possibly using a drop menu to shorten you display issues on it. You can see how that works by checking out this link:

http://help.joomla.org/index2.php?option=com_content&do_pdf=1&id=130

See if that helps you out to shrink your source code and make the functions a little tighter.