Creating a Customer Report - Exercise

Contact Us or call 1-877-932-8228
Creating a Customer Report - Exercise

Creating a Customer Report

Duration: 20 to 30 minutes.

In this exercise, you will create a sales report using MDB2.

  1. Open MDB2/Exercises/SalesReport.php for editing.
  2. Write code to:
    • Include the MDB2 package.
    • Connect to the Northwind database.
    • If the connection fails, return an error message to the browser.
    • If the connection succeeds run a query that gets the order date and the first and last name of the associated employee and the customer company for all orders. Order by OrderDate.
    • Output the results of the query in a table.
    • Free the result and disconnect from the database.

Code Sample:

MDB2/Exercises/SalesReport.php
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Sales Report</title>
</head>
<body>
<?php
//Include MDB2 Package

//Connect to the Northwind database.
//	If the connection fails, return an error message to the browser.
//	If the connectoin succeeds run a query that gets the order date 
//		and the first and last name of the associated employee 
//		and the customer company for all orders.  Order by OrderDate.
?>
	<table border="1">
	<tr>
		<th>#</th>
		<th>Salesperson</th>
		<th>Customer</th>
		<th>Order Date</th>
	</tr>
<?php
	//Create rows for each record returned from the query.
?>
	</table>
<?php
	//Free the result and disconnect from the database.
}
?>
</body>
</html>

Challenge

830 rows are returned. This is too many to display on a single page. MySQL has a LIMIT clause that specifies which and how many records to return from the query. For example, the query below would return 10 orders starting with the 100th order (note that the first row is row 0).

SELECT OrderDate, OrderID
FROM Orders
LIMIT 99,10;

Add code to your solution that allows the user to tab through the results 10 at a time with Previous and Next buttons.

Solution:

MDB2/Solutions/SalesReport.php
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Sales Report</title>
</head>
<body>
<?php
require_once 'MDB2.php';

$db = MDB2::connect('mysqli://root:pwdpwd@localhost/Northwind');
if (PEAR::isError($db))
{
	echo 'Cannot connect to database: ' . $db->getMessage();
}
else
{
	$query = "SELECT e.FirstName, e.LastName, c.CompanyName,
				DATE_FORMAT(o.OrderDate, '%M %e, %Y') AS OrderDate
			FROM Employees e
				JOIN Orders o ON (e.EmployeeID = o.EmployeeID)
				JOIN Customers c ON (c.CustomerID = o.CustomerID)
				ORDER BY o.OrderDate";
	$result = $db->query($query);
	$numResults = $result->numRows();

	echo "<b>Showing $numResults Orders</b>";
?>

	<table border="1">
	<tr>
		<th>#</th>
		<th>Salesperson</th>
		<th>Customer</th>
		<th>Order Date</th>
	</tr>
<?php
	$count=1;
	while ($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))
	{
		echo '<tr>';
		echo "<td>$count</td>";
		echo '<td>' . $row['firstname'] . ' ' .
				$row['lastname'] . '</td>';
		echo '<td>' . $row['companyname'] . '</td>';
		echo '<td>' . $row['orderdate'] . '</td>';
		echo '</tr>';
		$count++;
	}
?>
	</table>

<?php
	$result->free();


	$db->disconnect();
}
?>
</body>
</html>

Solution:

MDB2/Solutions/SalesReport-challenge.php
<?php 
	if (array_key_exists('LastStart',$_GET))
	{
		if (array_key_exists('Next',$_GET))
		{
			$currentRow = $_GET['LastStart'] + 10;
		}
		elseif (array_key_exists('Prev',$_GET))
		{
			$currentRow = $_GET['LastStart'] - 10;
		}
		if ($currentRow < 0)
		{
			$currentRow=0;
		}
	}
	else
	{
		$currentRow = 0;
	}
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="UTF-8">
<title>Sales Report</title>
</head>
<body>
<?php
require_once 'MDB2.php';

$db = MDB2::connect('mysqli://root:pwdpwd@localhost/Northwind');
if (PEAR::isError($db))
{
	echo 'Cannot connect to database: ' . $db->getMessage();
}
else
{
	$query = "SELECT e.FirstName, e.LastName, c.CompanyName,
				DATE_FORMAT(o.OrderDate, '%M %e, %Y') AS OrderDate
			FROM Employees e
				JOIN Orders o ON (e.EmployeeID = o.EmployeeID)
				JOIN Customers c ON (c.CustomerID = o.CustomerID)
				ORDER BY o.OrderDate
				LIMIT $currentRow,10";
	$result = $db->query($query);
	$numResults = $result->numRows();

	echo "<b>$numResults Orders Showing</b>";
?>

	<table border="1">
	<tr>
		<th>#</th>
		<th>Salesperson</th>
		<th>Customer</th>
		<th>Order Date</th>
	</tr>
<?php
	$count=$currentRow+1;
	while ($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))
	{
		echo '<tr>';
		echo "<td>$count</td>";
		echo '<td>' . $row['firstname'] . ' ' .
				$row['lastname'] . '</td>';
		echo '<td>' . $row['companyname'] . '</td>';
		echo '<td>' . $row['orderdate'] . '</td>';
		echo '</tr>';
		$count++;
	}
?>
	<tr>
		<td colspan="4">
			<form method="get" action="SalesReport-challenge.php">
				<input type="hidden" name="LastStart" value="<?php echo $currentRow ?>">
				<input type="submit" name="Prev" value="Previous 10"
					<?php if ($currentRow < 1) echo "disabled"; ?>>
				<input type="submit" name="Next" value="Next 10"
					<?php if ($numResults < 10) echo "disabled"; ?>>
			</form>
		</td>
	</tr>
	</table>

<?php
	$result->free();
	$db->disconnect();
}
?>
</body>
</html>
Next