Monday 17 July 2017

Combining PHP, AngularJS & SQLite make simple Web App


I want to create a web application using Angularjs as the front-end, with PHP and SQLite on the back-end. I started by looking for simple examples of combining their use, but as is often the case, the simple examples were too complicated. These are my notes on creating a web app combining the 3 technologies.
I am not an expert in IT and have little experience of using these 3 technologies, my aim is to get the technologies interacting in the way I want, what I produce may not be the best or most correct way.

Starting with a simple AngularJS App

The inital html page

I started with a simple working AngularJS web app saved as index.html:

<!DOCTYPE html>
<html>
  <head>
 <script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
    <script>
      function myController($scope){
        $scope.name = "Jon"
      }
      </script>
  </head>
  <body>
    <p>My name is: </p>
    <div ng-app="" ng-controller="myController">
    <p>{{name}}</p>
    </div>
  </body>
</html> 
This is a simple angular app which has one function: myController which sets the value of the name $scope variable. This is then injected into the html to give the output:
My name is:
Jon


Adding some PHP

So next we need to retrieve a value from a php script. So let's create a php script named access_db.phpto return the value "Jon".

<?php
 echo "Jon"
?>

As the script will sit server-side we need to access it, we can do this using the angular $http service. We need to replace our current myController function with one which sets the name $scope variable to the response from the $http request to the access_db.php page.

      function myController($scope, $http){
        $http.get('http://localhost/~JonCooper/BirdCoop/access_db.php').
          success(function(data){
            $scope.name = data;
          });

      }
Basically this function performs a get request to the access_db.php when the response is successfully retrieved it then sets the value of the name $scope variable which then appears on the page. Now we need to access the SQLite database and return data from that. Our php script therefore needs to connect to an SQLite database and perform a query, returning the output to angular.

Getting values from SQLite

To access the SQLite database I am going to use PDO (PHP Data Objects), this will enable me to easily change the data source if I want to at a later date. To keep things simple to start I am going to retrieve a single value from a single field. I changed the php script to connect to connect to the database and retrieve the contents of the regionID field from the worldRegions database.
<?php
  // connect to sqlite database called AppDB7.sqlite in same directory as script
  $db_conn = new PDO('sqlite:AppDB7.sqlite');
  // run a query against the database
  $rows = $db_conn -> query('SELECT regionID FROM worldRegions');
  //return the results as an array indexed by column number
  $result = $rows->fetchAll(PDO::FETCH_NUM);
  // return second item of first column
  echo $result[1][0];
?>;
Obviously it would be better to retrieve all of the rows and return them. So we need a directive that can handle an array, so we will display the output of the function as a list instead of as a single tag. So we amend our index.html page to output the results of an array.
<!DOCTYPE html>;
<html>;
  <head>

    <script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
    <script>
      function myController($scope, $http){
        $http.get('http://localhost/~JonCooper/BirdCoop/access_db.php').
          success(function(data){
            $scope.names = data;
          });

      }
      </script>
  </head>
  <body>
    <p>My name is: </p>
    <div ng-app="" ng-controller="myController">
    <ul>
      <li ng-repeat="x in names">{{x}}</li>
    </ul>
    </div>
  </body>
</html>
Note that i have changed the $scope variable from name to names, and now we are outputting our results into an unordered list. Running this without changing the php results in the letters of the returned expression being output each to their own bullet point. So my original:
AFR became
  • A
  • F
  • R
If we simply remove the array brackets and instead echo $result then when we run our web app name is not displayed. If we look at what Angular is receiving, by putting {{names}} into our script, we see the word Array displayed. The array needs to be processed somehow so that Angular can use it. There are a number of ways of doing this we can use json_encode to return our result. If we use echo json_encode($result) in our PHP, then we get a list of terms enclosed in brackets and quotes e.g. ["AFR"]. This is because $result returns an array of arrays, to just get the term in each array we can reference {{x[0]}} instead of {{x}}, this will give us the first term in x, rather than the whole array.

Where are we now?

