Shantanu's Blog

Database Consultant

January 12, 2017

Dockerize php application

If you have CodeIgniter based php application, you can easily dockerize it. The docker command will look something like this...

docker run -p 80:80 -e MYHOST=172.31.11.168 -e MYUSER=root -e MYPASS=pass -e MYDB=livedbbox --restart always -d oksoft/phpapp

We are passing the database and password as environment variable that can be read from the database config file. You need this code in your database.php file.

# vi application/config/database.php

$db['default'] = array(
'dsn' => '',
'hostname' => getenv("MYHOST"),
'username' => getenv("MYUSER"),
'password' => getenv("MYPASS"),
'database' => getenv("MYDB"),

If your application is not able to write session data to a file, then you may need this change as well.

# vi application/config/config.php
# $config['sess_save_path'] = sys_get_temp_dir();

nginx and other config files along with Dockerfile can be found here...

https://github.com/shantanuo/docker-1/tree/master/nginx-php

Labels: docker, php

posted by shantanu # Thursday, January 12, 2017 0 comments

May 21, 2016

decouple application using docker compose

# install packages
yum install -y git mysql-server docker

curl -L https://github.com/docker/compose/releases/download/1.7.1/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
chmod +x /usr/local/bin/docker-compose

/etc/init.d/docker start

# create and copy public key to github to clone private repo

ssh-keygen -t rsa -b 4096 -C "user@gmail.com"

cat ~/.ssh/id_rsa.pub

# clone your private repository
git clone git@github.com:shantanuo/xxx.git

# create your custom my.cnf config file

mkdir -p /my/custom/
vi /my/custom/my.cnf
[mysqld]
sql_mode=''

# start docker containers as per yml file

/usr/local/bin/docker-compose up -d

# restore mysql data into container from host:
mysqladmin -h localhost -P 3306 --protocol=tcp -u root -ppasswd create livebox

mysql -h localhost -P 3306 --protocol=tcp -u root -ppasswd livebox < livebox.sql

# access mysql container from another container:

mysql -h mysql -uroot -ppasswd
_____

## use container ID of tutum/mysql:5.5 for e.g.
docker logs c7950eeab8e9
mysql -uadmin -pxmFShXB1Asgn -h127.0.0.1

# use the password to connect to 127.0.0.1 and execute commands:
mysql> grant all on *.* to 'root'@'%' identified by 'passwd' with grant option;
Query OK, 0 rows affected (0.00 sec)

# data only container to be used for mysql data
docker run -d -v /var/lib/mysql --name db_vol1 -p 23:22 tutum/ubuntu:trusty
docker run -d --volumes-from db_vol1 -p 3306:3306 tutum/mysql:5.5

Labels: aws, docker, mysql, mysql 5.1, mysql tips, php, usability

posted by shantanu # Saturday, May 21, 2016 0 comments

February 02, 2016

Starting with docker

1) Docker can be easily installed if you are using Amazon Linux. Here are the steps to install and run docker.

sudo yum update -y

sudo yum install -y docker

sudo service docker start

# applies on for AWS
sudo usermod -a -G docker ec2-user

2) Let's download a sample application from github and build it as docker image.

git clone https://github.com/awslabs/ecs-demo-php-simple-app

cd ecs-demo-php-simple-app

cat Dockerfile

docker build -t shantanuo/amazon-ecs-sample .

3) You can login to docker hub and push your image.

docer login

docker push shantanuo/amazon-ecs-sample

4) now you can pull it down and "activate" the contents floating in the docker image.

docker pull shantanuo/amazon-ecs-sample

docker run -p 80:80 shantanuo/amazon-ecs-sample

Labels: aws, linux tips, php, python, usability

posted by shantanu # Tuesday, February 02, 2016 0 comments

December 13, 2012

Drupal site in 4 clicks

Here is how you can have a Drupal site in 4 easy steps using cloud Formation service of Amazon Web Services.

1) Log in aws console and go to CloudFormation Management Console. Click on "Create Stack" button.

2) admin/ admin is the default DB user name and password. You can specify the instance type (for e.g. m1.small)

Within a few minutes the site is ready!

This however, does not give you shell access to the ec2 instance. You need to create the drupal site based on a template in order to gain the root access.

http://aws.amazon.com/cloudformation/aws-cloudformation-templates/

Use the template that says: "Single EC2 Instance with local MySQL database"
You will be allowed to specify the kye pair that can be used to connect to the ec2.

This applies to Drupal, Joomla, Wordpress etc. Amazon ec2 instances are a bit costlier than the shared hosting. But I think aws is a good choice considering the host of features offered, reliability, support and brand.

