Drop Down List Using PHP Assignment

The functionality for adding a department is very problematic. If you review the DDL that was used to create the Company database, the social security number that you enter for the manager of the department has to match a social security number in the Employee table. The way the application is currently written, we have no way to enforce that constraint. The user may enter any ssn, and if it violated the foreign key constraint, it will generate an exception.

-->This could be avoided (and the form made much easier to use), if we were to incorporate a drop down list for the ssn, and populate that list with the ssn's from the Employee table.

-->Your job is to take the application that we worked on in class, and change form.html.php so that the use can select the ssn from a drop-down list, which is populated with the results of the Employee ssn's. The rest of the application should remain the same; however, you should incorporate any changes that you must make in order to get the drop-down list to work.

form.html.php

Department Name:

Department Number:

Department Manager's SSN:

index.php

if (isset($_GET['addDepartment']))

{

include 'form.html.php';

exit();

}

try

{

$pdo = new PDO('mysql:host=localhost;dbname=company', 'root', 'Mm05698698');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('SET NAMES "utf8"');

}

catch (PDOException $e)

{

$error = 'Unable to connect to the database server.';

include 'error.html.php';

exit();

}

if (isset($_POST['dname']))

{

try

{

$sql = 'INSERT INTO department SET

dname = :dname,

dnumber = :dnum,

mgr_ssn = :mgr_ssn,

mgr_start=CURDATE()';

$s = $pdo->prepare($sql);

$s->bindValue(':dname', $_POST['dname']);

$s->bindValue(':dnum', $_POST['dnum']);

$s->bindValue(':mgr_ssn', $_POST['mgr_ssn']);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding submitted department: ' . $e->getMessage();

include 'error.html.php';

exit();

}

header('Location: .');

exit();

}

try

{

$sql = 'SELECT dname FROM department';

$result = $pdo->query($sql);

}

catch (PDOException $e)

{

$error = 'Error fetching departments: ' . $e->getMessage();

include 'error.html.php';

exit();

}

while ($row = $result->fetch())

{

$dnames[] = $row['dname'];

}

include 'departments.html.php';

department.php

Add a department

Here are all the departments in the database: