Technical Test – Javascript (Frontend) & PHP (API Backend) | Jonathan Devine

Please complete the following technical task. I will be reviewing it to asses the following:

* Your ability to follow vague instructions and use your own initiative.
* Your ability to write documented PHP code.
* Your ability to design and abstract database tables.
* Your ability to ability to write a simple RESTful API.
* Your ability to use jQuery and ajax to interact with your API.
* Your ability to write integration documentation for your code.
* The overall robustness of your code (error handling, validation and security especially).

Here is the task:

* Create a simple RESTful API and PHP 7 application which serves to create, retrieve, update and delete employees to two fictional companies "Company A" and "Company B".

* A database with company and employee tables have been provided for you. Use these tables, but feel free to add any other tables or columns you need.

* The RESTful API must be the backend of your PHP app.

* Ensure that your API returns the proper HTTP status where required: 200/400/403/404/405/406/500 - this will be tested.

* In the frontend of your PHP app, create a simple list view to show all employees from all companies, but provide an option to filter these by company and sort them by first name or last name. Also include action buttons to add a new employee and update/delete existing employees.

* Use bootstrap components for style and responsive layout.

* Use a modal dialog to display the form which creates or updates a user.

* When deleting a user, display a message to ask the user if they are sure they want to proceed.

* When creating, updating or deleting a user, use jQuery ajax to call your API which in turn updates the database.

* When complete, put the project in a .git repository and add a readme.md to explain how to install and run the project on a vanilla linux box.

Source code: https://github.com/JonnyD/Technical-Test-Javascript-and-PHP

Employees:

You can filter by Company:

and sort by first name or last name:

You can read each employee:

Edit:

And Delete

Create employee:

Here is the MySQL to create and populate employees and companies:

CREATE DATABASE  IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `test`;

--
-- Table structure for table `company`
--

DROP TABLE IF EXISTS `company`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO `company` (`id`, `name`) VALUES (1, 'Company A');
INSERT INTO `company` (`id`, `name`) VALUES (2, 'Company B');

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) DEFAULT NULL,
  `last_name` varchar(45) DEFAULT NULL,
  `job_title` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `address_line_1` varchar(45) DEFAULT NULL,
  `address_line_2` varchar(45) DEFAULT NULL,
  `town_city` varchar(45) DEFAULT NULL,
  `county_region` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `postcode` varchar(45) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

ALTER TABLE employee ADD FOREIGN KEY (company_id) REFERENCES company(id);

INSERT INTO employee (id, first_name, last_name, job_title, email, phone, address_line_1, address_line_2, town_city, county_region, country, postcode, company_id)
VALUES (1, 'Jonathan', 'Devine', 'Software Developer', 'contact@jonnydevine.com', '0862041801', '23 Windmill Road', NULL, 'Drogheda', 'Louth', 'Ireland', 'ABC123', 1);
INSERT INTO employee (id, first_name, last_name, job_title, email, phone, address_line_1, address_line_2, town_city, county_region, country, postcode, company_id)
VALUES (2, 'Steven', 'Andrews', 'Front End Developer', 'contact@steven.com', '0862454944', '2 Fake Road', NULL, 'Dublin', 'Dublin', 'Ireland', 'DEF456', 2);

Here is an example of reading employees using Javascript:

$(document).ready(function(){

    // show list of employees on first load
    showEmployees();

    $(document).on('click', '#select-company', function(){
        showEmployees();
    });

    $(document).on('click', '.order-by', function(){
        showEmployees();
    });
});