Labels: aws, drupal, mysql, php, unicode, usability

posted by shantanu # Thursday, December 13, 2012 0 comments

February 10, 2012

Import, Export and SQLize DynamoDB

You can use Hive to export data from DynamoDB to S3 bucket using Hadoop

In order to use hadoop, you need Elastic MapReduce instance. Click on "Create New Job Flow" and use defauls to create a new job called "My Job Flow". You will now get the Master Public DNS Name to connect to using "hadoop" as username. Once connected, type "hive" to get the command prompt where you can link to DynamoDB table.

hive> CREATE EXTERNAL TABLE hivereply (col1 string, col2 string, col3 string, col4 string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "reply",
"dynamodb.column.mapping" = "col1:Id,col2:ReplyDateTime,col3:Message,col4:PostedBy");

You can now use the table hivereply just like any other MySQL table. For e.g.
hive> select * from hivereply where col2 = '2012-01-31 23:09:46';

Amazon DynamoDB#DynamoDB Thread 2 2012-01-31 23:09:46 null

You can call the INSERT OVERWRITE command to write the data to an external directory. You can use this to create an archive of your Amazon DynamoDB data in Amazon S3.

hive> INSERT OVERWRITE DIRECTORY 's3://php-sdk-getting-started-aki-19/' SELECT * FROM hiveTableName;

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

Using Comparision Operator in dynamodb

If you want to query the data, you need to index it on Range key. For e.g. in the following "reply" table we have a primary key that is combination of Hash key and a Range key. Range key is nothing but the timestamp as on the reply was received. We can write a query that will return all the replies those are older than 7 days.
In this case the column "ReplyDateTime" column is indexed and we can use Comparison Operator.


require_once 'sdk.class.php';

$ten_days_ago = date('Y-m-d H:i:s', strtotime("-10 days"));

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id'            => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => $ten_days_ago                        ), // Range Key
        'Message'       => array( AmazonDynamoDB::TYPE_STRING  => 'DynamoDB Thread 2 Reply 1 text'   ),
        'PostedBy'      => array( AmazonDynamoDB::TYPE_STRING => 'User A'                            ),
    )
));

// Success?
print_r($add_response);


$seven_days_ago = date('Y-m-d H:i:s', strtotime("-7 days"));

$response = $dynamodb->query(array(
    'TableName' => 'reply',
    'HashKeyValue' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ),
// optional parameters
    'AttributesToGet' => array( 'ReplyDateTime', 'Message', 'PostedBy' ),
    'ConsistentRead' => true,
    'RangeKeyCondition' => array(
        'ComparisonOperator' => AmazonDynamoDB::CONDITION_LESS_THAN_OR_EQUAL,
        'AttributeValueList' => array(
            array( AmazonDynamoDB::TYPE_STRING => $seven_days_ago )
        )
    )
));

// 200 response indicates Success
print_r($response);

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

NoSQL support by AWS

Click on "Create Table" button on "DynamoDB" tab while using AWS web Management Console.

https://console.aws.amazon.com/dynamodb/home

Type table name "testme1" and choose Number as Primary Key Type. Hash attribute name can be "Id". Assuming that you have correctly installed AWS SDK, the following PHP code should add a key - value to the table testme1 table.


require_once 'sdk.class.php';

// Instantiate the class
$dynamodb = new AmazonDynamoDB();

$add_response = $dynamodb->put_item(array(
    'TableName' => 'reply',
    'Item' => array(
        'Id' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' ), // Hash Key
        'ReplyDateTime' => array( AmazonDynamoDB::TYPE_STRING => '2012-01-31 23:28:40' ), // Range Key
        'Message' => array( AmazonDynamoDB::TYPE_STRING => 'DynamoDB Thread 2 Reply 1 text' ),
        'PostedBy' => array( AmazonDynamoDB::TYPE_STRING => 'User A' ),
    )
));

// Success?
print_r($add_response);

_____

get item is obviously similar to put_item as shown above:


$get_response = $dynamodb->get_item(array(
    'TableName' => 'reply',
    'Key' => array(
        'HashKeyElement' => array( AmazonDynamoDB::TYPE_STRING => 'Amazon DynamoDB#DynamoDB Thread 2' )
    )
));

_____

In order to drop the table, use the following:


$delete_response = $dynamodb->delete_table(array(
    'TableName' => 'reply'
    )
);

_____

The following code will create a table called "reply":


