Shady Alaaeldin Issa, Response to Technical Test

This blog is a get started tutorial for the following:

• Work on a LAMP server via ssh using a private/public keypair
• Work on an mysql database with php
• Create a REST service

Since this is concerned with working on a LAMP server via ssh it assumes that the following are correctly set up:

• Apache web server with the required modules for running PHP
• MySQL

SSH

In order to access a remote server using public private key:

#load the contents of the public key file into the authorized keys file
cat id_dsa.pub >> $HOME/.ssh/authorized_keys2
chmod 0600 $HOME/.ssh/authorized_keys2 
#access the server using the following command
ssh -i id_dsa username@server
#when prompted for password enter the ssh private key passphrase

PHP
To make sure that the configurations are correct create a file, name it index.php containing the following:
<?php
    Echo "Hello World!"
?>

When navigating to the root URL, Hello World! should be printed on the screen.

MYSQL and PHP
After making sure that PHP is working correctly the next step is to start working on MYSQL via PHP.

Creating a Table
The following code performs the following:

creates a connection to MYSQL server located at localhost using the credentials of the account peter who has the password abc123.

creates a database schema called my_db by executing the following sql command:

CREATE DATABASE my_db

create a table called Persons by executing the following command:

CREATE TABLE Persons
(
FirstName varchar(15),
LastName varchar(15),
Age int
)

The function responsible for creating a connection to MYSQL server is mysql_connect and the function responsible for executing a SQL command on the MYSQL server is the mysql_query.

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Persons
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

Inserting a Record
The code below inserts a record into the table Persons created by the code above by executing the following SQL command

INSERT INTO Persons (FirstName, LastName, Age) VALUES ('Peter', 'Griffin',35)


<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)");

mysql_query("INSERT INTO Persons (FirstName, LastName, Age) 
VALUES ('Glenn', 'Quagmire',33)");

mysql_close($con);
?>

Selecting Records
To select records from the database the following SQL command is being executed using mysql_query function:
SELECT * FROM Persons

this function returns a result variable which contains an array of results that must be iterated through in order to retrieve each row resulting from the select process.

each time mysql_fetch_array function is executed when given the output of the mysql_query function a new row is returned. Data within the row can be accessed by the column names, the code below prints the FirstName and LastName for all the records within the Persons table.

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

Rest Services
The main advantage of REST services is the simple web interface it provides for other applications to consume using HTTP methods: GET, POST, PUT, DELETE. The following is a simple example for a GET REST service that queries the Persons table by first name.