// function to show list of employees
function showEmployees(){
    var order_by = $('input[name=orderBy]:checked').val();
    if (typeof order_by === 'undefined' || !order_by) {
        order_by = "first_name";
    }

    var company_id = $("#select-company").val();
    if (typeof company_id === 'undefined' || !company_id) {
        company_id = "All";
    }

    var order_by_html = "";
    if (order_by === "first_name") {
        order_by_html += "<input type='radio' class='order-by' name='orderBy' value='first_name' checked> First Name";
    } else {
        order_by_html += "<input type='radio' class='order-by' name='orderBy' value='first_name'> First Name";
    }
    if (order_by === "last_name") {
        order_by_html += "<input type='radio' class='order-by' name='orderBy' value='last_name' checked> Last Name";
    } else {
        order_by_html += "<input type='radio' class='order-by' name='orderBy' value='last_name'> Last Name";
    }

    // load list of companies
    $.getJSON("api/company/read.php", function(data) {
        // build companies option html
        // loop through returned list of data
        var companies_options_html = "";
        companies_options_html += "<select id='select-company' name='company_id' class='form-control pull-left'>";
        companies_options_html += "<option value='All'>All</option>";
        $.each(data, function (key, val) {
            if(val.id == company_id) {
                companies_options_html += "<option value='" + val.id + "' selected>" + val.name + "</option>";
            } else {
                companies_options_html += "<option value='" + val.id + "'>" + val.name + "</option>";
            }
        });
        companies_options_html += "</select>";

        // get list of employees from the API
        $.getJSON("api/employee/read.php?company_id=" + company_id + "&order_by=" + order_by, function(data){
            // html for listing employees
            read_employees_html = "";

            read_employees_html += "<div class='row'>";
                read_employees_html += "<div class='col-md-4'>" + companies_options_html + "</div>";

                read_employees_html += "<div class='col-md-4'>" + order_by_html + "</div>";

                // when clicked, it will load the create employee form
                read_employees_html += "<div class='col-md-4'><div id='create-employee' class='btn btn-primary pull-right create-employee-button' data-toggle='modal' data-target='#myModal'>";
                    read_employees_html += "<span class='glyphicon glyphicon-plus'></span> Create Employee";
                read_employees_html += "</div></div>";

            read_employees_html += "</div>";
            // start table
            read_employees_html += "<table class='table table-bordered table-hover'>";

                // creating our table heading
                read_employees_html += "<tr>";
                    read_employees_html += "<th class='w-25-pct'>First Name</th>";
                    read_employees_html += "<th class='w-10-pct'>Last Name</th>";
                    read_employees_html += "<th class='w-15-pct'>Company</th>";
                    read_employees_html += "<th class='w-25-pct text-align-center'>Action</th>";
                read_employees_html += "</tr>";

            // loop through returned list of data
            $.each(data, function(key, val) {

                // creating new table row per record
                read_employees_html += "<tr>";

                    read_employees_html += "<td>" + val.first_name + "</td>";
                    read_employees_html += "<td>" + val.last_name + "</td>";
                    read_employees_html += "<td>" + val.company.name + "</td>";

                    // 'action' buttons
                    read_employees_html += "<td>";
                        // read employee button
                        read_employees_html += "<button class='btn btn-primary read-one-employee-button' data-id='" + val.id + "' data-toggle='modal' data-target='#myModal'>";
                            read_employees_html += "<span class='glyphicon glyphicon-eye-open'></span> Read";
                        read_employees_html += "</button>";

                        // edit button
                        read_employees_html += "<button class='btn btn-info update-employee-button' data-id='" + val.id + "' data-toggle='modal' data-target='#myModal'>";
                            read_employees_html += "<span class='glyphicon glyphicon-edit'></span> Edit";
                        read_employees_html += "</button>";

                        // delete button
                        read_employees_html += "<button class='btn btn-danger delete-employee-button' data-id='" + val.id + "'>";
                            read_employees_html += "<span class='glyphicon glyphicon-remove'></span> Delete";
                        read_employees_html += "</button>";
                    read_employees_html += "</td>";

                read_employees_html += "</tr>";

            });

            // end table
            read_employees_html += "</table>";

            // inject to 'page-content' of our app
            $("#page-content").html(read_employees_html);

        });
    });
}

For more see here https://github.com/JonnyD/Technical-Test-Javascript-and-PHP/tree/master/app/employees

An example of a backend API:

<?php
// required header
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// include files
include_once '../config/database.php';
include_once '../objects/employee.php';
include_once '../objects/company.php';
include_once '../repository/employee_repository.php';
include_once '../repository/company_repository.php';

// instantiate database
$database = new Database();
$db = $database->getConnection();

// instantiate employee repository
$employeeRepository = new EmployeeRepository($db);

// instantiate company repository
$companyRepository = new CompanyRepository($db);

// get company ID
$companyId = isset($_GET['company_id']) ? $_GET['company_id'] : null;

// get order by
$orderBy = isset($_GET['order_by']) ? $_GET['order_by'] : null;

// query employees
if ($companyId != "All" && $companyId != null) {
    $employees = $employeeRepository->readByCompany($companyId, $orderBy);
} else {
    $employees = $employeeRepository->read($orderBy);
}

// check if more than 0 records found
if(count($employees) > 0) {
    $employeesArray = [];

    foreach ($employees as $employee) {
        $company = $companyRepository->readOne($employee->getCompanyId());

        $employeesArr[] = [
            'id' => $employee->getId(),
            'first_name' => $employee->getFirstName(),
            'last_name' => $employee->getLastName(),
            'job_title' => $employee->getJobTitle(),
            'email' => $employee->getEmail(),
            'phone' => $employee->getPhone(),
            'address_line_1' => $employee->getAddressLine1(),
            'address_line_2' => $employee->getAddressLine2(),
            'town_city' => $employee->getTownCity(),
            'county_region' => $employee->getCountyReqion(),
            'country' => $employee->getCountry(),
            'postcode' => $employee->getPostcode(),
            'company' => [
                'id' => $company->getId(),
                'name' => $company->getName()
            ]
        ];
    }

    header("HTTP/1.1 200");
    echo json_encode($employeesArr);
} else {
    header("HTTP/1.1 404");
    echo json_encode([
        "message" => "No employees found."
    ]);
}
?>

