Group Database Results

cdrees

Hello Everyone,

I have a DB that I'm developing, that I've currently got functional, but I want to clean up the output.

Currently, I have a site that displays currently available courses, listed by date. It shows the following:

Course Date Course Title Notes

2005-12-20 Course1 9am-5pm
2005-12-23 Course2 6pm-10pm
2005-12-26 Course1 9am-5pm
2005-12-30 Course1 9am-5pm

I'd like it to group by Course (if one is schedule), list the actual course title only once in my output, and then under each Course Title Heading list the dates and notes, like so:

Course One

2005-12-20 9am-5pm
2005-12-26 9am-5pm
2005-12-30 9am-5pm

Course Two

2005-12-23 6pm-10pm

I have three tables currently, one to hold the courses, one to hold locations, and one to hold scheduled dates:

CREATE TABLE `academy_loc` (
  `loc_id` int(5) NOT NULL auto_increment,
  `location_st` varchar(5) collate latin1_general_ci NOT NULL,
  `location_co` varchar(25) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`loc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;



CREATE TABLE `academy_main` (
  `main_id` int(5) NOT NULL auto_increment,
  `loc_id` int(5) NOT NULL,
  `course_title` varchar(150) collate latin1_general_ci default NULL,
  `course_desc` text collate latin1_general_ci,
  `class_type` varchar(15) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=40 ;



CREATE TABLE `academy_sched` (
  `sched_id` int(11) NOT NULL auto_increment,
  `main_id` int(11) NOT NULL,
  `course_date` date default NULL,
  `sched_notes` text collate latin1_general_ci,
  PRIMARY KEY  (`sched_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=75 ;

Thanks in advance!!

devinemke

a simple example:

$result = mysql_query('SELECT * FROM academy_main') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$courses[$row['main_id']] = $row;}

$result = mysql_query('SELECT * FROM academy_sched') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$courses[$row['main_id']]['sched'][] = $row;}

foreach ($courses as $value)
{
	echo '<b>' . $value['course_title'] . '</b><br>';
	foreach ($value['sched'] as $subvalue)
	{
		echo $subvalue['course_date'] . '<br>';
	}
	echo '<br>';
}

cdrees

Thanks for the reply.

My question is, how can I run two queries under the same variable? (two $result = queries)

devinemke

cdrees wrote:
My question is, how can I run two queries under the same variable? (two $result = queries)

huh? i don't undertand what you are asking.

cdrees

$result = mysql_query('SELECT * FROM academy_main') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$courses[$row['main_id']] = $row;}

$result = mysql_query('SELECT * FROM academy_sched') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$courses[$row['main_id']]['sched'][] = $row;}

If I'm assigning $result a value twice, doesn't the second $result= over write the value of the first? (I'm just going off the code you wrote above... not sure if that was meant to be a literal drop in section of code or not..)

devinemke

cdrees wrote:
If I'm assigning $result a value twice, doesn't the second $result= over write the value of the first?

yes but it does not matter, the $courses array is being populated correctly.

cdrees

ah... gotcha, thanks very much for the help!