Inserting Records - Exercise

Contact Us or call 1-877-932-8228
Inserting Records - Exercise

Inserting Records

Duration: 30 to 40 minutes.

In this exercise, you will work on the InsertEmployee.php script to insert new employees.

  1. Open ManagingData/Exercises/Includes/InsertEmployee.php for editing.
  2. Add code at the top to populate $dbEntries with SQL-safe entries from the form.
  3. Make the connection to the database.
  4. The SQL insert query has been started for you. Finish it.
  5. Write code to execute the query.
    • If the query succeeds, return "Employee Added" and a link to EmployeeReport.php. You will also need to set $showForm to false to prevent the form from reappearing.
    • If the query fails, return "Insert failed".

Code Sample:

ManagingData/Exercises/Includes/InsertEmployee.php
<?php
	//Add code to populate $dbEntries with
	//SQL-safe entries from the form.

	//Connect to the database
	
	//Finish the query below
	$query = "INSERT INTO Employees
		(FirstName, LastName, Title, 
			TitleOfCourtesy, Email, BirthDate, HireDate,
			Address, City, Region, PostalCode, Country,
			HomePhone, Extension, Notes, ReportsTo, Password)...
			
	
	//Execute the query.  
	//If the query succeeds, return 'Employee Added'
	//	and provide a link to EmployeeReport.php
	//	and set $showForm to false
	//If it fails, return 'Insert failed'
?>

Challenge

Instead of hiding the form after an employee is inserted. Provide a new empty form, so that the user can enter another employee.

Solution:

ManagingData/Solutions/Includes/InsertEmployee.php
<?php
	$dbEntries = $_POST;
	foreach ($dbEntries as &$entry)
	{
		$entry = dbString($entry);
	}

	@$dB = new mysqli('localhost','root','pwdpwd','Northwind');
	if (mysqli_connect_errno())
	{
		echo 'Cannot connect to database: ' . mysqli_connect_error();
	}
	$query = "INSERT INTO Employees
		(FirstName, LastName, Title,
			TitleOfCourtesy, Email, BirthDate, HireDate,
			Address, City, Region, PostalCode, Country,
			HomePhone, Extension, Notes, ReportsTo, Password)
		VALUES ('" .	$dbEntries['FirstName'] . "','" .
						$dbEntries['LastName'] . "','" .
						$dbEntries['Title'] . "','" .
						$dbEntries['TitleOfCourtesy'] . "','" .
						$dbEntries['Email'] . "','" .
						$dbEntries['BirthYear'] . "-" .
					 		$dbEntries['BirthMonth'] . "-" .
					 		$dbEntries['BirthDay'] . "','" .
						$dbEntries['HireYear'] . "-" .
					 		$dbEntries['HireMonth'] . "-" .
					 		$dbEntries['HireDay'] . "','" .
						$dbEntries['Address'] . "','" .
						$dbEntries['City'] . "','" .
						$dbEntries['Region'] . "','" .
						$dbEntries['PostalCode'] . "','" .
						$dbEntries['Country'] . "','" .
						$dbEntries['HomePhone'] . "','" .
						$dbEntries['Extension'] . "','" .
						$dbEntries['Notes'] . "'," .
						$dbEntries['ReportsTo'] . ",'" .
						$dbEntries['Password'] . "')";

	if ($dB->query($query))
	{
		echo '<div align="center">Employee Added</div>
			<a href="EmployeeReport.php">Employee Report</a>';
		$showForm = false;
	}
	else
	{
		echo '<div align="center">Insert failed</div>';
	}
?>

Solution:

ManagingData/Solutions/Includes/InsertEmployee-challenge.php
<?php
---- C O D E   O M I T T E D ----
	if ($dB->query($query))
	{
		echo '<div align="center">Employee Added</div>
			<a href="EmployeeReport.php">Employee Report</a>';
		foreach ($dbEntries as &$entry)
		{
			$entry = ';
		}
	}
	else
	{
		echo '<div align="center">Insert failed</div>';
	}
?>
Next