We have a simple working app with a front-end of AngularJs javascript and html :
<!DOCTYPE html>
<html>
  <head>
    <script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
    <script>
      function myController($scope, $http){
        $http.get('http://localhost/~JonCooper/BirdCoop/access_db.php').
          success(function(data){
            $scope.names = data;
          });

      }
      </script>
  </head>
  <body>
    <p>My name is: </p>
    <div ng-app="" ng-controller="myController">
    <ul>
      <li ng-repeat="x in names">{{x[0]}}</li>
    </ul>
    </div>
  </body>
</html>

Serving up data from our PHP and SQLite backend:
<?php
  // connect to sqlite database called AppDB7.sqlite in same directory as script
  $db_conn = new PDO('sqlite:AppDB7.sqlite');
  // run a query against the database
  $rows = $db_conn -> query('SELECT regionID FROM worldRegions');
  //return the results as an array indexed by column number
  $result = $rows->fetchAll(PDO::FETCH_NUM);
  // return second item of first column
  echo json_encode($result);
?>
This is fine as it goes, but what about if we want results from a different table? What we need is to be able to pass information to the PHP script.

Passing parameters to the PHP script

We can pass parameters to our PHP script by appending a ? followed by variableName=value multiple parameters can be passed by using an & to connect them. The variables can then be accessed by PHP using the $_GET associative array.

Let's amend the myController function to pass some parameters to the PHP function. We will pass tableName=worldRegions to our PHP page by changing our $http.get parameter to:
'http://localhost/~JonCooper/BirdCoop/access_db.php?tableName=worldRegions'
We will then change the query parameter on the PHP to:
'SELECT regionID FROM '.$_GET['tableName']
This works, but we also need to be able to specify the name of the field. Our $http.get parameter is changed to:
'http://localhost/~JonCooper/BirdCoop/access_db.php?tableName=worldRegions&fieldName=regionId'
and our PHP query parameter to:
'SELECT '.$GET['fieldName'].' FROM '.$_GET['tableName']
Now we need a way of changing the tableName and fieldName passed to PHP.
We can do this using $scope variables, we can use $scope.tableName to get and set the tableName and $scope.fieldName to get and set the fieldName. Let's change our $http.get parameter to:
'http://localhost/~JonCooper/BirdCoop/access_db.php?tableName='+$scope.tableName+'&fieldName='+$scope.fieldName. To start with we will set these values in the same function to see if it works. Now that is working we will create a function that can be called. Change myController function to:
      function myController($scope,$http){
        $scope.changeSource = function(){
          $http.get($scope.url+'?tableName='+$scope.tableName+'&fieldName='+$scope.fieldName).
          success(function(data){
            $scope.names = data;
          });
        };
        $scope.tableName = 'worldRegions'
        $scope.fieldName = 'regionID'
        $scope.url = 'http://localhost/~JonCooper/BirdCoop/access_db.php'
        $scope.changeSource()
      }
On the html page we add two radio buttons. We connect these up to change the value of the fieldName variable and when they change they call the chnageSource function which then changes data on the page. The buttons need to be added within the div containing the controller. I have also added a field to show the current value of the field name.
    
    <input ng-change="changeSource()" ng-model="fieldName" type="radio" value="regionID" />; ID

    <input ng-change="changeSource()" ng-model="fieldName" type="radio" value="regionDescription" />;Description

    Field = {{fieldName}}<br />

Clicking the radio button changes the value of the $scope fieldName variable, the $scope changeSource function is then called which updates the page when it returns successfully.

Where next?

The fundamentals of interaction between Angular, PHP and SQLite are there. Of course there are many things missing, security for a start, but the fundamentals are there in a fairly simple app containing just 2 files and an SQLite database:
index.html:
<!DOCTYPE html>
<html>
  <head>
    <script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.2.26/angular.min.js"></script>
    <script>

      function myController($scope,$http){
        $scope.changeSource = function(){
          $http.get($scope.url+'?tableName='+$scope.tableName+'&fieldName='+$scope.fieldName).
          success(function(data){
            $scope.names = data;
          });
        };
        $scope.tableName = 'worldRegions'
        $scope.fieldName = 'regionID'
        $scope.url = 'http://localhost/~JonCooper/BirdCoop/access_db.php'
        $scope.changeSource()
      }

      </script>
  </head>
  <body>

    <p>My name is: </p>
    <div ng-app="" ng-controller="myController">
    <input type="radio" ng-model="fieldName" value="regionID" ng-change="changeSource()"/> ID<br>
    <input type="radio" ng-model="fieldName" value="regionDescription" ng-change="changeSource()"/>Description<br>
    <p>Field = {{fieldName}}</p>
    <ul>
      <li ng-repeat="x in names">{{x[0]}}</li>
    </ul>
    </div>
  </body>
