• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

run test on multi-dimensional array and reconfigure based on test results

Hello,
I need help creating a condtional multi-dimensional array. I am including the code for three sample related databases at the end of the question.
1. SampleCourses: One Course
2. SampleClasses: One or Many instances of each Courses
3. SampleClassDetails: One or Many details about each Class

So I have a larger query that displays each Course, plus all the instances of the Course that are being taught that semester, ie there might be Biology 101 on Thursday nights in room 1112 taught by professor X, on Wednesday Mornings in Room 3321 taught by professor Y, and etc. But then there are Class Details. Each Class might have two or more details, for example, if there is lab and a lecture portion. For bureaucratic reasons, we have to keep track of lab time separately from lecture time. So, one class detail might say: 10:00-11:01 am. Room 2230 Days W F And the other Class Detail for that Class might say: 11:02 am - 12:30 pm Room 2230 Days W F. So it looks pretty silly to display two details for one Class, when essentially the line should read: 10:00 - 12:30 pm Room 2230 Days W F. But, sometimes the Class Details need to be separated, for instance, if a Class is taught in Room 2230 on Wedesday, and Room 4444 on Friday, though at the same time.  So, I need to create a multi-dimensional array containing the following class details: RoomNo,StartTime,EndTime,DayCode. The ClassID needs to be there so that I can connect the ClassDetail to the correct Class. I need to first test to see whether the Room Number and Day Code are the same, and if so, combine the earliest Start Time of all Class Details for that particular class with the latest EndTime for all Class Details for that particular class, so that later, when I display the Class Details, they look good, not silly.

Hope I have explained this clearly enough. If not, please let me know.
Thanks!
Audrey

Here is the sample data:
-- --------------------------------------------------------

--
-- Table structure for table `SampleCourses`
--

