Trenger retting for å sjekke opp mot allerede fått vurderingINF115 Compulsory Exercise 3

Olav Dahl Larsen · 17. mai 2016

INF115 Compulsory Exercise 3

PHP Environment

In this exercise we will connect to and query a database using PHP. In order to setup a PHP

development environment we recommend using XAMPP

( https://www.apachefriends.org/index.html ), you can find a guide explaining how to setup this

enviroment on the mittuib

INF115 webpage under:

INF115 > Pages > Connecting with XAMPP

Dataset

For the questions 410

below use ‘employees’ dataset, available at the following webpage:

https://github.com/datacharmer/test_db

To setup the database:

1. Create a new empty database called “employees” ( http://localhost/phpmyadmin )

2. Download and unzip the repository

3. Open a terminal

4. Navigate to the directory (for me this is CD:O\Downloads\test_dbmaster\)

5. Type: mysql h

localhost u

root employees < employees.sql

The database schema is as follows:

Submission guidelines

Write a seperate PHP/HTML script for each of the questions below. Put each of the scripts into

a folder and submit the compressed (zipped) folder to mitt uib. We will use local version of the

employees database to test your submissions so be careful not to change the names of entities

and attributes within the database.

The deadline for Submissions is: 10:00am on 4th May 2016.

Tasks Use

HTML/PHP to complete the following tasks (for tasks 4 to 10 use the employee

database specified above):

1. (1 point) Write a PHP script to output "Good luck with the last compulsory!" in boldface.

2. (4 points) Produce a HTML form that asks for “name”, “year of birth” and “present age” as

input. The form should have a submit and a reset button. You should also implement a method

to test the values that were entered into the HTML form. The script should check that all of the

fields were completed, and that the values for “year of birth” and “present age” do not contradict

each other. If there are any mistakes in the submission, a form with instructions (hints) on how

to correct the submission should be returned.

3. (2 points) Make a HTML form that asks a user for their preferred language. The form should

have two check boxes corresponding to the options “English” and “Norwegian”. Depending on

the option selected the script should return the HTML form specified in task 2 in either English

or Norwegian (you do not need to reimplement all of the code from task 2, just the HTML form).

4. (1 point) Write a PHP script to connect to the employee database and return a list of the

department names, sorted in alphabetical order.

5. (1 point) Make a script which returns all of the unique job titles from the employee database.

6. (2 points) Use PHP to display a list of the tables in the employee database, with each table

name on a separate line.

7. (3 points) Produce a PHP script that displays a HTML table showing each of the attributes of

each of the tables in the employee database. The database table name should be in the left

hand column and the attribute name in the right hand column.

8. (2 points) Write a script that queries the employees database and returns a list of the number

of employees that were born in each decade.

9. (3 points) Using PHP query the employee database and produce a HTML table that shows

how many employees there are in each department. Each row of the HTML table should contain

the department name, the name of the manager and the employee count.

10. (6 points) Create a HTML form and a PHP script to provide information about the salaries in

the employee database. The HTML form should allow the user to specify a particular year and

to select if they wish to see the total salaries or the average salaries for the chosen year. The

requested information should be returned as a HTML page. Include checks to make sure that

the user does not submit empty or invalid dates, and provide feedback to the user via the HTML

form if there are problems.

Olav Dahl Larsen · 17. mai 2016

<html>
<head>
<title> Oppgave 1 </title>

</head>

<body>
<?php
echo "<b>Good luck with the last compulsory! </b>";
?>
</body>
</head>

</html>

Olav Dahl Larsen · 17. mai 2016

<head>
<title> Oppgave 2 </title>

<?php
            class Oppgave2 {
  var $age;
  var $birthYear;
  var $name;

  function dataInvariant($age, $birthYear, $name){


   if ($age < 0 ){
    $ip = $_SERVER['REMOTE_ADDR'];

     error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
     echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b>";
    return false;
   }
   if ($birthYear < 0 ){
    $ip = $_SERVER['REMOTE_ADDR'];
    error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
     echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    return false;
   }
   if(!strcmp($name, "Full name")){
     echo " <b>Error occured... Please enter your name </b> ";
    return false;
   }

//To mulige aldre for hvert år

   if ((date('Y') - $age) != $birthYear && (date('Y') - $age) != $birthYear+1){
     echo " <b> Error occured. Please reenter your birth year and age </b> ";
    return false;
   }

return true;
}

function __construct($age, $birthYear, $name){

if(!$this->dataInvariant($age, $birthYear, $name)) return false;

   $this->age           = $age;
   $this->birthYear = $birthYear;
   $this->name       = $name;

echo "<b>Registration complete</b>";

   return true;

  }

}
?>
</head>

<body>

<form method="POST" action="oppgave2.php">
<table>
   <tr>
         <td>
                <b> name </b>
         </td>
          <td>
                  <input type = "text" name="name" onclick=" this.value='' " value="Full name" />
         </td>
   </tr>
   <tr>
          <td>
                  <b> year of birth </b>
          </td>

          <td>
<select name= "birthYear">
  <?php
            for($i = 1900; $i < 2017; $i++){
                       print("<option value ='$i'> $i </option>");
               }
  ?>
</select>
          </td>
   </tr>
    <tr>
          <td>
                  <b> present age </b>
          </td>

</select>
</td>
</tr>
</table>

<br>

<?php

if (isset($_POST["name"]) && isset($_POST["birthYear"]) && isset($_POST["age"])){

  $name             = htmlentities(($_POST["name"]);
  $birthYear     = htmlentities(($_POST["birthYear"]);
  $age                = htmlentities(($_POST["age"]);

$oppgave2 = new Oppgave2($age, $birthYear, $name);
}

?>
</body>

Olav Dahl Larsen · 17. mai 2016

<head>
<title> Oppgave 3 </title>

<?php

           if( !isset($_COOKIE["Language"]))   $language = 'English';
           else                                                                 $language = $_COOKIE["Language"];

            class Oppgave3 {
  var $age;
  var $birthYear;
  var $name;

  function dataInvariant($age, $birthYear, $name, $language){


   if ($age < 0 || $age > 101 || !is_numeric($age) ){
    $ip = $_SERVER['REMOTE_ADDR'];

     error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
     if($language ==1) echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    else                 echo " <b>HTML tampering detektert av IP address $ip. Hendelsen er rappotert \n </b> ";
    return false;
   }
   if ($birthYear < 0 || $birthYear > 2017 || !is_numeric($birthYear) ){
    $ip = $_SERVER['REMOTE_ADDR'];
    error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
    if($language ==1) echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    else                 echo " <b>HTML tampering detektert av IP address $ip. Hendelsen er rappotert \n </b> ";
    return false;
   }
   if(!strcmp($name, "Full name") || !strcmp($name, "Fult navn") ){
    if($language ==1)   echo " <b>Error occured... Please enter your name </b> ";
    else                    echo " <b>Feil oppstod... Venligst skriv inn ditt navn </b> ";
    return false;
   }

//To mulige aldre for hvert år

   if ((date('Y') - $age) != $birthYear && (date('Y') - $age) != $birthYear+1){
    if($language ==1)    echo " <b> Error occured. Please reenter your birth year and age </b> ";
    else                                echo "<b>" . utf8_encode("Feil oppstod. Venligst skriv inn på nytt ditt fødsels år og alder") . "</b>";
    return false;
   }

return true;
}

function __construct($age, $birthYear, $name, $language){

if(!$this->dataInvariant($age, $birthYear, $name,$language)) return;

   $this->age           = $age;
   $this->birthYear = $birthYear;
   $this->name       = $name;

    if($language ==1) echo "<b>Registration complete</b>";
   else                               echo "<b>Registrering velykket </b>";

  }

}

?>

</head>

<body>

<table>

<tr>
<td> English </td>
<td> <input type="checkbox" onclick=" document.cookie = 'Language=1'; this.form.submit() " / > </td>

<td> Norwegian </td>
<td> <input type="checkbox" onclick="document.cookie = 'Language=0'; this.form.submit()" /> </td>

</tr>

</table>
</form>

<form method="POST" action="oppgave3.php">
<hr>
<br>
<table>
   <tr>
         <td>
   <?php
                if($language ==1) echo("<b> Name </b>");
                else             echo("<b> Navn </b>");
?>
         </td>
          <td>
                 <?php
                  if($language ==1) echo("<input type = 'text' id = 'name' name='name' onclick=' resetName()'   value='Full name' /> ");
                  else               echo("<input type = 'text'id = 'name' name='name' onclick=' resetName() ' value='Fult navn' /> ");
                 ?>
         </td>
   </tr>
   <tr>
          <td>
<?php
                            if($language ==1) echo("<b> year of birth </b>");
                               else                          echo "<b>" . utf8_encode("Fødsels år") . "</b>";
?>
          </td>

          <td>

<select name= "birthYear">

<?php
            for($i = 1900; $i < 2017; $i++){
                        print("<option value ='$i'> $i </option>");
                                 }
?>
</select>
          </td>
   </tr>
    <tr>
          <td>
<?php
                  if($language ==1) echo("<b> present age </b> ");
                     else                             echo("<b>alder </b> ");
?>
          </td>

</select>
</td>
</tr>
</table>

<br>

<?php

if (isset($_POST["name"]) && isset($_POST["birthYear"]) && isset($_POST["age"])){

  $name             = htmlentities($_POST["name"]);
  $birthYear     = htmlentities($_POST["birthYear"]);
  $age                = htmlentities($_POST["age"]);

  $oppgave3 = new Oppgave3($age, $birthYear, $name,$language);
   }

?>
</body>

Olav Dahl Larsen · 17. mai 2016

     tr:nth-child(even) {
background-color: lightgrey;
     }
    th {   background-color: darkgrey;
              font-weight: bold;
    }
tr:hover {
    background-color: lightyellow;
    cursor: pointer;

}
</style>
</head>
<body>
<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);

$task = "SELECT dept_name FROM departments ORDER BY dept_name";

$result = mysqli_query($conn, $task);

echo("<table>");
echo("<th>");
echo("Departments");
echo("<th>");
while($row = mysqli_fetch_assoc($result)){
echo("<tr>");
echo("<td>");

echo $row["dept_name"];
echo("</td>");
echo("</tr>");
}

echo("</table>");

mysqli_free_result($result);
mysqli_close($conn);
?>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

     tr:nth-child(even) {
background-color: lightgrey;
     }
    th {   background-color: darkgrey;
              font-weight: bold;
    }
tr:hover {
    background-color: lightyellow;
    cursor: pointer;

}
</style>
</head>
<body>
<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);

$task = "SELECT DISTINCT title FROM titles";

$result = mysqli_query($conn, $task);

echo("<table>");

echo("<th>");
echo("Titles");
echo("</th>");

while($row = mysqli_fetch_assoc($result)){
echo("<tr>");
echo("<td>");

echo($row["title"]);
echo("<br>");

echo("</td>");
echo("</tr>");

}
echo("</table>");

mysqli_free_result($result);
mysqli_close($conn);

?>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

</head>
<body>
<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);

$task = "SHOW TABLES";

$result = mysqli_query($conn, $task);

echo "<table>";
echo "<th>";
echo "Table_names";
echo "</th>";
while($row = mysqli_fetch_assoc($result)){
echo("<tr>");
echo("<td>");
echo $row["Tables_in_employees"];
echo("</td>");
echo("</tr>");

}
echo "</table>";

mysqli_free_result($result);
mysqli_close($conn);
?>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

<html>
<head>
<style >
     tr:nth-child(even) {
background-color: lightgrey;
     }
    th {   background-color: darkgrey;
              font-weight: bold;
    }
   tr:hover {
    background-color: lightyellow;
    cursor: pointer;
}

</style>
</head>
<body>
<table>
<th> Table name </th>
<th> Attribute </th>

<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);
$task = "SHOW TABLES";
$tables = mysqli_query($conn, $task);

while($row = mysqli_fetch_assoc($tables)){
$current_table = $row['Tables_in_employees'];

$task = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='$current_table' ";
$result = mysqli_query($conn, $task);
while($value = mysqli_fetch_assoc($result)){
  echo('<tr>');
  echo('<td>');
   echo $row["Tables_in_employees"];
  echo('</td>');
  echo('<td>');
   echo $value["column_name"];
  echo('</td>');
  echo('</tr>');
}
mysqli_free_result($result);
}

mysqli_free_result($tables);
mysqli_close($conn);

?>

</table>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

     tr:nth-child(even) {
background-color: lightgrey;
     }
    th {   background-color: darkgrey;
              font-weight: bold;
    }
   tr:hover {
    background-color: lightyellow;
    cursor: pointer;
}
</style>

</head>
<body>
<table>
<th> Decade </th>
<th> Employees_born_in_this_decade </th>
<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);

