function searchCourses($search)
{
$sql = "SELECT DISTINCT strCourseName AS courseName,\n\t\tintSectionID AS secID,\n\t\tCONCAT(tblcourse.strCourseID,'-',intSectionNumber) AS secNumber,\n\t\tstrFirstName,\n\t\tstrLastName,\n\t\tstrDayFormat,\n\t\tCONCAT(DATE_FORMAT(timStartTime,'%l:%i%p'),'-',DATE_FORMAT(timEndTime,'%l:%i%p')) AS time,\n\t\tstrFacilityName,\n\t\tstrRoomNumber\n\t\tFROM tblCourse\n\t\tINNER JOIN tblsection ON tblcourse.strCourseID = tblsection.strCourseID\n\t\tINNER JOIN tblfaculty ON tblsection.intFacultyID = tblfaculty.intFacultyID\n\t\tINNER JOIN tblsectionschedule ON tblsection.intScheduleID = tblsectionschedule.intDaySlotID\n\t\tINNER JOIN tblsectiontimes ON tblsection.intTimeSlotID = tblsectiontimes.intTimeSlotID\n\t\tINNER JOIN tblroom ON tblsection.intRoomID = tblRoom.intRoomID\n\t\tINNER JOIN tblfacility ON tblroom.intFacilityID = tblFacility.intFacilityID";
$term = preg_replace('/\\s+/', ' ', $search);
$term = preg_replace('/^\\s|\\s$/', '', $term);
//TODO: Sanitize $term
$words = explode(' ', $term);
$fields = array('tblcourse.strCourseID', 'tblcourse.strDeptCode', 'tblfaculty.strLastName', 'tblfaculty.strFirstName', 'tblcourse.strCourseName');
$where = NULL;
foreach ($words as $wd) {
$andwhere = NULL;
if ($where) {
$where .= ' AND';
}
foreach ($fields as $field) {
if (!empty($wd)) {
if ($andwhere) {
$andwhere .= ' OR';
}
$andwhere .= " {$field} LIKE '%{$wd}%'";
}
}
if ($andwhere) {
$where .= " ({$andwhere})";
}
}
if ($where) {
$sql .= " WHERE {$where} ORDER BY secNumber";
}
if ($result = dbGetAll($sql)) {
echo "<thead>";
echo "<tr><td class=\"thr\">Select</td>";
echo "<td class=\"thr\">Course Name</td>";
echo "<td class=\"thr\">Section</td>";
echo "<td class=\"thr\">Schedule</td>";
echo "<td class=\"thr\">Time</td>";
echo "<td class=\"thr\">Instructor</td>";
echo "<td class=\"thr\">Facility</td>";
echo "<td class=\"thr\">Room</td></tr>";
echo "</thead><tbody>";
foreach ($result as $row) {
echo "<tr>";
//echo "<td class=\"advcell\"><input type=\"checkbox\" name=\"check[".$row['secID']."]\" value=\"\" /></td>";
echo "<td class=\"advcell\"><input type=\"checkbox\" name=\"check[]\" value=\"" . $row['secID'] . "\" /></td>";
echo "<td class=\"advcell\">" . $row['courseName'] . "</td>";
echo "<td class=\"advcell\">" . $row['secNumber'] . "</td>";
echo "<td class=\"advcell\">" . $row['strDayFormat'] . "</td>";
echo "<td class=\"advcell\">" . $row['time'] . "</td>";
echo "<td class=\"advcell\">" . $row['strFirstName'] . ' ' . $row['strLastName'] . "</td>";
echo "<td class=\"advcell\">" . $row['strFacilityName'] . "</td>";
echo "<td class=\"advcell\">" . $row['strRoomNumber'] . "</td>";
echo "</tr>";
}
echo "</tbody>";
} else {
echo "<tr><td class=\"advcell\">Your search returned no results.</td></tr>";
}
}
function plotfromdatabase()
{
$sql = "SELECT DISTINCT fac.strFacilityName, fac.intFacilityID, fac.intLatitude, fac.intLongitude\n\t\t\t\tFROM tblfacility AS fac\n\t\t\t\tJOIN tblRoom ON tblRoom.intFacilityID = fac.intFacilityID\n\t\t\t\tJOIN tblSection ON tblRoom.intRoomID = tblSection.intRoomID\n\t\t\t\tJOIN tblStudentEnrollment ON tblStudentEnrollment.intSectionID = tblSection.intSectionID\n\t\t\t\tJOIN tblStudent ON tblStudent.intStudentID = tblStudentEnrollment.intStudentID\n\t\t\t\tWHERE tblStudent.strStudentEID = ?";
if ($result = dbGetAll($sql, "s", $_SESSION['cruser'])) {
foreach ($result as $row) {
addmarker($row["strFacilityName"], strtolower(substr($row["strFacilityName"], 0, 1) . $row["intFacilityID"]), $row["intLatitude"], $row["intLongitude"]);
}
}
}
function popDropDown()
{
// Spit out default option
echo '<option value="-1">---</option>';
// Retrieve options from db
if ($result = dbGetAll("SELECT intDeptID as id, strDeptCode as code FROM tblDepartment;")) {
foreach ($result as $row) {
echo '<option value="' . $row['id'] . '">' . $row['code'] . '</option>';
}
}
}
function popDropDown()
{
// Spit out default option
echo '<option value="-1">---</option>';
// Retrieve options from db
if ($result = dbGetAll("SELECT intFacilityId as id, strFacilityName as name FROM tblFacility;")) {
foreach ($result as $row) {
echo '<option value="' . $row['id'] . '">' . $row['name'] . '</option>';
}
}
}
function loadadvisors()
{
$sql = "SELECT CONCAT(fac.strFirstName,' ',fac.strLastName) AS \"strAdvisor\",\n\t\t\tfac.strPhone AS \"strAdvPhone\",\n\t\t\tfac.strFacultyEID AS \"strFEID\",\n\t\t\tdep.strDeptName AS \"strDept\"\n\t\t\tFROM tblstudent stu\n\t\t\tJOIN tblfaculty fac ON stu.intFacultyID = fac.intFacultyID\n\t\t\tJOIN tbldepartment dep ON fac.intDeptID = dep.intDeptID\n\t\t\tWHERE strStudentEID = ?;";
if ($result = dbGetAll($sql, "s", $_SESSION["cruser"])) {
foreach ($result as $row) {
echo "<td class=\"advcell\">" . $row["strAdvisor"] . "</td>\r\n";
echo "<td class=\"advcell\">" . preg_replace("/(\\d{3})(\\d{3})(\\d{4})/", "(\\1) \\2-\\3", $row["strAdvPhone"]) . "</td>\r\n";
echo "<td class=\"advcell\">" . $row["strFEID"] . "@ndsu.edu</td>\r\n";
echo "<td class=\"advcell\">" . $row["strDept"] . "</td>\r\n";
}
}
}
function fillSelect()
{
$eid = $_SESSION['cruser'];
$sql = "SELECT DISTINCT sec.intYear as iYear, sec.strSeason as sSeason\n\t\t\t\tFROM tblSection sec\n\t\t\t\tJOIN tblStudentEnrollment enr ON sec.intSectionID = enr.intSectionID\n\t\t\t\tJOIN tblStudent stu ON stu.intStudentID = enr.intStudentID\n\t\t\t\tWHERE stu.strStudentEID = ?\n\t\t\t\tORDER BY iYear";
if ($result = dbGetAll($sql, "s", $eid)) {
$i = 0;
foreach ($result as $row) {
echo '<option value="' . $i . '">' . $row['sSeason'] . ' ' . $row['iYear'] . '</option>';
$i++;
}
}
}
<?php
if (session_status() == PHP_SESSION_NONE) {
session_start();
}
loginHandler();
echo "<table class=\"schedule\">\r\n";
echo "<tr>\r\n<td class=\"thr\">Time</td><td class=\"thr\">Monday</td><td class=\"thr\">Tuesday</td><td class=\"thr\">Wednesday</td><td class=\"thr\">Thursday</td><td class=\"thr\">Friday</td>\r\n</tr>";
// Fetch class data
$eid = $_SESSION['cruser'];
$sql = "SELECT sec.strCourseID AS id, sch.strDayFormat AS timeSlot, tim.timStartTime AS timeStart, tim.timEndTime AS timeEnd\n\t\tFROM tblSection sec\n\t\tJOIN tblSectionSchedule sch ON sch.intDaySlotID = sec.intScheduleID\n\t\tJOIN tblSectionTimes tim ON tim.intTimeSlotID = sec.intTimeSlotID\n\t\tJOIN tblStudentEnrollment enr ON sec.intSectionID = enr.intSectionID\n\t\tJOIN tblStudent stu ON stu.intStudentID = enr.intStudentID\n\t\tWHERE stu.strStudentEID = ?";
$newSchedule = dbGetAll($sql, 's', $eid);
$times = array('8:00am', '9:00am', '10:00am', '11:00am', '12:00pm', '1:00pm', '2:00pm', '3:00pm', '4:00pm', '5:00pm', '6:00pm', '7:00pm', '8:00pm');
// Create the table
for ($y = 0; $y < 13; $y++) {
$theTime = $y + 8;
// The hour of the current loop iteration
echo "<tr>\r\n<td class=\"tws\">" . $times[$y] . "</td>";
for ($x = 0; $x < 5; $x++) {
// Get day of the week for the current loop iteration
$day;
switch ($x) {
case 0:
$day = 'M';
break;
case 1:
$day = 'T';
break;
case 2:
$day = 'W';
break;
function returnCourses($javascriptable)
{
$sql = "SELECT DISTINCT strCourseName AS courseName,\n\t\ttblsection.intSectionID AS secID,\n\t\tCONCAT(tblcourse.strCourseID,'-',intSectionNumber) AS secNumber,\n\t\ttblcourse.strCourseID AS secBareCourse,\n\t\ttblfaculty.strFirstName,\n\t\ttblfaculty.strLastName,\n\t\tstrDayFormat,\n\t\tCONCAT(DATE_FORMAT(timStartTime,'%l:%i%p'),'-',DATE_FORMAT(timEndTime,'%l:%i%p')) AS time,\n\t\tstrFacilityName,\n\t\tstrRoomNumber\n\t\tFROM tblstudentenrollment\n\t\tJOIN tblsection ON tblstudentenrollment.intSectionID = tblsection.intSectionID\n\t\tJOIN tblcourse ON tblsection.strCourseID = tblcourse.strCourseID\n\t\tJOIN tblfaculty ON tblsection.intFacultyID = tblfaculty.intFacultyID\n\t\tJOIN tblsectionschedule ON tblsection.intScheduleID = tblsectionschedule.intDaySlotID\n\t\tJOIN tblsectiontimes ON tblsection.intTimeSlotID = tblsectiontimes.intTimeSlotID\n\t\tJOIN tblroom ON tblsection.intRoomID = tblRoom.intRoomID\n\t\tJOIN tblfacility ON tblroom.intFacilityID = tblFacility.intFacilityID\n JOIN tblstudent ON tblstudentenrollment.intStudentID = tblstudent.intStudentID\n WHERE tblstudent.strStudentEID = ?;";
if ($result = dbGetAll($sql, "s", $_SESSION["cruser"])) {
echo "<thead>";
echo "<tr><td class=\"thr\">Select</td>";
echo "<td class=\"thr\">Course Name</td>";
echo "<td class=\"thr\">Section</td>";
echo "<td class=\"thr\">Schedule</td>";
echo "<td class=\"thr\">Time</td>";
echo "<td class=\"thr\">Instructor</td>";
echo "<td class=\"thr\">Facility</td>";
echo "<td class=\"thr\">Room</td></tr>";
echo "</thead><tbody>";
foreach ($result as $row) {
echo "<tr>";
if ($javascriptable == true) {
echo "<td class=\"advcell\"><input type=\"checkbox\" name=\"check[]\" value=\"" . $row['secID'] . "\" onchange=\"checkchanged('" . $row["secBareCourse"] . "')\" /></td>";
} else {
echo "<td class=\"advcell\"><input type=\"checkbox\" name=\"check[]\" value=\"" . $row['secID'] . "\" /></td>";
}
echo "<td class=\"advcell\">" . $row['courseName'] . "</td>";
echo "<td class=\"advcell\">" . $row['secNumber'] . "</td>";
echo "<td class=\"advcell\">" . $row['strDayFormat'] . "</td>";
echo "<td class=\"advcell\">" . $row['time'] . "</td>";
echo "<td class=\"advcell\">" . $row['strFirstName'] . ' ' . $row['strLastName'] . "</td>";
echo "<td class=\"advcell\">" . $row['strFacilityName'] . "</td>";
echo "<td class=\"advcell\">" . $row['strRoomNumber'] . "</td>";
echo "</tr>";
}
echo "</tbody>";
} else {
echo "<tr><td class=\"advcell\">You are not signed up for any classes.</td></tr>";
}
}
?>
</table>
</div>
<br />
<span class="title">Milestones:</span>
<br />
<div class="shadow-container">
<table class="schedule">
<tr>
<td class="thr">Item</td>
<td class="thr">Issued</td>
<td class="thr">Department</td>
<td class="thr"> </td>
</tr>
<?php
$sql = "SELECT typ.strTypeName AS taskType, tsk.dtmIssued AS dateIssued, tsk.strDepartment AS department, typ.strInstruction AS instruction\n\t\tFROM tblTask tsk\n\t\tJOIN tblTaskType typ ON tsk.intTaskTypeID = typ.intTaskTypeID\n\t\tJOIN tblStudent stu ON stu.strStudentEID = tsk.strEID\n\t\tWHERE stu.strStudentEID = ? AND typ.strTaskClass = 'M'\n\t\tORDER BY taskType";
$result = dbGetAll($sql, "s", $_SESSION['cruser']);
foreach ($result as $row) {
$dateIssued = $row['dateIssued'] ? date_format(date_create($row['dateIssued']), "M/d/Y") : "---";
echo '<tr>';
echo '<td class="advcell"><a href="javascript:openWindow("' . $row['instruction'] . '")">' . $row['taskType'] . '</a></td>';
echo '<td class="advcell">' . $dateIssued . '</td>';
echo '<td class="advcell">' . $row['department'] . '</td>';
echo '<td class="advcell"> </td>';
}
?>
</table>
</div>
</div>
</body>
</html>
function getStudentMinors($eid)
{
$sql = "SELECT strMinor1, strMinor2, strMinor3 FROM tblstudent WHERE strStudentEID = ?";
if ($result = dbGetAll($sql, "s", $eid)) {
return $result;
}
return false;
}
<?php
if (session_status() == PHP_SESSION_NONE) {
session_start();
}
require_once "system.php";
loginHandler();
if ($_SERVER["REQUEST_METHOD"] == "GET") {
$eid = $_SESSION['cruser'];
$sql = "SELECT crs.strCourseName AS courseName,\n\t\t\t crs.intCredits AS courseCredits,\n\t\t\t crs.strCourseID AS courseId,\n\t\t\t sec.intYear AS sectionYear,\n\t\t\t sec.strSeason AS sectionSeason,\n\t\t\t enr.strSectionGrade AS courseGrade\n\t\t\tFROM tblStudentEnrollment enr\n\t\t\tJOIN tblSection sec ON enr.intSectionID = sec.intSectionID\n\t\t\tJOIN tblCourse crs ON sec.strCourseID = crs.strCourseID\n\t\t\tJOIN tblStudent stu ON stu.intStudentID = enr.intStudentID\n\t\t\tWHERE stu.strStudentEID = ?\n\t\t\tORDER BY sectionYear,\n\t\t\t\tCASE WHEN sectionSeason = 'Spring' THEN 0\n\t\t\t\twhen sectionSeason = 'Summer' THEN 1\n\t\t\t\twhen sectionSeason = 'Fall' THEN 2\n\t\t\t\tEND,\n\t\t\t\tcourseID;";
$transcript = dbGetAll($sql, "s", $eid);
if ($transcript) {
$struct = array("transcript" => $transcript);
print json_encode($struct);
} else {
// Error?
}
} else {
echo "Nice try.";
}
