Monday, April 1, 2013

MySQL Database Connection at OpenShift Redhat Cloud using PDO Class

I was very unhappy when my code was correct but could not connect to my mysql database in openshift. But after trying some tricks it got to work. So I will share some tips that you can also put to your PHP MySQL application in openshift.
First thing to be noted is that when you create a PHP Application in OpenShift you will get some environmental variable. Which you can view them by using echo $ENV_NAME in the shell.
Some of MYSQL DB Environmental variables in openshift clouds are follows.
OPENSHIFT_MYSQL_DB_HOST
OPENSHIFT_MYSQL_DB_PORT
OPENSHIFT_MYSQL_DB_USERNAME
OPENSHIFT_MYSQL_DB_PASSWORD
OPENSHIFT_MYSQL_DB_SOCKET
OPENSHIFT_MYSQL_DB_URL
          
You can Find more about it here OpenShift Variables

Considering we have database name 'gitspot' and have a table name having demo where there is a field 'name' and has a record 'RedHat Openshift'
Now create a php file conf.php which contains all the Database Host Port etc and the way to connect using PDO Class.
<?php
        define('DB_HOST', getenv('OPENSHIFT_MYSQL_DB_HOST'));
        define('DB_USER',getenv('OPENSHIFT_MYSQL_DB_USERNAME'));
        define('DB_PASS',getenv('OPENSHIFT_MYSQL_DB_PASSWORD'));
        define('DB_BASE','gitspot');
        define('DB_PORT',getenv('OPENSHIFT_MYSQL_DB_PORT')); 
function mysqlConnector(){
    $dsn = 'mysql:dbname='.DB_BASE.';host='.DB_HOST.';port='.DB_PORT;
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
}
         ?>

Now include that in your app.php

<?php
 try {
                require_once 'conf.php';
                $conn = mysqlConnector();
                $stmt = $conn->prepare('SELECT * FROM demo');
                $stmt->execute();

                while($row = $stmt->fetch()) {
                        print_r($row);          
                }


        } catch(PDOException $e) {
                echo 'ERROR: ' . $e->getMessage();
        }


?>

The result of https://app-appname.rhcloud.com/app.php

Array ( [name] => RedHat Openshift [0] => RedHat Openshift ) 


That is all how you can connect to mysql database in RedHat OpenShift cloud.