?
Solved

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

Posted on 2007-11-14
6
Medium Priority
?
238 Views
Last Modified: 2012-06-27
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
Comment
Question by:aberns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20284761
how about a sample of what the output should look like?
0
 

Author Comment

by:aberns
ID: 20285994
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
 

Author Comment

by:aberns
ID: 21568798
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 49

Accepted Solution

by:
Roonaan earned 2000 total points
ID: 21570883
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
 

Author Comment

by:aberns
ID: 21575503
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
 

Author Closing Comment

by:aberns
ID: 31409171
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

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question