$create_response = $dynamodb->create_table(array(
    'TableName' => 'reply',
    'KeySchema' => array(
        'HashKeyElement' => array(
            'AttributeName' => 'Id',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        ),
        'RangeKeyElement' => array(
            'AttributeName' => 'ReplyDateTime',
            'AttributeType' => AmazonDynamoDB::TYPE_STRING
        )
    ),
    'ProvisionedThroughput' => array(
        'ReadCapacityUnits' => 10,
        'WriteCapacityUnits' => 5
    )
));

_____

Here are 3 easy steps you may need to follow if you have not already downloaded and configured Software Development kit from Amazon.

# Download AWS SDK for PHP
svn co http://svn.github.com/amazonwebservices/aws-sdk-for-php.git AWSSDKforPHP
cd AWSSDKforPHP
_____

# copy sample to config file
mv config-sample.inc.php config.inc.php
_____

# add your actual key and secret found on "security credentials" page that can be found here...
# https://aws-portal.amazon.com/gp/aws/securityCredentials

vi config.inc.php

'key' => 'ABCD',
'secret' => 'XYZ',
'default_cache_config' => '/tmp/',

Labels: aws, noSQL, php

posted by shantanu # Friday, February 10, 2012 0 comments

June 14, 2010

Error reporting page

With the Apache webserver it’s possible to use custom directives for your error script, place this code into your .htaccess file (place the file into the site root):

ErrorDocument 400 /error.php?err=400
ErrorDocument 401 /error.php?err=401
ErrorDocument 403 /error.php?err=403
ErrorDocument 404 /error.php?err=404
ErrorDocument 500 /error.php?err=500

We use for the custom error script the most common HTTP errors.

The error.php file will display the custom error message based on the error number. It will also log the IP and referrer to a text file.

http://docs.google.com/View?id=dddjxz53_477dwhdwqck

Labels: php

posted by shantanu # Monday, June 14, 2010 0 comments

December 13, 2009

Using PHP to avoid filesort

The "order by" clause has one column in ascending order and the other descending.
Such queries are very slow and hence PHP needs to be used.

mysql> explain SELECT * FROM ABCD WHERE a=1 AND b=1 ORDER BY c DESC, d ASC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ABCD
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 2
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

So, we changed 1 query into 11 queries (10 parents per page) to make the page load happen faster, by getting rid of the filesort.

FOREACH($C_parent as $i => $c_id) {
$C_parent[$i] = SELECT SQL_CALC_FOUND_ROWS * FROM ABCD WHERE A=? AND B=? AND C=$c_id ORDER BY D ASC LIMIT 1;
}

Calculating the running total is another example where we need to use PHP as writing complex query is time consuming as well as very slow.

Labels: mysql, php

posted by shantanu # Sunday, December 13, 2009 1 comments

PHP at command prompt

1) Run php page with the ini settings
# php -c /etc/php.ini /var/www/html/TablesList.php
Warning: mysql_pconnect(): Access denied for user 'sink'@'localhost' (using password: YES) in /var/www/html/dbconnect.php on line 6
Error Occurred while communicating with the database!

2) Check Syntax error
# php -l /var/www/html/TablesList.php
No syntax errors detected in /var/www/html/TablesList.php

3) Strip the source code of comments and whitespace
# php –w example.php
Can be useful if you need to reduce the file size.

4) Run php code from the command line
# php -r "$foo = 'hello!'; $foo = strtoupper($foo); echo $foo;"
Directly run php code from the command line without using the php start and end tags.

Labels: php, shell script

posted by shantanu # Sunday, December 13, 2009 0 comments

October 18, 2009

PHP security tips

avoid SQL Injection

It can happen anywhere you have a form that the user can fill in and submit.
There is a PHP function to avoid the SQL Injection attack.

$query_ok = "SELECT * FROM users WHERE username = 'mysql_real_escape_string($injection_string)'";

Real escape strings will replace all single quote and double quote character with the escaped string \

$new_user=mysql_real_escape_string($_GET["injection_string"]);

$query_ok = "SELECT * FROM mysql.user WHERE User = '" . "$new_user" ."' limit 1";
echo $query_ok;
$result=mysql_query($query_ok) or die("some errror");

_____

If you need to use SMTP server to send mail, here is a nice writeup for the same.

http://9lessons.blogspot.com/2009/10/send-mail-using-smtp-and-php.html

Labels: php, php tips

posted by shantanu # Sunday, October 18, 2009 0 comments

October 09, 2009

Naming my baby

Append the variable name with the variable type. for e.g. instead of just saying FirstName you can call it strFirstName like this...

$strFirstName = ’sonny’;
$intAge = 5;

There are 2 types of Case styles:

Pascal Case -> ThisIsPascalCase (Also Called Upper Camel Case)

Camel Case -> thisIsCamelCase (Also Called Lower Camel Case)

_____

