Install LAMP on Ubuntu and implement PHP scripts

In this article I explain how to install LAMP (Apache, MySQL, and PHP) on Ubuntu and implement some PHP scripts to insert records into the database from an iOS app. We need a server for our database, we will use a Linux Ubuntu host. This is a cheap, but flexible option.

We shall assume to store more types of data in the database: integer, string and date.

On a Linux Ubuntu 16.10 64 bit we can install LAMP with these terminal windows commands:

sudo apt-get update
sudo apt-get install apache2

sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql

sudo mysql_install_db
sudo /usr/bin/mysql_secure_installation
sudo apt-get install php5 libapache2-mod-php5 php5-mcrypt
sudo nano /etc/apache2/mods-enabled/dir.conf

Add index.php to the beginning of index files. The page should now look like this:

...
DirectoryIndex index.php index.html index.cgi index.pl index.php index.xhtml index.htm
...

Now we can test PhpMyAdmin. In a browser execute:

http://localhost/phpmyadmin

Change “localhost” with IP address of the database host.

Then test apache writing a php script in the folder /var/www/html. Create a file named hello_world.php with the following lines:

<?php
 echo “hello World !”;
 ?>

To make the script executable:

$ sudo chmod 755 /var/www/html/hello_world.php

Test the script in the browser with:

http://localhost/hello_world.php

Now the thing we need is the database, so we create it with phpmyadmin.

Go to phpmyadmin (http://localhost/phpmyadmin) and create a new database.

Create a database named sheetsdb, and add a table with the name workdata.

Set the id field as primary key, with AUTO_INCREMENT property.

Now we have our database. We will store the values in this table.

Creating PHP Scripts

We can use notepad, or a text editor, or a programming IDE. We assume to use geany,  available as Ubuntu package.

To install geany:

$ sudo apt-get install geany

In the htdocs folder we create an etc folder, and a bin folder.

Inside directory etc create a file named Params.php and write the following code.

<?php
 define('DB_USERNAME', 'the database user);
 define('DB_PASSWORD', 'the database password');
 define('DB_HOST', 'the host name or localhost');
 define('DB_NAME', 'sheetsdb');

Now in the same directory (etc) again create a new file named DatabaseConnect.php and write the following lines of code to create the database connection.

<?php

class DatabaseConnect
{
private $conn;
function __construct()
{

}
/* database connection

function connect()
{
require_once 'Params.php';
// Connecting to the database
$this->conn = new Mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check for database connection error
if (Mysqli_connect_errno()) {
echo "Connection Failed: " . Mysqli_connect_error();
}
// returing connection link
return $this->conn;
}
}

We need a file for the database operations, so we can create a file named DbInsert.php and write the following code with the editor.

<?php

class DbInsert
{
   private $conn;

   //Constructor
   function __construct()
   {

      require_once dirname(__FILE__) . '/Params.php';
      require_once dirname(__FILE__) . '/DatabaseConnect.php';

      // opening db connection
      $db = new DatabaseConnect();
      $this->conn = $db->connect();
   }

   //Function to create a new record
   // substitute ..... with the desired fields
   public function createRecord($username, $work_id, $compl, ...... $surveydate, $note)
   {

      $stmt = $this->conn->prepare("INSERT INTO workdata(username, work_id, compl, edif, allo, exallo, plan, address, civilworks, electricworks, woodworks, plumberworks, surveydate, note) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

      // the 1st parameter is s for each string field, i for integer, d for double ...
      $stmt->bind_param("sii ..... ss", $username, $work_id, $compl, ......... $surveydate, $note);

      $result = $stmt->execute();
      $stmt->close();
      if ($result) {
      return true;
      } else {
      return false;
      }
   }
}

Now inside bin folder create a file named CreateRec.php and write the following code. This file will actually insert the values to database.

<?php

//creating return information

$response = array();

if($_SERVER['REQUEST_METHOD']=='POST'){

//getting values
$username = $_POST['username'];
$work_id = $_POST['work_id'];
$compl = $_POST['compl'];
// ..... other fields ....
$surveydate = $_POST['surveydate'];
$note = $_POST['note'];

//including the db operation file

require_once '../etc/DbInsert.php';

$db = new DbInsert();

//inserting values

if($db->createRecord($username, $work_id, $compl, $edif, ......, $surveydate, $note)){

$response['error']=false;
$response['message']='Record added successfully';
}else{
$response['error']=true;
$response['message']='Could not add record';
}

}else{
$response['error']=true;
$response['message']='user not authorized';
}

echo json_encode($response);

Our web service is ready for the use.

We can write the Web Service URL address in a browser

http://IP_ADDRESS/WebService/bin/CreateRec.php

We should first test the web service we created, for example for testing we can use Google REST Client for the Mozilla browser.

In the REST Client, freely available as Mozilla Plugin, in the headers menu we must insert the two values:
name                    Content-Type
value                    application/x-www-form-urlencoded

As you can see below we have successfully inserted a record in the database, writing in the body section the field values concatenated by the & symbol, and executing the php program pressing the red button.

Note that the date format in the body must be in the format yyyy-MM-dd.

In the next tutorial we shall send the http post request to the URL from an iOS app.

All product names, logos, and brands are property of their respective owners