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
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);
?>
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:
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);
?>
0 comments: