Solved

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

Posted on 2007-11-14
6
236 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 49

Accepted Solution

by:
Roonaan earned 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL recovery 7 33
Wordpress and Wufoo 1 39
Pull Variable from URL  Use php template 1 35
Compiling PHP with Curl plus protocols 8 24
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article discusses how to implement server side field validation and display customized error messages to the client.
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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

733 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