</html>

and a PHP page access_db.php:
<?php
  // connect to sqlite database called AppDB7.sqlite in same directory as script
  $db_conn = new PDO('sqlite:AppDB7.sqlite');
  // run a query against the database
  $rows = $db_conn -> query('SELECT '.$_GET['fieldName'].' FROM '.$_GET['tableName']);
  //return the results as an array indexed by column number
  $result = $rows->fetchAll(PDO::FETCH_NUM);
  // return second item of first column
  echo json_encode($result);
?>

Saturday 6 May 2017

File Uploads in Angular with a Node and Hapi Backend

In this article, we will talk about how to handle file uploads with Angular. We will create an images uploader that allow user to upload single or multiple images file by drag and drop or select file dialog.We will then upload the selected images and display them accordingly. We will also learn to filter the upload file type, for example, we only allow images, do not allow file type like PDF.
                                     Image uploader

# File Upload UI & API

File upload consists of two parts: the UI (front-end) and the API (back-end). We will be using Angular to handle the UI part. We need a backend application to accept the uploaded files. You may follow the backend tutorials or download and run either one of these server side application to handle file upload for your backend:-
We will be using File upload with Hapi.js as our backend throughout this articles. We will also learn the tricks to enable fake upload on the front-end.

# File Upload Component HTML

Alright, let's start creating our Angular file upload component.
<!-- page-file-upload.component.html -->

<div>
  <!--UPLOAD-->
  <form #f="ngForm" enctype="multipart/form-data" novalidate 
    *ngIf="currentStatus === STATUS_INITIAL || currentStatus === STATUS_SAVING">
    <h1>Upload images</h1>
    <div class="dropbox">
      <input type="file" multiple
        [name]="uploadFieldName" (change)="filesChange($event.target.name, $event.target.files)" 
        [disabled]="currentStatus === STATUS_SAVING" accept="image/*" #photos>
      <p *ngIf="currentStatus === STATUS_INITIAL">
        Drag your file(s) here to begin<br>
        or click to browse
      </p>
      <p *ngIf="currentStatus === STATUS_SAVING">
        Uploading {{ photos.files.length }} files... 
      </p>
    </div>
  </form>
</div>
Notes:-
  1. Our upload form will have a few statuses: STATUS_INITIAL, STATUS_SAVING, STATUS_SUCCESS, STATUS_FAILED, the variable name is pretty expressive themselves.
  2. We will display the upload form when the status is initial or saving.
  3. The form attribute enctype="multipart/form-data" is important. To enable file upload, this attribute must be set. Learn more about enctype here.
  4. We have a file input <input type="file" /> to accept file upload. The property multiple indicate it's allow multiple file upload. Remove it for single file upload.
  5. We will handle the file input change event. Whenever the file input change (someone drop or select files), we will trigger the filesChange function and pass in the control name and selected files $event.target.files, and then upload to server.
  6. We limit the file input to accept images only with the attribute accept="image/*".
  7. The file input will be disabled during upload, so user can only drop / select files again after upload complete.
  8. We set a template variable #photos to the file input. This gives us a reference to the file input control. Later, you can see we use the photos variable in displaying number of files uploading Uploading {{ photos.files.length }} files....

# Style our File Upload Component

Now, that's the interesting part. Currently, our component look like this:
                                  File upload component without styling
We need to transform it to look like this:
                                     File upload component with styling
Let's style it!

/* page-file-upload.component.css */

.dropbox {
    outline: 2px dashed grey; /* the dash box */
    outline-offset: -10px;
    background: lightcyan;
    color: dimgray;
    padding: 10px 10px;
    min-height: 200px; /* minimum height */
    position: relative;
    cursor: pointer;
}

