<?php
  if (isset($_GET['source'])) {
        highlight_file($_SERVER['SCRIPT_FILENAME']);
        exit;
  }
?>
<html>
<head>
<title>Selecting Database Information</title>
    
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <meta name="theme-color" content="#B5C7DD" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <link rel="stylesheet" href="../css/reset.css">
        <link rel="stylesheet" href="../css/mandy.css">
        <link rel="stylesheet" href="../css/form.css">

<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon">
    
    
    <!--brown border hover-->
        <style>
    table {color: var(--midtone); font-size: smaller;  position: absolute;  z-index: 2; max-width: 700px;  margin: 0 0 0 0;  background: rgba(253, 252, 246, 0.88)}
    tr {border-bottom: 1px dashed rgba(137, 112, 82, 0); border-top: 1px dashed rgba(137, 112, 82, 0);}    
    tbody tr:hover 
        {
/*            background-color: rgba(158, 187, 221, 0.15);*/
            color: var(--dark); border-bottom: 1px solid rgba(137, 112, 82, 0.6); border-top: 1px solid rgba(137, 112, 82, 0.6); position: relative; z-index: 3}
            
/*        modified for this page*/
    tr:nth-child(odd), #selected, th {background-color: rgba(153, 149, 144, 0.07)}
/*        body, .stretch {padding:  0;}*/
    
    table a {color: #9bb9de; text-transform: lowercase}
    @media only screen and (max-width: 700px) {html {background-color: var(--light); background-image:none} table {margin: inherit 0 0 -24px;}}
        
        pre {position: fixed; top:0; background: var(--light); z-index: 5}
    </style>
    
    <style>input, .space.button, .space.button:hover, .space.button:active, #selected {width: 8em; opacity: 1; border-color: rgba(153, 149, 144, 0.8)} .space.button {border-color: rgba(153, 149, 144, 0.38); opacity: .8} #selected {color: var(--dark)}
        .stretch {position:fixed; bottom: 0; padding: 340px }
        
/*     specific to this page   */
        .ib {display:inline-block}
        table {margin: 1em auto auto -16.5px;}
        th {font-family: var(--display); font-weight: 400; font-size: larger;}
        th:last-of-type {padding-right: .5em;}
        td {padding-left: .7em; padding-right: .7em;}
        form {display: flex; flex-wrap: wrap; justify-content: space-evenly}
    </style>
    
</head>
<body>

<h2 class="pad-top">    
    
<?php   # need this to be able to view source in the below anchor tag
  $urlPath=dirname($_SERVER['SCRIPT_URL']).'/'; 
  $labsPath=$urlPath.'labs/'; 
?>     

    <a href="../index.php">CS 130A</a> → Lab 12
<a href="<?=$urlPath?>second.php?source" target="labs">Source code</a>

</h2>   


<h4>Selecting Database Information</h4>
    <h5 class="ib">Part II.</h5><p class="ib">Displaying Schedule Information</p>
    <p class="nudge in">PHP page that will display some of the information in the northwoods database.</p>
<?php
$pick = "";   # setting to null to avoid error on page load    
?>    

<!--    if failed at generating this exac via mysql found much lower down, use this -->
<!--
<form action='second.php' method='post'> Pick a term:
    <input type='submit' class='space button' name='pickTerm' value="Fall 2005">
    <input type='submit' class='space button' name='pickTerm' value="Spring 2006">
    <input type='submit' class='space button' name='pickTerm' value="Summer 2006">
    <input type='submit' class='space button' name='pickTerm' value="Fall 2006">
    <input type='submit' class='space button' name='pickTerm' value="Spring 2007">
    <input type='submit' class='space button' name='pickTerm' value="Summer 2007">
</form>  
-->
    
<!--<pre>-->
<?php    
//  include_once('/users/kfreedma/secure/dbvars.inc'); // file which has database user/password
# couldn't figure out how to use an .inc file so just listed the login directly

$dbhost = 'twinpeaks.ccsf.edu';
$dbuser = 'mchen75';
$dbpass = 'jan2992.mc';
$database = 'northwoods';    
    
  $DB = new mysqli($dbhost, $dbuser, $dbpass, $database);
  unset($dbhost, $dbuser, $dbpass, $database); // erase these variables so they wont show up in print_r(get_all_vars()) later on.

  if (!$DB) die("<p>Cannot open database</p>");


   

  
   # get the list of terms from the database to turn them into buttons
$dbTerms = $DB->query("select term_desc from term");
  
//<input type='submit' class='space button' name='pickTerm' value="">   
    
        if ($dbTerms->num_rows > 0)   # if there are rows of data
            {
            
            if (!empty($_POST["pickTerm"]))  
                {
                $pick = $_POST["pickTerm"];
                }
            # output found terms into a set of submit buttons
            echo "<h4>Pick a term:</h4> <form action='second.php' method='post'>"; 
            $i = 0; # incrementor
            while($term = $dbTerms->fetch_assoc()) 
                foreach ($term as $v)
                {
                echo "<input type=\"submit\" class=\"space button\" name=\"pickTerm\" value=\"$v\"";   # only double quotes escape here in this echo statement, single quotes aren't escaped
                if ($v==$pick) print " id='selected'"; 
                echo ">";
                $i++;
                }
            echo "</form>";
            } 
        else 
            {
        echo "There are no course listings for this term";
            }    

   # decided to just generate the buttons manually - it's faster for just those 6     

if (empty($_POST["pickTerm"]))       # error msg if empty form is sent
{
echo "<p>You didn't pick a term.</p>";
}
        
else   
{
$pick = $_POST["pickTerm"];  
   
   # complicated query took maybe 20 tries to get right
$query = "select term_desc, course_name, sec_num, f_last, bldg_code, room, c_sec_day, c_sec_time
from course_section, courses, term, location, faculty
where
      course_section.term_id = term.term_id
and   term.term_desc = '$pick'
and   course_section.f_id = faculty.f_id
and   course_section.loc_id = location.loc_id
and   course_section.course_id = courses.course_id
order by course_name;";
   
$result = $DB->query($query);   
   
if ($_SERVER["REQUEST_METHOD"] == "POST") 
    {

        
        if ($result->num_rows > 0)   # if there are rows of data
            {
            # output search query into a table
            echo "<table class=\"pad-top\"> <tr> <th>Course</th> <th>Section</th> <th>Instructor</th> <th>Location</th> <th>Days</th> <th>Start Time</th> </tr>";
            while($value = $result->fetch_assoc()) 
                {
                echo "<tr> <td>".$value['course_name']."</td> <td>".$value['sec_num']."</td> <td>".$value['f_last']."</td> <td>".$value['bldg_code']." ".$value['room']."</td> <td>".$value['c_sec_day']."</td> <td>".$value['c_sec_time']."</td> </tr>";
                }
            echo "</table>";
            } 
        else 
            {
        echo "<p>There are no course listings for this term</p>";
            }
        
    
    }
   
   
   
}   
   
$DB->close();
   
  
?>
    
<!--   </pre>     -->
<div style="position: absolute; bottom: 1.25em">
<h5 class="ib pad-top">Part I.</h5><p class="ib"><a href="index.php">Link back to Part I here</a></p>
</div>  


    
    <p class="hide">
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
</p>

    <div class="stretch">&nbsp;</div>
</body>
</html>