More here https://github.com/JonnyD/Technical-Test-Javascript-and-PHP/tree/master/api

EmployeeRepository:

<?php

class EmployeeRepository
{
    /**
     * @var string
     */
    private $tableName;

    /**
     * @var PDO
     */
    private $db;

    /**
     * @param PDO $db
     */
    public function __construct(PDO $db)
    {
        $this->tableName = "employee";
        $this->db = $db;
    }

    /**
     * @param string $orderBy
     * @return Employee[]
     */
    public function read(string $orderBy = null)
    {
        $query = "SELECT
                        id, 
                        first_name, 
                        last_name,
                        job_title,
                        email,
                        phone,
                        address_line_1,
                        address_line_2,
                        town_city,
                        county_region,
                        country,
                        postcode,
                        company_id
                    FROM
                        " . $this->tableName;

        if ($orderBy != null) {
            $query .= " ORDER BY " . $orderBy . " ASC";
        }

        $stmt = $this->db->prepare($query);
        $stmt->execute();

        $employees = [];
        if($stmt->rowCount() > 0) {
            // retrieve our table contents
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $employee = new Employee();
                $employee->setId($row['id']);
                $employee->setFirstName($row['first_name']);
                $employee->setLastName($row['last_name']);
                $employee->setJobTitle($row['job_title']);
                $employee->setEmail($row['email']);
                $employee->setPhone($row['phone']);
                $employee->setAddressLine1($row['address_line_1']);
                $employee->setAddressLine2($row['address_line_2']);
                $employee->setTownCity($row['town_city']);
                $employee->setCountyReqion($row['county_region']);
                $employee->setCountry($row['country']);
                $employee->setPostcode($row['postcode']);
                $employee->setCompanyId($row['company_id']);

                array_push($employees, $employee);
            }
        }