.dropbox:hover {
    background: lightblue; /* when mouse over to the drop zone, change color */
}

input[type="file"] {
    opacity: 0; /* invisible but it's there! */
    width: 100%;
    height: 200px;
    position: absolute;
    cursor: pointer;
}

.dropbox p {
    font-size: 1.2em;
    text-align: center;
    padding: 50px 0;
}
With only few lines of css, our component looks prettier now.
Notes:-
  1. We make the file input invisible by applying opacity: 0 style. This doesn't hide the file input, it just make it invisible.
  2. Then, we style the file input parent element, the dropbox css class. We make it look like a drop file zone surround with dash.
  3. Then, we align the text inside dropbox to center.

# File Upload Component Code

// page-file-upload.component.ts

import { Component } from '@angular/core';
import { FileUploadService } from './file-upload.service'; // we will create this next!

@Component({
  selector: 'page-file-upload',
  templateUrl: './page-file-upload.component.html',
  styleUrls: ['./page-file-upload.component.css']
})
export class PageFileUploadComponent {

  uploadedFiles = [];
  uploadError;
  currentStatus: number;
  uploadFieldName = 'photos';

  readonly STATUS_INITIAL = 0;
  readonly STATUS_SAVING = 1;
  readonly STATUS_SUCCESS = 2;
  readonly STATUS_FAILED = 3;

  constructor(private _svc: FileUploadService) {
    this.reset(); // set initial state
  }

  filesChange(fieldName: string, fileList: FileList) {
    // handle file changes
    const formData = new FormData();

    if (!fileList.length) return;

    // append the files to FormData
    Array
      .from(Array(fileList.length).keys())
      .map(x => {
        formData.append(fieldName, fileList[x], fileList[x].name);
      });

    // save it
    this.save(formData);
  }

  reset() {
    this.currentStatus = this.STATUS_INITIAL;
    this.uploadedFiles = [];
    this.uploadError = null;
  }

