Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Wednesday, March 9, 2016

Sample application with AngularJS, PHP and Mysql

AngularJS sample application with PHP and MYSQL

Lets start by creating a sample database and a table to have some values which can be used for listing the data.

AngularJS PHP MYSQL



CREATE TABLE `movies` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `director` VARCHAR(15) NOT NULL, `year` int(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;


Then some default entries to the table

INSERT INTO `movies` (`id`, `title`, `director`, `year`) VALUES (1, 'Mad Max: Fury Road', 'George Miller', '2015'), (2, 'Captain Phillips', 'Paul Greengrass', '2013'), (3, 'Room', 'Lenny Abrahamson', '2015') , (4, 'London Has Fallen', 'Babak Najafi', '2016') , (5, 'Deadpool', 'Tim Miller', '2016');


Now we have the database ready to be used, shall start from building the html pages, js and relative php file.

index.html

<!DOCTYPE html>

<html ng-app="customMovApp">

<head>

 <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.0/angular.min.js"></script>

 <script type="text/javascript" src="js/app.js"></script>

</head>

        <body ng-controller="movieController">

                <div ng-include src=" 'views/movie.html' "></div>

        </body>

</html>

In angularjs based applications we use attribute names with ng keyword. As you can see in the html tag we have an attribute ng-app been set to a value 'customMovApp' and this is to initialize the html page with anuglarjs app which we will be defining on the app.js file. Within the body tag we have also initialized to controller to be used for this page with ng-controller attribute. Then we have a content loading div section which uses another view to be included when the index page is loaded. Here, note that the source url used within the ng-include attribute is enclosed within single quotes.

Now we shall define the app.js file which will be having the core of angular js application.

app.js

var app = angular.module('customMovApp',[]);

app.controller('movieController',  function($scope, $http) {

 getMovie();

 function getMovie() {

  $http.post("getMovie.php").success(function(data) {

   console.log(data);

   $scope.movies = data;

  });

 }

});


Here from the 1st line angular module is been initialized as 'customMovApp' and then with that we have created a new controller named 'movieController' and it has a function 'getMovie' which is in turn defined to call the php file getMovie.php and set the returned json object to 'movies' variable.

We already have a database and a table with some sample entries, we have the php script to connect to the database and returns the response as json object. For this we have a sample script which can be used.

getMovie.php

// mysqli connection object initialization
$mysqli = new mysqli('host', 'username', 'password', 'dbname');

$query = "select * from movies";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
$arr     = array();

if($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
                $arr[] = $row;
        }

}

// JSON-encoded response
echo $json_response = json_encode($arr);


Now we need a view page to render the response from the controller action of angular module.

movie.html

<table ng-controller="movieController">

<thead><tr><th>Title</th><th>Director</th><th>Released Year</th></tr></thead>

<tbody>
 <tr ng-repeat="movie in movies">

 <td>{{movie.title}}</td>
 <td>{{movie.director}}</td>
 <td>
    <div ng-if="movie.year == '2016'"> <span>Current Year</span> </div>
 <div ng-if="movie.year != '2016'"> <span>{{movie.year}}</span> </div>
 </td>

 </tr>

</tbody> </table>

Here we are using ng-repeat to loop through all the movie records from the response object.

Thursday, July 9, 2015

Sugarcrm connection to mysql DB with SSL - Amazon RDS with SSL - using ca-cert file

This is based on Sugarcrm CE 6.5, and by default the MysqliManager.php doesnt support ssl connection to mysql db's, even if we set the 'ssl' flag on 'dboptions' of config.php file. To make the DBManager work, we need to update the MysqliManager code.

First, we need to have the ssl flag on and the file path to the ca-cert file, for that we can have these details on config_override.php
$sugar_config['dbconfigoption']['ssl'] = true;
$sugar_config['dbconfigoption']['ca_cert'] = '/home/user/ca-cert-rds.pem';

Then, update the MysqliManger.php file inside include/database folder.

            $link = FALSE;

            $configDbOptions = $this->getOptions();

            if ($configDbOptions['ssl'] === TRUE) {

                $this->database = mysqli_init();

                mysqli_options($this->database, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

                $this->database->ssl_set(NULL,NULL,$configDbOptions['ca_cert'],NULL,NULL);

                $link = $this->database->real_connect($dbhost, $configOptions['db_user_name'], $configOptions['db_password'], isset($configOptions['db_name'])?$configOptions['db_name']:'', $dbport, NULL, MYSQLI_CLIENT_SSL);

            } else {

                $this->database = mysqli_connect($dbhost,$configOptions['db_user_name'],$configOptions['db_password'],isset($configOptions['db_name'])?$configOptions['db_name']:'',$dbport);

                $link = TRUE;

            }



            if($link === FALSE) {

                $GLOBALS['log']->fatal("Could not connect to DB server ".$dbhost." as ".$configOptions['db_user_name'].". port " .$dbport . ": " . mysqli_connect_error());

                if($dieOnError) {

                    if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {

                        sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);

                    } else {

                        sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");

                    }

                } else {

                    return false;

                }

            }

Wednesday, January 4, 2012

Openshift with MySQL PHPMyAdmin

Click here to set up the Openshift server with Ubuntu.

Install Mysql for Openshift

/var/lib/gems/1.8/bin/rhc-ctl-app -a APPNAME -e add-mysql-5.1 -l EMAILID -p PASSWORD

Now Mysql is installed and access details will be obtained after running the above command.

Install PHPMyAdmin 

Download PHPMyadmin 

wget http://superb-dca2.dl.sourceforge.net/project/phpmyadmin/phpMyAdmin/3.4.8/phpMyAdmin-3.4.8-all-languages.zip

Go to application folder (local) and unzip the downloaded file

cd APP_FOLDER_NAME/phpunzip ../../phpMyAdmin-3.4.8-all-languages.zip

Rename the folder (name will be used to access the mysql [domainname/phpmyadmin_foldername])

mv phpMyAdmin-3.4.8-all-languages phpmyadmin

Update Configuration file for phpmyadmin

cd phpmyadmincp config.sample.inc.php config.inc.phpupdate config.inc.php$cfg['blowfish_secret']     = 'some value'; //COOKIE AUTH$cfg['Servers'][$i]['host'] = 'hostadress'; //host address obtained on mysql installation

Add files to be pushed to the server, and commit the changes

git add -Agit commit -m 'phpmyadmin setup'git push 

After pushing the changes to server, mysql can be accessed through
DOMAINNAME/phpmyadmin
e.g. http://sreejithblog-sreejithblog.rhcloud.com/phpmyadmin

Then login using the credentials obtained on MySql installation