$task = "SELECT COUNT(*) AS numbDec, FLOOR(YEAR(birth_date)/10) AS decade FROM employees GROUP BY decade";

$result = mysqli_query($conn, $task);

while($row = mysqli_fetch_assoc($result)){
echo("<tr>");
echo("<td>");
echo $row["decade"] . '0\'s';
echo("</td>");

echo("<td>");
echo $row["numbDec"];
echo("</td>");

echo("</tr>");

}
mysqli_free_result($result);
mysqli_close($conn);
?>
</table>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

</head>
<body>
<table >
<th> Department </th>
<th> Name_of_manager </th>
<th> Number_of_employees </th>
<?php

$server = "127.0.0.1";
$user    = "root";
$pass   = "123";
$db      = "employees";

$conn = mysqli_connect($server,$user,$pass,$db);

$task = "SELECT first_name, last_name, dept_name, dept_no FROM employees NATURAL JOIN dept_manager NATURAL JOIN departments";

$result = mysqli_query($conn, $task);

while($row = mysqli_fetch_assoc($result)){

echo("<tr>");
echo("<td>");
echo $row["dept_name"];
echo("</td>");

echo("<td>");
echo $row["first_name"] . " " . $row["last_name"];
echo("</td>");

$dept_no = $row["dept_no"];

$task         = "SELECT COUNT(*) AS count FROM dept_emp WHERE dept_no='$dept_no' " ;
$count      = mysqli_fetch_assoc(mysqli_query($conn, $task));

echo("<td>");
  echo $count["count"];
echo("</td>");
echo("</tr>");

}
mysqli_free_result($result);
mysqli_close($conn);