        return $employees;
    }

    /**
     * @param int $companyId
     * @param string $orderBy
     * @return Employee[]
     */
    public function readByCompany(int $companyId, string $orderBy = null)
    {
        $query = "SELECT
                        id, 
                        first_name, 
                        last_name,
                        job_title,
                        email,
                        phone,
                        address_line_1,
                        address_line_2,
                        town_city,
                        county_region,
                        country,
                        postcode,
                        company_id
                    FROM
                        " . $this->tableName . "
                    WHERE company_id = :companyId";

        if ($orderBy != null) {
            $query .= " ORDER BY " . $orderBy . " ASC";
        }

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(":companyId", $companyId);
        $stmt->execute();

        $employees = [];
        if($stmt->rowCount() > 0) {
            // retrieve our table contents
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $employee = new Employee();
                $employee->setId($row['id']);
                $employee->setFirstName($row['first_name']);
                $employee->setLastName($row['last_name']);
                $employee->setJobTitle($row['job_title']);
                $employee->setEmail($row['email']);
                $employee->setPhone($row['phone']);
                $employee->setAddressLine1($row['address_line_1']);
                $employee->setAddressLine2($row['address_line_2']);
                $employee->setTownCity($row['town_city']);
                $employee->setCountyReqion($row['county_region']);
                $employee->setCountry($row['country']);
                $employee->setPostcode($row['postcode']);
                $employee->setCompanyId($row['company_id']);

                array_push($employees, $employee);
            }
        }

        return $employees;
    }

    /**
     * @param int $id
     * @return Employee|null
     */
    public function readOne(int $id)
    {
        $query = "SELECT 
                    id,
                    first_name,
                    last_name,
                    job_title,
                    email,
                    phone,
                    address_line_1,
                    address_line_2,
                    town_city,
                    county_region,
                    country,
                    postcode,
                    company_id
				FROM " . $this->tableName . "
				WHERE id = :id
				LIMIT 0,1";

        // prepare query statement
        $stmt = $this->db->prepare($query);

        // bind selected record id
        $stmt->bindParam(":id", $id);

        // execute the query
        $stmt->execute();

        // get record details
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($stmt->rowCount() > 0) {
            // assign values to object properties
            $employee = new Employee();
            $employee->setId($row['id']);
            $employee->setFirstName($row['first_name']);
            $employee->setLastName($row['last_name']);
            $employee->setJobTitle($row['job_title']);
            $employee->setEmail($row['email']);
            $employee->setPhone($row['phone']);
            $employee->setAddressLine1($row['address_line_1']);
            $employee->setAddressLine2($row['address_line_2']);
            $employee->setTownCity($row['town_city']);
            $employee->setCountyReqion($row['county_region']);
            $employee->setCountry($row['country']);
            $employee->setPostcode($row['postcode']);
            $employee->setCompanyId($row['company_id']);

            return $employee;
        } else {
            return null;
        }
    }

    /**
     * @param Employee $employee
     * @return bool
     */
    public function create(Employee $employee)
    {
        $query = "INSERT INTO
					" . $this->tableName . "
				SET
					first_name = :first_name, 
					last_name = :last_name,
					job_title = :job_title,
					email = :email,
					phone = :phone,
					address_line_1 = :address_line_1,
					address_line_2 = :address_line_2,
					town_city = :town_city,
					county_region = :county_region,
					country = :country,
					postcode = :postcode,
					company_id = :company_id";

        // prepare query
        $stmt = $this->db->prepare($query);

        // bind values
        $firstName = $employee->getFirstName();
        $stmt->bindParam(":first_name", $firstName);
        $lastName = $employee->getLastName();
        $stmt->bindParam(":last_name", $lastName);
        $jobTitle = $employee->getJobTitle();
        $stmt->bindParam(":job_title", $jobTitle);
        $email = $employee->getEmail();
        $stmt->bindParam(":email", $email);
        $phone = $employee->getPhone();
        $stmt->bindParam(":phone", $phone);
        $addressLine1 = $employee->getAddressLine1();
        $stmt->bindParam(":address_line_1", $addressLine1);
        $addressLine2 = $employee->getAddressLine2();
        $stmt->bindParam(":address_line_2", $addressLine2);
        $townCity = $employee->getTownCity();
        $stmt->bindParam(":town_city", $townCity);
        $countyRegion = $employee->getCountyReqion();
        $stmt->bindParam(":county_region", $countyRegion);
        $country = $employee->getCountry();
        $stmt->bindParam(":country", $country);
        $postcode = $employee->getPostcode();
        $stmt->bindParam(":postcode", $postcode);
        $companyId = $employee->getCompanyId();
        $stmt->bindParam(":company_id", $companyId);

        // execute query
        if ($stmt->execute()) {
            return true;
        } else {
            echo "<pre>";
            print_r($stmt->errorInfo());
            echo "</pre>";

            return false;
        }
    }

    /**
     * @param Employee $employee
     * @return bool
     */
    public function update(Employee $employee)
    {
        $query = "UPDATE
					" . $this->tableName . "
				SET
					first_name = :first_name, 
					last_name = :last_name,
					job_title = :job_title,
					email = :email,
					phone = :phone,
					address_line_1 = :address_line_1,
					address_line_2 = :address_line_2,
					town_city = :town_city,
					county_region = :county_region,
					country = :country,
					postcode = :postcode,
					company_id = :company_id
				WHERE
					id = :id";

        // prepare query statement
        $stmt = $this->db->prepare($query);

        // bind new values
        $id = $employee->getId();
        $stmt->bindParam(':id', $id);
        $firstName = $employee->getFirstName();
        $stmt->bindParam(":first_name", $firstName);
        $lastName = $employee->getLastName();
        $stmt->bindParam(":last_name", $lastName);
        $jobTitle = $employee->getJobTitle();
        $stmt->bindParam(":job_title", $jobTitle);
        $email = $employee->getEmail();
        $stmt->bindParam(":email", $email);
        $phone = $employee->getPhone();
        $stmt->bindParam(":phone", $phone);
        $addressLine1 = $employee->getAddressLine1();
        $stmt->bindParam(":address_line_1", $addressLine1);
        $addressLine2 = $employee->getAddressLine2();
        $stmt->bindParam(":address_line_2", $addressLine2);
        $townCity = $employee->getTownCity();
        $stmt->bindParam(":town_city", $townCity);
        $countyRegion = $employee->getCountyReqion();
        $stmt->bindParam(":county_region", $countyRegion);
        $country = $employee->getCountry();
        $stmt->bindParam(":country", $country);
        $postcode = $employee->getPostcode();
        $stmt->bindParam(":postcode", $postcode);
        $companyId = $employee->getCompanyId();
        $stmt->bindParam(":company_id", $companyId);

        // execute the query
        if ($stmt->execute()) {
            return true;
        }else{
            return false;
        }
    }

    /**
     * @param int $id
     * @return bool
     */
    public function delete(int $id)
    {
        $query = "DELETE FROM " . $this->tableName . " WHERE id = :id";

        // prepare query
        $stmt = $this->db->prepare($query);

        // bind id of record to delete
        $stmt->bindParam(':id', $id);

        // execute query
        if ($stmt->execute()) {
            return true;
        }

        return false;

    }
}

Jonathan Devine – Software/Web Developer

Skills: PHP, Java, Javascript, Angular, Typescript, Node, MySQL.

Technical Test – Javascript (Frontend) & PHP (API Backend)

Leave a Reply Cancel reply