Zend Framework has defined the following guidelines for naming variables and function names.

Classes – PascalCase (UpperCamelCase)
Class names must match the name of the directory on the file system that contains the class file. If you have a class file named Car.php in the path ‘/Models/Car.php’ then the class name would have to be Models_Car.

Functions and Methods – camelCase
Names must describe behavior of function or method.
Methods that are declared with private or protected visibility modifier must start with an _.

Constants – ALL_CAPS

Labels: php

posted by shantanu # Friday, October 09, 2009 0 comments

PHP memory management

Do not forget to delete the data stored in the buffer.

$long_result = mysql_query('SELECT * FROM `posts`');
//Output $long_result
unset($long_result);
$var = 'Lorem ipsum dolor sit amet. Lorem ipsum dolor sit.';
echo $var;
unset($var);
?>

Labels: php

posted by shantanu # Friday, October 09, 2009 0 comments

php.ini file setting

There are many people who talk about my.cnf setting for mysql. But I do hardly hear anything about php.ini setting. Here are a few tips...

asp_tags = Off
display_errors = On
display_startup_errors = Off
log_errors = Off
safe_mode = Off
register_globals = Off
short_open_tag = Off
sql.safe_mode = Off
max_input_time = 60
magic_quotes_gpc = Off
track_errors = Off

Labels: php

posted by shantanu # Friday, October 09, 2009 0 comments

July 30, 2009

PHP filters

http://www.devshed.com/c/a/PHP/Using-Filters-in-PHP-5/3/

This devshed article taught how to use filters in PHP 5

$min = 1;
$max = 99;
$input = 101;

if(filter_var($input, FILTER_VALIDATE_INT, array("options" => array("min_range" => $min, "max_range"=> $max ))) === FALSE)

{
echo 'Error: Input must be a value between 1 and 99.';
}

else

{
echo 'Input is correct';
}

Labels: php

posted by shantanu # Thursday, July 30, 2009 0 comments

July 15, 2009

Inserting multiple rows

Insertion of multiple rows comes about often in batch jobs, database migrations and handling table relationships. A naive approach, via PHP, would be to loop over the data to be inserted, inserting one row at a time. Suppose the data is already properly filtered and quoted:

foreach( $data as $row ) {
$query = "INSERT INTO `test_table` (user_id,content) VALUES ("
. $row['user_id'] . "," . $row['content'] . ")";
mysql_query($query);
}

Depending on the amount of rows to be inserted, this can be a costly process. A better approach would be to concatenate the values into one insert query and then execute it:

$values = array();
foreach( $data as $row ) {
$values[] = "(" . $row['user_id'] . "," . $row['content'] . ")";
}
if( !empty($values) ) {
$query = "INSERT INTO `test_table` (user_id,content) VALUES "
. implode(',',$values);
mysql_query($query);
}

A single query completes much faster than looping through multiple queries. At 2560 rows inserted, it took the loop ~36 seconds to complete, yet the single query took just 0.14 seconds.

Sounds Interesting!

http://www.techfounder.net/2009/05/14/multiple-row-operations-in-mysql-php/

Labels: mysql tips, php

posted by shantanu # Wednesday, July 15, 2009 0 comments

May 20, 2009

PHP and shell script

You can call the shell script from PHP page and also check if it was run successfully.

?php
passthru('xfer_rsync.sh',$returnvalue);

if ($returnvalue != 0){
//we have a problem!
//add error code here
}else{
//we are okay
//redirect to some other page
}
?

Read More

Labels: php, shell script

posted by shantanu # Wednesday, May 20, 2009 0 comments

November 10, 2008

Introduction to PEAR - Pager

Pagination is a frequent requirement in web development projects. Most PHP developers must have already implementated paging in one form or other in their projects. In this post we will see how to add pagination the easy way using PEAR’s Pager class. Use the following command to install the require library...

pear install Pager

Now you can use the class in your code as shown below:

/* Include the Pear::Pager file */
require_once ('Pager/Pager.php');

/* Replace this with your database details */
$connection = mysql_connect("localhost", "root", "");
mysql_select_db("calls", $connection);

/* First we need to get the total rows in the table */
$result=mysql_query("SELECT count(*) AS total FROM jaip2", $connection);
$row = mysql_fetch_array($result);

/* Total number of rows in the logs table */
$totalItems = $row['total'];

/* Set some options for the Pager */
$pager_options = array(
'mode' => 'Sliding', // Sliding or Jumping mode. See below.
'perPage' => 10, // Total rows to show per page
'delta' => 4, // See below
'totalItems' => $totalItems,
);

/* Initialize the Pager class with the above options */
$pager = Pager::factory($pager_options);

