I am pulling college classes and details out of a MySQL database and sorting them onto a webpage with PHP.
Currently my script is simple. It pulls several fields and organizes them by class title:
Class name: Programming 101
Sometimes we have 3 or 4 of the same class going on, so this can create a rather long page. I'm trying to simplify the way the page looks.
So instead of having repeated class names:
Class name: Programming 101
Class name: Programming 101
You could see this:
Class name: Programming 101 Credit hours: 4
Class name: Programming 102 Credit hours: 4
Here is my current script:
$sql = "SELECT crs_title, trm_cde, last_name, first_name, begin_dte, end_dte, crs_cde, begin_tim, end_tim, monday_cde, tuesday_cde, wednesday_cde, thursday_cde, friday_cde, saturday_cde, sunday_cde, bldg_cde, room_cde, udef_5_2_1, crs_capacity, crs_enrollment, section_sts FROM BTC_Web_Schedule_view WHERE yr_cde = 2014 AND trm_cde = 'fa' ORDER BY crs_title, trm_cde";
$rs = odbc_exec($conn,$sql);
while (odbc_fetch_row($rs))
{
$crs_title = odbc_result($rs,"crs_title");
$trm_cde = odbc_result($rs,"trm_cde");
$crs_cde = odbc_result($rs,"crs_cde");
$begin_tim = odbc_result($rs,"begin_tim");
$end_tim = odbc_result($rs,"end_tim");
$begin_time = substr($begin_tim, 11, -7);
$end_time = substr($end_tim, 11, -7);
$begin_dte = odbc_result($rs,"begin_dte");
$end_dte = odbc_result($rs,"end_dte");
$monday_cde = odbc_result($rs,"monday_cde");
$tuesday_cde = odbc_result($rs,"tuesday_cde");
$wednesday_cde = odbc_result($rs,"wednesday_cde");
$thursday_cde = odbc_result($rs,"thursday_cde");
$friday_cde = odbc_result($rs,"friday_cde");
$saturday_cde = odbc_result($rs,"saturday_cde");
$sunday_cde = odbc_result($rs,"sunday_cde");
$first_name = odbc_result($rs,"first_name");
$last_name = odbc_result($rs,"last_name");
$fullname = $first_name.$last_name;
$bldg_cde = odbc_result($rs,"bldg_cde");
$room_cde = odbc_result($rs,"room_cde");
$udef_5_2_1 = odbc_result($rs,"udef_5_2_1");
$crs_capacity = odbc_result($rs,"crs_capacity");
$crs_enrollment = odbc_result($rs,"crs_enrollment");
$seats_left = $crs_capacity - $crs_enrollment;
$section_sts = odbc_result($rs,"section_sts");
echo "<div class='container'><p><h2>$crs_title</h2> | $crs_cde</p></div>";
echo "<div class='panel'><p><strong>Time</strong>: ".date('g:ia', strtotime($begin_time))." - ".date('g:ia', strtotime($end_time))."<br>";
echo "<strong>Start date</strong>: ".date("F jS, Y",strtotime($begin_dte))."<br>";
echo "<strong>End date</strong>: ".date("F jS, Y",strtotime($end_dte))."<br>";
echo "<strong>Days</strong>: $monday_cde $tuesday_cde $wednesday_cde $thursday_cde $friday_cde $saturday_cde $sunday_cde <br>";
echo "<strong>Instructor</strong>: $fullname <br>";
echo "<strong>Building</strong>: $bldg_cde <br>";
echo "<strong>Room</strong>: $room_cde <br>";
echo "<strong>Fee</strong>: $$udef_5_2_1 <br>";
echo "<strong>Seats remaining</strong>: $seats_left <br>";
echo "<strong>Enrollment status</strong>: $section_sts</p></div>";
}
odbc_close($conn);
I appreciate any advice. Thank you.
Normalize your database?
Other then that:
while (odbc_fetch_row($rs))
{
$crs_title = odbc_result($rs,"crs_title");
$first_name => odbc_result($rs,"first_name");
$last_name => odbc_result($rs,"last_name");
$fullname = $first_name.$last_name;
$begin_tim = odbc_result($rs,"begin_tim");
$end_tim = odbc_result($rs,"end_tim");
$crs_capacity = odbc_result($rs,"crs_capacity");
$crs_enrollment = odbc_result($rs,"crs_enrollment");
$courses[$crs_title][] = array(
$trm_cde = odbc_result($rs,"trm_cde")
'crs_cde' => odbc_result($rs,"crs_cde"),
'begin_tim' => $begin_tim,
'begin_time' => substr($begin_tim, 11, -7),
'end_tim' => $end_tim,
'end_time' = substr($end_tim, 11, -7),
'begin_dte' => odbc_result($rs,"begin_dte"),
'end_dte' => odbc_result($rs,"end_dte"),
'monday_cde' => odbc_result($rs,"monday_cde"),
'tuesday_cde' => odbc_result($rs,"tuesday_cde"),
'wednesday_cde' => odbc_result($rs,"wednesday_cde"),
'thursday_cde' => odbc_result($rs,"thursday_cde"),
'friday_cde' => odbc_result($rs,"friday_cde"),
'saturday_cde' => odbc_result($rs,"saturday_cde"),
'sunday_cde' => odbc_result($rs,"sunday_cde"),
'first_name' => $first_name,
'last_name' => $last_name,
'fullname' => $first_name.$last_name,
'bldg_cde' => odbc_result($rs,"bldg_cde"),
'room_cde' => odbc_result($rs,"room_cde"),
'udef_5_2_1' => odbc_result($rs,"udef_5_2_1"),
'crs_capacity' => $crs_capacity,
'crs_enrollment' => $crs_enrollment,
'seats_left' = $crs_capacity - $crs_enrollment,
'section_sts' => odbc_result($rs,"section_sts"),
);
foreach ($courses as $crs_title => $data) {
/* html output */
$count = count($data);
foreach ($data as $subdata) {
//multiple times
}
}
}
A few things to consider:
Change your query to do as much of the sorting as possible before even retrieving the data. For example, you could ORDER BY course title first (then secondarily by other fields like start date).
Build an array from the data such that you can easily output in teh way you want: For example:
$array = array();
while (odbc_fetch_row($rs)) {
// ... get your data (probably use something besides odbc_result so you can get ther whole array at once .. but for this example just assume I am using your same variables.
$array[$crs_title] = array(
// place your other course data here in array
);
}
foreach($array as $course_title => $course_data) {
// present your data
}