?>
</table>
</body>

</html>

Olav Dahl Larsen · 17. mai 2016

<head>
<title> Oppgave 2 </title>

<?php

class Oppgave10 {

var $year;
var $salLogic;

  var $server = "127.0.0.1";
  var $user    = "root";
  var $pass   = "123";
  var $db      = "employees";

  var $conn;

  function getTotal(){
   $task = "SELECT SUM(salary) AS sum FROM salaries WHERE $this->year BETWEEN YEAR(from_date) AND YEAR(to_date)";

   $result = mysqli_query($this->conn, $task);

   echo "<b> In year $this->year the total sum of salaries of all the employees where/is: </b>";
   $row = mysqli_fetch_assoc($result);
   echo $row["sum"];

   mysqli_free_result($result);

  }
  function dataInvariant($year, $salLogic){

   if ($year< 0 || $year > 2017 ){
    $ip = $_SERVER['REMOTE_ADDR'];
    error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
    echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    return false;
   }

   if (!($salLogic==0) && !($salLogic==1)){
    $ip = $_SERVER['REMOTE_ADDR'];
    error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
    echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    return false;
   }
   if(!is_numeric($year)){
    $ip = $_SERVER['REMOTE_ADDR'];
    error_log("html tampering- attacker $ip", 3, "C:\wamp64\logs\hackLog.log");
    echo " <b>HTML tampering detected by IP address $ip. The incident has been reported \n </b> ";
    return false;
   }

return true;

}

function __construct($year, $salLogic){

if(!$this->dataInvariant($year, $salLogic)) throw new Exception("dataInvariant fail");

$this->year = $year;
$this->salLogic = $salLogic;

}

?>

</head>

<body>

<table>
<tr>
<td> <b> Total salary </b> </td>
<td> <input type="radio" name="salLogic" value="0" checked / > </td>

<td><b> Average salary </b> </td>
<td> <input type="radio" name="salLogic" value="1" / > </td>
</tr>
</table>
<hr>

<td>

<br>

<?php

if (isset($_GET["year"] ) && isset($_GET["salLogic"] )) {

  $year          =   htmlentities( $_GET["year"]);
  $salLogic =   htmlentities($_GET["salLogic"]);

  try{
   $oppgave10 = new Oppgave10($year, $salLogic);
   $oppgave10->createConnection();

if($salLogic == 1) $oppgave10->getAverage();
else $oppgave10->getTotal();

$oppgave10->destroyConnection();

  }
  catch(Exception $ex){
   die();
  }


   }

?>
</body>

Olav Dahl Larsen · 17. mai 2016

Alle oppgavene er gitt fra 1-10 i slik rekkefølge

Logg inn

Trenger retting for å sjekke opp mot allerede fått vurderingINF115 Compulsory Exercise 3

Anbefalte innlegg

Olav Dahl Larsen

Lenke til kommentar

Videoannonse

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Olav Dahl Larsen

Lenke til kommentar

Opprett en konto eller logg inn for å kommentere

Opprett konto

Logg inn

Hvem er aktive 0 medlemmer