list($from, $to) = $pager->getOffsetByPageId();
/* The MySQL 'LIMIT' clause index starts from '0',
so decrease the $from by 1 */
$from = $from - 1;

/* The number of rows to get per query */
$perPage = $pager_options['perPage'];
echo "";
$result = mysql_query("SELECT id, inno FROM jaip2 LIMIT $from , $perPage", $connection);
while($row = mysql_fetch_array($result))
{
/* Do something with the query results */
echo "";
echo "";
echo "";
echo "";
}

echo "
".$row[0]." ".$row[1]."
";

$topass = "select id, inno from jaip2 limit $from, $perPage";
$newpass = explode("limit", $topass);
$newsend = ($newpass[0]);
echo "
";
/* display the data */
echo $pager->links;
?>

As you can see the page has a link that says "Export to excel" and calls the page testexport.php page. The source code of that page is given below...

/* Replace this with your database details */
$connection = mysql_connect("localhost", "root", "");
mysql_select_db("calls", $connection);

$string = $_GET["toimport"];

$result = mysql_query("$string", $connection);

$tsv = array();
$html = array();

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[] = implode("\t", $row);
$html[] = "" .implode("", $row) . "";
}

$tsv = implode("\r\n", $tsv);
$html = "" . implode("\r\n", $html) . "
";

$fileName = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");

echo $tsv;

// echo $html;

?>

Read more...

http://www.codediesel.com/php/simple-pagination-in-php/

Labels: pear, php

posted by shantanu # Monday, November 10, 2008 0 comments

January 29, 2008

php functions, tips and syntax

Phil Thompson has written about "7 PHP functions that saved his life".

http://imgiseverything.co.uk/2008/01/18/7-php-functions-that-saved-my-life/

I found the tips in the comments more valuable than the article!

1) You know you can combine the echo and the exit() call:
exit(”x is greater than y”);
When debugging forms, I find it very handy to write a little helper function that spits out the contents of the $_POST array wrapped in PRE tags, then call that:
die(post_contents());

2) The substr() is often not needed if you only need the first or the nth character, you can use the curly-braces syntax in those situations:
For example: substr($var, 0, 1) is the same as $var{0}

3) // instead of this:
if(isset($variable) && $variable != ”){}
// you can do this:
if(!empty($variable)){}
// it’s the same

4) switching to python from php was how i really saved my life. Try that language and its frameworks, don’t die on php! :P

Labels: php

posted by shantanu # Tuesday, January 29, 2008 1 comments

November 02, 2007

6 PHP coding tips by Alex

I really liked the Alex Netkachov approach of "when there is no variable, then there is no problem."

http://www.alexatnet.com/node/100

1) We are too much dependent on variables and I will be happy to avoid them where ever I can. The reason why we are so liberal while using them is that we have seen everyone using it for almost everything, even if it's not needed. I have seen and used the OR keyword as suggested by Alex. Where? mmmm... yes, now I remember, while connecting to the database.

mysql_select_db('database_name', $g_link) or die('Could not select database.');

But I never realized that the OR keyword can be used anywhere in the script as well. Like this...

fwrite($h, 'some text') or log('Writing failed');

Fantastic. Point noted!

2) I did actually googled for the syntax of ternary operator a while back. I wanted to use it in the function because my function was getting too lengthy to manage. So here it is...

$message = ($age < 16) ? 'Welcome!' : 'You are too old!';

And what about the second method of making the code shorter?

$message = 'You are too old!';
if ($age < 16) {
$message = 'Welcome!';

Clever!

3) Though 'while' looks more logical and readable, 'for' still wins!

Everyone will agree with the points 4, 5 and 6.

So overall 100 marks out of 100!
Thank you Alex.

Labels: php

posted by shantanu # Friday, November 02, 2007 0 comments

Shantanu's Blog

January 12, 2017

Dockerize php application

May 21, 2016

decouple application using docker compose

February 02, 2016

Starting with docker

December 13, 2012

Drupal site in 4 clicks

February 10, 2012

Import, Export and SQLize DynamoDB

Using Comparision Operator in dynamodb

NoSQL support by AWS

June 14, 2010

Error reporting page

December 13, 2009

Using PHP to avoid filesort

PHP at command prompt

October 18, 2009

PHP security tips

October 09, 2009

Naming my baby

PHP memory management

php.ini file setting

July 30, 2009

PHP filters

July 15, 2009

Inserting multiple rows

May 20, 2009

PHP and shell script

November 10, 2008

Introduction to PEAR - Pager

January 29, 2008

php functions, tips and syntax

November 02, 2007

6 PHP coding tips by Alex

Archives