Solved

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

Posted on 2007-11-14
6
232 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
  • 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now