CREATE TABLE `SampleCourses` (
  `CourseID` varchar(10) NOT NULL,
  `CourseName` varchar(255) NOT NULL,
  PRIMARY KEY  (`CourseID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SampleCourses`
--

INSERT INTO `SampleCourses` VALUES ('0100116', 'Advanced Photography');
INSERT INTO `SampleCourses` VALUES ('1610204', 'Refrigeration Fundamentals II');
INSERT INTO `SampleCourses` VALUES ('0230119', 'Environmental Biology (Pending Approval)');
INSERT INTO `SampleCourses` VALUES ('0230107', 'Nutrition-Consumer Education');
       
--
-- Table structure for table `SampleClasses`
--

CREATE TABLE `SampleClasses` (
  `ClassID` int(10) unsigned NOT NULL,
  `CourseID` varchar(10) NOT NULL,
  `SectionID` varchar(8) NOT NULL,
  PRIMARY KEY  (`ClassID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SampleClasses`
--

INSERT INTO `SampleClasses` VALUES (14555, '0100116', 'TUV');
INSERT INTO `SampleClasses` VALUES (1020, '0100116', 'U');
INSERT INTO `SampleClasses` VALUES (1979, '0100116', 'WB4');
INSERT INTO `SampleClasses` VALUES (14679, '1610204', 'ABL');
INSERT INTO `SampleClasses` VALUES (15277, '0230119', 'KS');
INSERT INTO `SampleClasses` VALUES (15778, '0230119', 'MST');
INSERT INTO `SampleClasses` VALUES (1027, '0230107', 'AB');
INSERT INTO `SampleClasses` VALUES (16050, '0230107', 'AD');
INSERT INTO `SampleClasses` VALUES (14568, '0230107', 'CD');
INSERT INTO `SampleClasses` VALUES (15357, '0230107', 'D');
INSERT INTO `SampleClasses` VALUES (552, '0230107', 'FG');
INSERT INTO `SampleClasses` VALUES (14570, '0230107', 'HJ');
INSERT INTO `SampleClasses` VALUES (3491, '0230107', 'PQY');
INSERT INTO `SampleClasses` VALUES (15982, '0230107', 'WA');
INSERT INTO `SampleClasses` VALUES (14895, '0230107', 'Z');

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

--
-- Table structure for table `SampleClassDetails`
--

CREATE TABLE `SampleClassDetails` (
  `DetailNo` mediumint(8) unsigned NOT NULL,
  `ClassID` mediumint(8) unsigned NOT NULL default '0',
  `RoomNo` varchar(10) NOT NULL default 'TBA',
  `StartTime` time default '00:00:00',
  `EndTime` time NOT NULL default '00:00:00',
  `DayCode` varchar(10) NOT NULL default '0',
  `InstrType` char(1) NOT NULL default 'L',
  `LastModifiedCD` datetime NOT NULL default '0000-00-00 00:00:00',
  `DetailsLastModifiedBy` mediumint(6) NOT NULL default '553',
  PRIMARY KEY  (`DetailNo`),
  KEY `ClassID` (`ClassID`),
  KEY `RoomNo` (`RoomNo`),
  KEY `DayCode` (`DayCode`),
  KEY `DetailsLastModifiedBy` (`DetailsLastModifiedBy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SampleClassDetails`
--

INSERT INTO `SampleClassDetails` VALUES (29537, 14555, '3929', 0x31393a31353a3030, 0x32303a30353a3030, 'T', 'L', '2007-09-28 15:18:38', 70);
INSERT INTO `SampleClassDetails` VALUES (29526, 14555, '3929', 0x32303a30363a3030, 0x32303a33353a3030, 'T', 'X', '2007-09-28 15:18:38', 70);
INSERT INTO `SampleClassDetails` VALUES (29538, 14555, '3929', 0x31393a31353a3030, 0x32303a33353a3030, 'Th', 'X', '2007-09-28 15:18:38', 70);
INSERT INTO `SampleClassDetails` VALUES (1109, 1020, '3929', 0x31393a31353a3030, 0x32303a30353a3030, 'T', 'L', '2007-02-08 15:38:24', 510);
INSERT INTO `SampleClassDetails` VALUES (28959, 1020, '3929', 0x32303a30363a3030, 0x32303a33353a3030, 'T', 'X', '2007-02-08 15:38:24', 510);
INSERT INTO `SampleClassDetails` VALUES (28960, 1020, '3929', 0x31393a31353a3030, 0x32303a33353a3030, 'Th', 'X', '2007-02-08 15:38:24', 510);
INSERT INTO `SampleClassDetails` VALUES (2078, 1979, '3929', 0x31333a30303a3030, 0x31343a30303a3030, 'Sat', 'L', '2007-10-03 11:05:37', 70);
INSERT INTO `SampleClassDetails` VALUES (29539, 1979, '3929', 0x31343a30313a3030, 0x31363a30303a3030, 'Sat', 'X', '2007-10-03 11:05:37', 70);
INSERT INTO `SampleClassDetails` VALUES (29672, 14679, '107', 0x30393a31313a3030, 0x31303a32353a3030, 'MWF', 'X', '2007-09-13 10:51:39', 70);
INSERT INTO `SampleClassDetails` VALUES (29671, 14679, '107', 0x30383a30303a3030, 0x30393a31303a3030, 'MWF', 'L', '2007-09-13 10:51:39', 70);
INSERT INTO `SampleClassDetails` VALUES (30493, 15277, '2175', 0x31353a31313a3030, 0x31363a32303a3030, 'TTh', 'X', '2007-09-26 13:03:29', 3064);
INSERT INTO `SampleClassDetails` VALUES (30492, 15277, '2175', 0x31343a30303a3030, 0x31353a31303a3030, 'TTh', 'L', '2007-09-26 13:03:29', 3064);
INSERT INTO `SampleClassDetails` VALUES (9931019, 15778, 'NEIU', 0x30383a30303a3030, 0x31313a30303a3030, 'MW', 'L', '2007-02-13 11:11:08', 70);
INSERT INTO `SampleClassDetails` VALUES (1116, 1027, '2977', 0x30393a30303a3030, 0x31323a30303a3030, 'MW', 'L', '2006-11-26 21:49:25', 192);
INSERT INTO `SampleClassDetails` VALUES (9931337, 16050, '2963', 0x30393a30303a3030, 0x31333a33303a3030, 'MW', 'L', '2007-07-03 13:17:31', 70);
INSERT INTO `SampleClassDetails` VALUES (29463, 14568, '2973', 0x31323a30303a3030, 0x31333a33353a3030, 'MW', 'L', '2007-09-27 16:55:20', 3064);
INSERT INTO `SampleClassDetails` VALUES (30581, 15357, '2977', 0x31343a30303a3030, 0x31373a30303a3030, 'MW', 'L', '2007-06-12 10:02:15', 70);
INSERT INTO `SampleClassDetails` VALUES (583, 552, '2963', 0x30393a30303a3030, 0x31303a33303a3030, 'TTh', 'L', '2007-09-06 09:30:14', 70);
INSERT INTO `SampleClassDetails` VALUES (29465, 14570, '2961', 0x31313a30303a3030, 0x31323a33353a3030, 'TTh', 'L', '2007-10-02 17:18:44', 70);
INSERT INTO `SampleClassDetails` VALUES (3622, 3491, '2963', 0x31383a30303a3030, 0x32303a35303a3030, 'M', 'L', '2007-10-18 12:54:19', 70);
INSERT INTO `SampleClassDetails` VALUES (9931252, 15982, '2973', 0x30383a35303a3030, 0x31313a35303a3030, 'Sat', 'L', '2007-10-18 12:54:55', 70);
INSERT INTO `SampleClassDetails` VALUES (30053, 14895, '2833', 0x31383a30303a3030, 0x32313a31353a3030, 'F', 'L', '2007-03-15 18:14:47', 70);
       
0
aberns
Asked:
aberns
  • 4
1 Solution
 
imitchieCommented:
how about a sample of what the output should look like?
0
 
abernsAuthor Commented:
Sorry I didn't think to do that.  I'm including a "before" and "after" Here is the "before": I've highlighted in red the rows that should be combined:
<table><tr valign="top"><td colspan="8"></td></tr><tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Art 116 Advanced Photography</strong></td></tr><tr valign="top"><td colspan="8"></td></tr>
<tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50126</td><td><a href="coursesid.php?courseid=0100116">010-0116 </a></td><td align="center">TUV</td><td align="right" style="color:red;">8:06 PM-8:35 PM</td><td align="center">T</td><td>3929</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="3">&nbsp;</td><td align="right" style="color: red">7:15 PM-8:05 PM</td><td align="center">T</td><td>3929</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr valign="top"><td colspan="3">&nbsp;</td><td align="right">7:15 PM-8:35 PM</td><td align="center">Th</td><td>3929</td><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Appliance Tech 204 Refrigeration Fundamentals  II</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>51384</td><td><a href="coursesid.php?courseid=1610204">161-0204 </a></td><td align="center">ABL</td><td align="right">8:00 AM-9:10 AM</td><td align="center">MWF</td><td>107</td><td align="right"></td></tr>
<tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Biology 119 Environmental Biology</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50120</td><td><a href="coursesid.php?courseid=0230119">023-0119 </a></td><td align="center">KS</td><td align="right" style="color:red">2:00 PM-3:10 PM</td><td align="center">TTh</td><td>2175</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="3">&nbsp;</td><td align="right" style="color:red">3:11 PM-4:20 PM</td><td align="center">TTh</td><td>2175</td><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr valign="top"><td colspan="8"></td></tr><tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Biology 107 Nutrition</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50075</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">CD</td><td align="right">12:00 PM-1:35 PM</td><td align="center">MW</td><td>2973</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50076</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">HJ</td><td align="right">11:00 AM-12:35 PM</td><td align="center">TTh</td><td>2961</td></td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50074</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">PQY</td><td align="right">6:00 PM-8:50 PM</td><td align="center">M</td><td>2963</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>51465</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">WA</td><td align="right">8:50 AM-11:50 AM</td><td align="center">Sat</td><td>2973</td></tr>

</table>

And here is how it should look after: You will see the change in ART 116 AND BIO 119

<table><tr valign="top"><td colspan="8"></td></tr><tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Art 116 Advanced Photography</strong></td></tr><tr valign="top"><td colspan="8"></td></tr>
<tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50126</td><td><a href="coursesid.php?courseid=0100116">010-0116 </a></td><td align="center">TUV</td><td align="right" style="color:red;">7:15 PM-8:35 PM</td><td align="center">T</td><td>3929</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="3">&nbsp;</td><td align="right">7:15 PM-8:35 PM</td><td align="center">Th</td><td>3929</td><td>&nbsp;</td><td>&nbsp;</td></tr>
<tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Appliance Tech 204 Refrigeration Fundamentals  II</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>51384</td><td><a href="coursesid.php?courseid=1610204">161-0204 </a></td><td align="center">ABL</td><td align="right">8:00 AM-9:10 AM</td><td align="center">MWF</td><td>107</td><td align="right"></td></tr>
<tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Biology 119 Environmental Biology</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50120</td><td><a href="coursesid.php?courseid=0230119">023-0119 </a></td><td align="center">KS</td><td align="right" style="color:red">2:00 PM-4:20 PM</td><td align="center">TTh</td><td>2175</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr>
<tr valign="top"><td colspan="8"></td></tr><tr valign="top" bgcolor="#ececec "><td colspan="6"><strong>Biology 107 Nutrition</strong></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50075</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">CD</td><td align="right">12:00 PM-1:35 PM</td><td align="center">MW</td><td>2973</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50076</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">HJ</td><td align="right">11:00 AM-12:35 PM</td><td align="center">TTh</td><td>2961</td></td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>50074</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">PQY</td><td align="right">6:00 PM-8:50 PM</td><td align="center">M</td><td>2963</td><td align="right"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td colspan="8"></td></tr><tr valign="top"><td>51465</td><td><a href="coursesid.php?courseid=0230107">023-0107 </a></td><td align="center">WA</td><td align="right">8:50 AM-11:50 AM</td><td align="center">Sat</td><td>2973</td></tr>

</table>
0
 
abernsAuthor Commented:
Hello,

I asked this question a while ago, but it is still an outstanding issue...maybe someone can take a crack at it? To summarize, I am need to create a multi-dimensional array using PHP or otherwise reformat MySQL data for display using PHP (it doesn't have to be a multidimensional array)...but numerous checks must be performed on the data first. To wit:

If the class meets on the *same days of the week* in the *same room*, combine the two rows
otherwise, keep them separate.

The rows have to be separate in the database, but I want to combine them when appropriate for display purposes.

See below for before and after.

I have included sample databases at the top for experimentation purposes.



<table>
<caption>BEFORE formatting</caption>
<tr><td>ID</td><td>CourseNo</td><td>Room Number</td><td>Days of the Week</td><td>Time</td></tr>
<tr><td>1</td><td>Biology 101</td><td>Room 2110</td><td>T/Th</td><td>8:30-12:30</td></tr>
<tr><td>2</td><td>Biology 101</td><td>Room 2110</td><td>T/Th</td><td>12:31-2:30</td></tr>
<tr><td>3</td><td>Art 200</td><td>Room 3000</td><td>M/W</td><td>11:00-12:00</td></tr>
<tr><td>4</td><td>Art 200</td><td>Room 4000</td><td>M/W</td><td>12:30-3:30</td></tr>
</table>
 
 
<table>
<caption>AFTER Formatting</caption>
<tr><td>ID</td><td>CourseNo</td><td>Room Number</td><td>Days of the Week</td><td>Time</td></tr>
 
<tr><td>1</td><td>Biology 101</td><td>Room 2110</td><td>T/Th</td><td>8:30-2:30</td></tr>
<tr><td>3</td><td>Art 200</td><td>Room 3000</td><td>M/W</td><td>11:00-12:00</td></tr>
<tr><td>4</td><td>Art 200</td><td>Room 4000</td><td>M/W</td><td>12:30-3:30</td></tr>
</table>

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
RoonaanCommented:
You mean like so:



<?php
        # Your mysql connection stuff here #
 
        # Table Configuration
	$coursesTable = 'samplecourses';
	$classesTable = 'sampleclasses';
	$detailsTable = 'sampleclassdetails';
	
        # Query Definition
	$sql = "SELECT
	         group_concat(details.DetailNo) as detailIds
	       , classes.ClassID as ID, courses.CourseName as CourseNo
	       , details.RoomNo as RoomNumber
	       , details.DayCode as DaysOfWeek
	       , concat(min(details.StartTime), '-', max(details.EndTime)) as Time
	      FROM
	      	$detailsTable details
	      , $classesTable classes
	      , $coursesTable courses
	      WHERE
	      	classes.ClassID = details.ClassID
	      	AND classes.CourseID = courses.CourseID
	      GROUP BY
	        courses.CourseID
	      , classes.ClassId
	      , details.RoomNo
	      , details.DayCode";
 
        # Query Execution	
	$result = mysql_query($sql) or print(mysql_error());
	
        # Retrieval of records
	$i = 0;
	echo '<table>';
	while($row = mysql_fetch_assoc($result)) {
		if($i++ == 0) {
			echo '<tr><th>'.implode('</th><th>', array_keys($row)).'</th></tr>';
		}
		echo '<tr><td>'.implode('</td><td>', $row).'</td></tr>';
	}
	echo '</table>';
?>

Open in new window

0
 
abernsAuthor Commented:
Wow...you really are a genius. I'm amazed! And humbled. I have one small follow-up question. Can the retreival of records part be tweaked so that the repetitive info is supressed? I.e. Only display the CourseName once...only display each section ID once? See below for a sample:

Thank you!!!!!

Advanced Photography
 	Section TUV
 	    3929	19:15:00-20:35:00	T
 	    3929	19:15:00-20:35:00	Th
 	Section U
 	     3929	 19:15:00-20:35:00	T
 	     3929	 19:15:00-20:35:00	Th
 	Section WB4
 	    3929	13:00:00-16:00:00	Sat

Open in new window

0
 
abernsAuthor Commented:
Thanks. I have to redo the code slightly, since I provided you with a pared down version of the database structure, but you have gotten me on the right track.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now