  save(formData: FormData) {
    // upload data to the server
    this.currentStatus = this.STATUS_SAVING;
    this._svc.upload(formData)
      .take(1)
      .delay(1500) // DEV ONLY: delay 1.5s to see the changes
      .subscribe(x => {
        this.uploadedFiles = [].concat(x);
        this.currentStatus = this.STATUS_SUCCESS;
      }, err => {
        this.uploadError = err;
        this.currentStatus = this.STATUS_FAILED;
      })
  }
}
Notes:-
  1. Later on, we will call the Hapi.js file upload API to upload images, the API accept a field call photos. That's our file input field name.
  2. We handle the file changes with the filesChange function. FileList is an object returned by the files property of the HTML element. It allow us to access the list of files selected with the element. Learn more [here]((https://developer.mozilla.org/en/docs/Web/API/FileList).
  3. We then create a new FormData, and append all our photos files to it. FormData interface provides a way to easily construct a set of key/value pairs representing form fields and their values. Learn more here.
  4. The save function will call our file upload service (hang on, we will create the service next!). We also set the status according to the result.

# File Upload Service

// file-upload.service.ts

import { Injectable } from '@angular/core';
import { Http, RequestOptionsArgs, Headers } from '@angular/http';

@Injectable()
export class FileUploadService {

    baseUrl = 'http://localhost:3001'; // our local Hapi Js API

    constructor(private _http: Http) { }

    upload(formData) {
        const url = `${this.baseUrl}/photos/upload`;
        return this._http.post(url, formData)
            .map(x => x.json())
            .map((x: any[]) => x
          // add a new field url to be used in UI later
                .map(item => Object
                    .assign({}, item, { url: `${this.baseUrl}/images/${item.id}` }))
            );
    }
}
Nothing much, the code is pretty expressive itself. We upload the files, wait for the result, map it accordingly.
Now wire up your component and service to module, usually app.module.ts, and run it.

# Display Success and Failed Result

We can upload the files successfully now. However, there's no indication in UI. Let's update our HTML.
<!-- page-file-upload.component.html -->
<div>
  <!--UPLOAD-->
  ...

  <!--SUCCESS-->
  <div class="margin-20" *ngIf="currentStatus === STATUS_SUCCESS">
    <h2>Uploaded {{ uploadedFiles.length }} file(s) successfully.</h2>
    <p>
      <a href="javascript:void(0)" (click)="reset()">Upload again</a>
    </p>
    <ul class="list-unstyled">
      <li *ngFor="let item of uploadedFiles">
        <img [src]="item.url" class="img-responsive img-thumbnail" 
          [alt]="item.originalName">
      </li>
    </ul>
  </div>

  <!--FAILED-->
  <div class="margin-20" *ngIf="currentStatus === STATUS_FAILED">
    <h2>Uploaded failed.</h2>
    <p>
      <a href="javascript:void(0)" (click)="reset()">Try again</a>
    </p>
    <pre>{{ uploadError | json }}</pre>
  </div>
</div>
Notes:-
  1. Display the uploaded image when upload successfully.
  2. Display the error message when upload failed.

# Fake the Upload in Front-end

If you are lazy to start the back-end application (Hapi, Express, etc) to handle file upload. Here is a fake service to replace the file upload service.
// file-upload.fake.service.ts

import { Injectable } from '@angular/core';
import { Observable } from 'rxjs/Rx';

@Injectable()
export class FileUploadFakeService {

    upload(formData: any) {
        const photos: any[] = formData.getAll('photos');
        const promises = photos.map((x: File) => this.getImage(x)
            .then(img => ({
                id: img,
                originalName: x.name,
                fileName: x.name,
                url: img
            })));
        return Observable.fromPromise(Promise.all(promises));
    }

    private getImage(file: File) {
        return new Promise((resolve, reject) => {
            const fReader = new FileReader();
            const img = document.createElement('img');

            fReader.onload = () => {
                img.src = fReader.result;
                resolve(this.getBase64Image(img));
            }

            fReader.readAsDataURL(file);
        })
    }

    private getBase64Image(img) {
        const canvas = document.createElement('canvas');
        canvas.width = img.width;
        canvas.height = img.height;

        const ctx = canvas.getContext('2d');
        ctx.drawImage(img, 0, 0);

        const dataURL = canvas.toDataURL('image/png');

        return dataURL;
    }
}
Came across this solution in this Stackoverflow post. Pretty useful. My online demo is using this service.
Basically, what the code do is read the source, draw it in canvas, and save it as data url with the canvas toDataURL function. Learn more about canvas here.
If you realize, our fake service has a same public interface as the real file upload service, both has upload function and return list of files. This is important for the next step, swap the real file upload service with the fake one.

# Swap the Real File Upload with the Fake Service

First you might think that to use the fake service, you need to register the fake service in module, and import it in our file upload component like how we do usually. However, there's a quicker way, with Angular dependency injection (DI). Let's look at our App module.
// app.module.ts

...
import { PageFileUploadComponent, FileUploadFakeService, 
  FileUploadService } from './file-upload';

@NgModule({
  ...
  providers: [
    // FileUploadService, // normally we do this, comment it, we do the below instead
    { provide: FileUploadService, useClass: FileUploadFakeService }, // we can do this instead

  ],
  ...
})
export class AppModule { }
With this, you don't need to change your component code, stop your backend API, refresh the browser, you should see our app is still working, calling fake service instead.
In short, Providers: [FileUploadService] is the short form of Providers: [{ provide: FileUploadService, useClass: FileUploadService }]. Therefore, as long as we have another class with similar interface, we can swap it easily.
Angular DI is powerful. We'll leave that for another post.

Thursday 6 April 2017

Display data from Mysql Database Using AngularJS with PHP

Hi friends, In this post we going to see how to display data from Mysql database using AngularJS with PHP. I will share very helpful things with you. Nowadays AngularJS is the best choice to develop the web application. If you are going to begin a new project, then why not choose AngularJS as front-end and PHP as back-end. So in this post, I will show you how to display data from MySQL using AngularJS and PHP.

index.php
display.php
Did you find apk for android? You can find new Free Android Games and apps.