<?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">
</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?>index.php?source" target="labs">Source code</a>

</h2>   


<h4>Selecting Database Information</h4>
    <h5 class="ib">Part I.</h5><p class="ib">Displaying Information from Multiple Tables</p>
    <p class="nudge in">PHP page that will display some of the information in the northwoods database.</p>
    
    <!--brown border hover-->
        <style>
    table {color: var(--midtone); font-size: smaller; /* position: absolute; */ z-index: 2; max-width: 700px; /* margin: 0 0 0 -64px; */ 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: 0 0 0 -24px;}}
        
        pre {position: fixed; top:0; background: var(--light); z-index: 5}
    </style>
    
    <style>input, .space.button:hover, .space.button:active, #selected {width: 3.5em; 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-left: auto; margin-right: auto;}
        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;}
    </style>
 
<!--<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>");

$query = "select s_first, s_mi, s_last, f_first, f_mi, f_last, f_phone from student, faculty where student.f_id = faculty.f_id order by f_last";
   
$result = $DB->query($query);   

   if ($result->num_rows > 0)   # if there are rows of data
        {
        # output search query into a table
        echo "<table class=\"pad-top\"> <tr> <th>Student</th> <th>Faculty Advisor</th> <th>Advisor Phone Number</th> </tr>";
        while($value = $result->fetch_assoc()) 
            {
            # format the phone numbers + hairspace
            $phone = preg_replace("/^([\d]{3})([\d]{3})([\d]{4})$/", "($1)&#8202;$2-$3", $value["f_phone"]);
            
            echo "<tr> <td>".$value["s_first"]." ".$value["s_mi"]." ".$value["s_last"]."</td><td>".$value["f_first"]." ".$value["f_mi"]." ".$value["f_last"]."</td><td>".$phone."</td> </tr>";
            }
        echo "</table>";
        } 
   else 
        {
        echo "0 results";
        }
   
$DB->close();
   
  
?>
    
<!--   </pre>     -->

<h5 class="ib pad-top">Part II.</h5><p class="ib"><a href="second.php">Link to Part II here</a></p>
   


    
    <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>