Example Code

Database

test_db.sql

-- phpMyAdmin SQL Dump

-- version 2.11.7.1

-- http://www.phpmyadmin.net

-- Host: localhost

-- Generation Time: Jan 13, 2010 at 11:23 PM

-- Server version: 5.0.41

-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

-- Database: `testdrive_db`

CREATE DATABASE `testdrive_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

USE `testdrive_db`;

-- --------------------------------------------------------

-- Table structure for table `departments`

CREATE TABLE `departments` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(50) default NULL,

`manager` varchar(100) default NULL,

`costcenter` int(11) default NULL,

`businessunit` varchar(50) default NULL,

`hrrep` varchar(100) default NULL,

`locationstreet` varchar(50) default NULL,

`locationcity` varchar(20) default NULL,

`locationstate` char(2) default NULL,

`locationzipcode` varchar(10) default NULL,

`budget` int default NULL,

`actualexpenses` int default NULL,

`estsalary` int default NULL,

`actualsalary` int default NULL,

`esttravel` int default NULL,

`actualtravel` int default NULL,

`estsupplies` int default NULL,

`actualsupplies` int default NULL,

`estcontractors` int default NULL,

`actualcontractors` int default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- Dumping data for table `departments`

INSERT INTO `departments` VALUES(1, 'User Experience', 'Elizabeth Roe', 11234, 'Core Services', 'Jane Doe', '601 Townsend St.', 'San Francisco', 'CA', '94103',395000,412000,375000,375000,10000,15000,10000,12000,0,10000);

INSERT INTO `departments` VALUES(2, 'Engineering', 'John Doe', 34523, 'Research and Development', 'Jane Doe', '345 Park Ave', 'San Jose', 'CA', '95110',434000,436000,410000,415000,12000,10000,12000,11000,0,0);

INSERT INTO `departments` VALUES(3, 'New Product Development', 'Albert Jones', 11456, 'Research and Development', 'Jane Doe', '345 Park Ave', 'San Jose', 'CA', '95110',1625000,1823000,500000,500000,25000,23000,1100000,1300000,0,0);

INSERT INTO `departments` VALUES(4, 'Corporate', 'Bruce Andrews', 11111, 'None', 'Jane Doe', '345 Park Ave', 'San Jose', 'CA', '95110',660000,705000,500000,500000,100000,120000,20000,30000,40000,55000);

INSERT INTO `departments` VALUES(5, 'Field Research', 'Jane Smith', 66555, 'Research and Development', 'Jane Doe', '345 Park Ave', 'San Jose', 'CA', '95110',440000,444000,410000,410000,15000,17000,15000,17000,0,0);

INSERT INTO `departments` VALUES(6, 'Food Services', 'Terry Eastman', 85225, 'Core Services', 'Jane Doe', '345 Park Ave', 'San Jose', 'CA', '95110',115000,113000,50000,40000,0,0,50000,48000,15000,25000);

INSERT INTO `departments` VALUES(7, 'Product Marketing', 'Samantha Smith', 55301, 'Corporate Marketing', 'Jane Doe', '601 Townsend St.', 'San Francisco', 'CA', '94103',445000,484000,375000,400000,30000,32000,20000,22000,20000,30000);

-- --------------------------------------------------------

-- Table structure for table `employees`

CREATE TABLE `employees` (

`id` int(11) NOT NULL auto_increment,

`firstname` varchar(50) default NULL,

`lastname` varchar(50) default NULL,

`title` varchar(50) default NULL,

`departmentid` int(11) default NULL,

`officephone` varchar(20) default NULL,

`cellphone` varchar(20) default NULL,

`email` varchar(50) default NULL,

`street` varchar(50) default NULL,

`city` varchar(20) default NULL,

`state` char(2) default NULL,

`zipcode` varchar(10) default NULL,

`office` varchar(20) default NULL,

`photofile` varchar(50) default NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

-- Dumping data for table `employees`

INSERT INTO `employees` VALUES(1, 'Bob', 'Smith', 'User Research Specialist', 1, '(415)555-1111', '(415)444-2222', 'bsmith@adobe.com', '601 Townsend St', 'San Francisco', 'CA', '94103', 'Cube 1234', 'bsmith.jpg');

INSERT INTO `employees` VALUES(2, 'John', 'Doe', 'Senior Computer Scientist', 2, '(408)555-2222', '(408)444-1111', 'jdoe@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'E12-123', 'jdoe.jpg');

INSERT INTO `employees` VALUES(3, 'Elizabeth', 'Roe', 'Senior Experience Designer', 1, '(408)324-6432', '(408)234-3453', 'eroe@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'E11-123', 'eroe.jpg');

INSERT INTO `employees` VALUES(4, 'Jane', 'Riley', 'Quality Engineer', 2, '(415)123-1234', '(415)123-4567', 'jriley@adobe.com', '601 Townsend St', 'San Francisco', 'CA', '94103', 'Cube 4567', 'jriley.jpg');

INSERT INTO `employees` VALUES(5, 'Joe', 'Johnson', 'Computer Scientist', 3, 'None', '(415)324-9870', 'jjohnson@adobe.com', 'Unseated', 'San Francisco', 'CA', '94103', 'Unseated', 'jjohnson.jpg');

INSERT INTO `employees` VALUES(6, 'Bruce', 'Andrews', 'Chief Executive Officer', 4, '(408)234-1253', '(408)324-3464', 'bandrews@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'W18-502', 'bandrews.jpg');

INSERT INTO `employees` VALUES(7, 'David', 'Jackson', 'Chief Technology Officer', 5, '(408)321-3332', '(408)893-9808', 'djackson@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'W6-211', 'djackson.jpg');

INSERT INTO `employees` VALUES(8, 'Albert', 'Jones', 'Principal Scientist', 5, '(408)234-5234', '(408)342-5345', 'ajones@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'W6-321', 'ajones.jpg');

INSERT INTO `employees` VALUES(9, 'Terry', 'Eastman', 'Food Services Manager', 6, 'None', 'None', 'teastman@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'Unseated', 'teastman.jpg');

INSERT INTO `employees` VALUES(10, 'Jane', 'Smith', 'Principal Scientist', 2, '(408)125-6534', '(408)758-4512', 'jasmith@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'W3-459', 'jasmith.jpg');

INSERT INTO `employees` VALUES(11, 'Samantha', 'Smith', 'Product Marketing Manager', 7, '(408)155-5893', '(408)452-5555', 'ssmith@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'E12-234', 'ssmith.jpg');

INSERT INTO `employees` VALUES(12, 'Jane', 'Doe', 'Human Resources Manager', 7, '(408)485-9856', '(408)451-4774', 'jadoe@adobe.com', '345 Park Ave', 'San Jose', 'CA', '95110', 'E5-321', 'jadoe.jpg');

Services Folder

EmployeeService.php

<?php

/**

* This sample service contains functions that illustrate typical

* service operations. This code is for prototyping only.

* Authenticate users before allowing them to call these methods.

class EmployeeService

{

public $username = "root";

public $password = "";

public $server = "localhost";

public $port = "3306";

public $databasename = "testdrive_db";

public $tablename = "employees";

public $connection;

public function __construct ()

{

$this->connection = mysqli_connect($this->server, $this->username,

$this->password, $this->databasename, $this->port);

$this->throwExceptionOnError($this->connection);

}

/**

* @return array[Employee]

public function getEmployees ()

{

$stmt = mysqli_prepare($this->connection,

"SELECT

employees.id,

employees.firstname,

employees.lastname,

employees.title,

employees.departmentid,

employees.officephone,

employees.cellphone,

employees.email,

employees.street,

employees.city,

employees.state,

employees.zipcode,

employees.office,

employees.photofile

FROM employees");

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$rows = array();

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->departmentid, $row->officephone,

$row->cellphone, $row->email, $row->street, $row->city, $row->state,

$row->zipcode, $row->office, $row->photofile);

while (mysqli_stmt_fetch($stmt)) {

$rows[] = $row;

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->departmentid, $row->officephone,

$row->cellphone, $row->email, $row->street, $row->city, $row->state,

$row->zipcode, $row->office, $row->photofile);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

public function getEmployeesSummary ()

{

$stmt = mysqli_prepare($this->connection,

"SELECT

employees.id,

employees.firstname,

employees.lastname,

employees.title,

employees.photofile

FROM employees");

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$rows = array();

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->photofile);

while (mysqli_stmt_fetch($stmt)) {

$rows[] = $row;

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->photofile);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

public function getDepartments ()

{

$stmt = mysqli_prepare($this->connection,

"SELECT

departments.id,

departments.name,

departments.manager,

departments.costcenter,

departments.businessunit,

departments.budget,

departments.actualexpenses,

departments.estsalary,

departments.actualsalary,

departments.esttravel,

departments.actualtravel,

departments.estsupplies,

departments.actualsupplies,

departments.estcontractors,

departments.actualcontractors

FROM departments");

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$rows = array();

$row = new Department();

mysqli_stmt_bind_result($stmt, $row->id, $row->name, $row->manager,

$row->costcenter, $row->businessunit, $row->budget, $row->actualexpenses,

$row->estsalary, $row->actualsalary, $row->esttravel, $row->actualtravel,

$row->estsupplies, $row->actualsupplies, $row->estcontractors,

$row->actualcontractors);

while (mysqli_stmt_fetch($stmt)) {

$rows[] = $row;

$row = new Department();

mysqli_stmt_bind_result($stmt, $row->id, $row->name, $row->manager,

$row->costcenter, $row->businessunit, $row->budget,

$row->actualexpenses, $row->estsalary, $row->actualsalary,

$row->esttravel, $row->actualtravel, $row->estsupplies,

$row->actualsupplies, $row->estcontractors, $row->actualcontractors);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

public function getEmployeesByID ($itemID)

{

$stmt = mysqli_prepare($this->connection,

"SELECT

employees.title,

employees.street,

employees.id,

employees.firstname,

employees.lastname,

employees.cellphone,

employees.departmentid,

employees.zipcode,

employees.office,

employees.email,

employees.state,

employees.officephone,

employees.photofile,

employees.city

FROM employees where employees.id=?");

$this->throwExceptionOnError();

mysqli_stmt_bind_param($stmt, 'i', $itemID);

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->title, $row->street, $row->id,

$row->firstname, $row->lastname, $row->cellphone, $row->departmentid,

$row->zipcode, $row->office, $row->email, $row->state, $row->officephone,

$row->photofile, $row->city);

if (mysqli_stmt_fetch($stmt)) {

return $row;

} else {

return null;

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

}

public function getEmployeesByName ($searchStr)

{

$searchStr = '%' . $searchStr . '%';

$stmt = mysqli_prepare($this->connection,

"SELECT

employees.title,

employees.street,

employees.id,

employees.firstname,

employees.lastname,

employees.cellphone,

employees.departmentid,

employees.zipcode,

employees.office,

employees.email,

employees.state,

employees.officephone,

employees.photofile,

employees.city

FROM employees WHERE employees.lastname LIKE ? OR employees.firstname LIKE ?");

$this->throwExceptionOnError();

mysqli_stmt_bind_param($stmt, 'ss', $searchStr, $searchStr);

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$rows = array();

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->title, $row->street, $row->id,

$row->firstname, $row->lastname, $row->cellphone, $row->departmentid,

$row->zipcode, $row->office, $row->email, $row->state, $row->officephone,

$row->photofile, $row->city);

while (mysqli_stmt_fetch($stmt)) {

$rows[] = $row;

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->title, $row->street, $row->id,

$row->firstname, $row->lastname, $row->cellphone, $row->departmentid,

$row->zipcode, $row->office, $row->email, $row->state,

$row->officephone, $row->photofile, $row->city);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

public function getEmployeesSummaryByName ($searchStr)

{

$stmt = mysqli_prepare($this->connection,

"SELECT

employees.id,

employees.firstname,

employees.lastname,

employees.title,

employees.photofile

FROM employees where employees.lastName LIKE ?");

$this->throwExceptionOnError();

mysqli_stmt_bind_param($stmt, 's', $searchStr);

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$rows = array();

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->photofile);

while (mysqli_stmt_fetch($stmt)) {

$rows[] = $row;

$row = new Employee();

mysqli_stmt_bind_result($stmt, $row->id, $row->firstname,

$row->lastname, $row->title, $row->photofile);

}

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $rows;

}

public function createEmployee (Employee $item)

{

$stmt = mysqli_prepare($this->connection,

"INSERT INTO employees (

firstname,lastname,title,departmentid,officephone,cellphone,

email,street,city,state,zipcode,office,photofile)

VALUES (?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?)");

$this->throwExceptionOnError();

mysqli_bind_param($stmt, 'sssisssssssss', $item->firstname,

$item->lastname, $item->title, $item->departmentid, $item->officephone,

$item->cellphone, $item->email, $item->street, $item->city, $item->state,

$item->zipcode, $item->office, $item->photofile);

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

$autoid = mysqli_stmt_insert_id($stmt);

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

return $autoid;

}

public function deleteEmployee ($itemID)

{

$stmt = mysqli_prepare($this->connection,

"DELETE FROM employees WHERE id = ?");

$this->throwExceptionOnError();

mysqli_bind_param($stmt, 'i', $itemID);

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

}

public function updateEmployee ($item)

{

$stmt = mysqli_prepare($this->connection,

"UPDATE employees SET

firstname=?,lastname=?,title=?,departmentid=?,officephone=?,cellphone=?,

email=?,street=?,city=?,state=?,zipcode=?,office=?,photofile=?

WHERE id=?");

$this->throwExceptionOnError();

mysqli_bind_param($stmt, 'sssisssssssssi', $item->firstname,

$item->lastname, $item->title, $item->departmentid, $item->officephone,

$item->cellphone, $item->email, $item->street, $item->city, $item->state,

$item->zipcode, $item->office, $item->photofile, $item->id);

$this->throwExceptionOnError();

mysqli_stmt_execute($stmt);

$this->throwExceptionOnError();

mysqli_stmt_free_result($stmt);

mysqli_close($this->connection);

}

/**

* Utitity function to throw an exception if an error occurs

* while running a mysql command.

private function throwExceptionOnError ($link = null)

{

if ($link == null) {

$link = $this->connection;

}

if (mysqli_error($link)) {

$msg = mysqli_errno($link) . ": " . mysqli_error($link);

throw new Exception('MySQL Error - ' . $msg);

}

EmployeeServiceTest.php

<?php

require_once 'EmployeeService.php';

require_once '../library/Employee.php';

require_once '../library/Department.php';

/**

* EmployeeService test case.

class EmployeeServiceTest extends PHPUnit_Framework_TestCase

{

/**

* @var EmployeeService

private $EmployeeService;

/**

* Prepares the environment before running a test.

protected function setUp ()

{

parent::setUp();

$this->EmployeeService = new EmployeeService();

}

/**

* Cleans up the environment after running a test.

protected function tearDown ()

{

$this->EmployeeService = null;

parent::tearDown();

}

/**

* Tests EmployeeService->getEmployees()

public function testGetEmployees ()

{

$emplyees = $this->EmployeeService->getEmployees();

PHPUnit_Framework_Assert::assertGreaterThan(1, sizeof($emplyees));

}

/**

* Tests EmployeeService->getEmployeesSummary()

public function testGetEmployeesSummary ()

{

$emplyees = $this->EmployeeService->getEmployeesSummary();

PHPUnit_Framework_Assert::assertGreaterThan(1, sizeof($emplyees));

}

/**

* Tests EmployeeService->getDepartments()

public function testGetDepartments ()

{

$departments = $this->EmployeeService->getDepartments();

PHPUnit_Framework_Assert::assertGreaterThan(1, sizeof($departments));

}

/**

* Tests EmployeeService->getEmployeesByID()

public function testGetEmployeesByID ()

{

$employee = $this->EmployeeService->getEmployeesByID(1);

PHPUnit_Framework_Assert::assertGreaterThan(0, sizeof($employee));

}

/**

* Tests EmployeeService->getEmployeesByName()

public function testGetEmployeesByName ()

{

$employee = $this->EmployeeService->getEmployeesByName("Bob");

PHPUnit_Framework_Assert::assertGreaterThan(0, sizeof($employee));

}

/**

* Tests EmployeeService->getEmployeesSummaryByName()

public function testGetEmployeesSummaryByName ()

{

$employee = $this->EmployeeService->getEmployeesSummaryByName("Smith");

PHPUnit_Framework_Assert::assertGreaterThan(0, sizeof($employee));

}

/**

* Tests EmployeeService->createEmployee()

public function testCreateEmployee ()

{

$e = new Employee();

$e->firstname = "firstname";

$e->lastname = "lastname";

$e->title = "title";

$e->departmentid = 1;

$e->officephone = "officephone";

$id = $this->EmployeeService->createEmployee($e);

}

/**

* Tests EmployeeService->deleteEmployee()

public function testDeleteEmployee ()

{

$service = new EmployeeService();

$employees = $service->getEmployeesSummaryByName("lastname");

foreach ($employees as $e) {

$service = new EmployeeService();

$service->deleteEmployee($e->id);

}

Library Folder

Department.php

<?php

/**

* Represents a department entity

class Department

{

/**

* @var int

public $id;

/**

* @var string

public $name;

/**

* @var string

public $manager;

/**

* @var int

public $costcenter;

/**

* @var int

public $businessunit;

/**

* @var string

public $hrrep;

/**

* @var string

public $locationstreet;

/**

* @var string

public $locationcity;

/**

* @var string

public $locationstate;

/**

* @var string

public $locationzipcode;

/**

* @var int

public $budget;

/**

* @var int

public $actualexpenses;

/**

* @var int

public $estsalary;

/**

* @var int

public $actualsalary;

/**

* @var int

public $esttravel;

/**

* @var int

public $actualtravel;

/**

* @var int

public $estsupplies;

/**

* @var int

public $actualsupplies;

/**

* @var int

public $estcontractors;

/**

* @var int

public $actualcontractors;

}

Employee.php

<?php

/**

* Represents an Employee entity

class Employee

{

/**

* @var int

public $id;

/**

* @var string

public $firstname;

/**

* @var string

public $lastname;

/**

* @var string

public $title;

/**

* @var int

public $departmentid;

/**

* @var string

public $officephone;

/**

* @var string

public $cellphone;

/**

* @var string

public $email;

/**

* @var string

public $street;

/**

* @var string

public $city;

/**

* @var string

public $state;

/**

* @var string

public $zipcode;

/**

* @var string

public $office;

/**

* @var string

public $photofile;

}