Getting started with PHP PDO and SQLITE

In android i used to have sqlite db which is light weight and sufficiently smart for smaller operations

PHP too supports the SQLITE db operations so i searched for some time and found certain code to make use of the SQLITE in php

 

First thing i tried to create the db file, on php.net site I got following code snippet

<?php
if ($db = sqlite_open('mysqlitedb', 0666, $sqliteerror)) { 
    sqlite_query($db, 'CREATE TABLE foo (bar varchar(10))');
    sqlite_query($db, "INSERT INTO foo VALUES ('fnord')");
    $result = sqlite_query($db, 'select bar from foo');
    var_dump(sqlite_fetch_array($result)); 
} else {
    die($sqliteerror);
}
?>

After I executed the code with sample values and some data, when i checked in SQLite db browser, it gave me version issue then i came across fact that This function only support database of SQLite 2 or below. For SQLite 3, you must use PDO.

So i started searching on SQLite with PDO

Finally some sort of working code I got which can create the db file execute some statements and finally that can be browsed via the sqlite browser below is the code with comments in between.. so

Enjoy

In following code explanation given for how to create sqlite db, create some tables, insert data, drop table, access the data.. and so on

The inline commenting is good enough to understand the code, so not writing here in much detail…

 

<?php
 try {
 // Create (connect to) SQLite database in file
 $file_db = new PDO('sqlite:users.sqlite3');
 // Set errormode to exceptions
 $file_db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 /**************************************
 * Create tables *
 **************************************/
 // Create table users with two colums firstname and last name
 $file_db->exec("CREATE TABLE IF NOT EXISTS tbl_users (
 id INTEGER PRIMARY KEY, 
 firstname TEXT, 
 lastname TEXT 
 )");
 
 /**************************************
 * Set initial data *
 **************************************/
 // Array with some test data to insert to database 
 $users = array(
 array('firstname' => 'Abhijeet',
 'lastname' => 'K'),
 array('firstname' => 'Testing firstname',
 'lastname' => 'Testing lastname'),
 array('firstname' => 'Dinesh',
 'lastname' => 'Patil')
 );
 // Prepare INSERT statement to SQLite3 file db
 $insert = "INSERT INTO tbl_users (firstname, lastname) 
 VALUES (:firstname, :lastname)";
 $stmt = $file_db->prepare($insert);
 
 // Bind parameters to statement variables
 $stmt->bindParam(':firstname', $firstname);
 $stmt->bindParam(':lastname', $lastname);
 
 
 // Loop thru all users data and execute prepared insert statement
 foreach ($users as $u) {
 // Set values to bound variables
 $firstname = $u['firstname'];
 $lastname = $u['lastname'];
 // Execute statement
 $stmt->execute();
 }
 
 // Select all data from users tabel and display 
 $result = $file_db->query('SELECT * FROM tbl_users');
 
 foreach($result as $row) {
 echo "Firstname: " . $row['firstname'] . "&nbsp;";
 echo "Lastname: " . $row['lastname'] . "<br>";
 
 echo "<br>";
 }
 
 
 /**************************************
 * Drop tables *
 **************************************/
 
 // Drop table users from file db
 $file_db->exec("DROP TABLE tbl_users");
 // Close file db connection
 $file_db = null;
 
 }
 catch(PDOException $e) {
 // Print PDOException message
 echo $e->getMessage();
 }
?>

How to execute PHP code on existing html page?

The way to execute PHP on a .html page is to modify your .htaccess file. This file may be hidden, so depending upon your FTP program you may have to modify some settings to see it. Then you just need to add this line for .html:

AddType application/x-httpd-php .html

If you only plan on including the PHP on one page, it is better to setup this way:
<Files abc.html>
AddType application/x-httpd-php .html
</Files>
This code will only make the PHP executable on the abc.html file, and not on all of your html pages.

Create and mount an EBS volume to your AMAZON instance

In this series so far I explained to set up basic PHP application and use of RDS for relational databases.

For the same couple of times we faced the issue that somehow the instance health check url returned false and as a result our instance got terminated, so we need to again re upload the entire data associate elastic ip etc that is somewhat hectic process

So we googled for the solution and came to know that if instance got restarted the local file system will be lost and you cant retain the data, the solution is to use the persistent storage so even in case of failure the data should remain.

Couple of options are available, some suggested to use bucket and some advised to use the EBS volumes.

I followed the EBS volumes

An informative guide can be found HERE 

Actually its pretty straight forward if you have some advanced linux knowledge for filesystems.

First i created the empty EBS volume, make sure for the availability zone as one volume may not available to other zones.

Ater that you need to associate the volume to your instance use between the range sdc to sdp as sda and sdb are already in use

After assigning to specific instance you can ssh to your instance and see its attached.

Before mounting for first time you need to format the ebs volume by following command

sudo mkfs.xfs /dev/sdc

Where “sdc” is the point of attachment at the time of attaching to instance

After create an empty directory to mount volume with  mkdir command

And final step is to actually mount it

mount /dev/xvdp /wb1

wb1 is the directory created to mount the drive

 


					

How to configure a basic php application on amazon ec2

The most important thing is you need your valid amazon login details

Login with the credentials and you will land on the home screen, dont get confused with various options to setup working php mysql application you need a few options.

The first most important thing is to generate a ssh key and import in the console A good guide on how to generate can be found HERE

Once imported name the key you can remember say php or mykey etc I use php.

Next you can go to Elastic Beanstalk section, select launch new environment.

Choose the required parameters the most important is container type

Select the one which suits you for your php mysql I selected 32 bit amazon linux running php 5.3

Check the parameters for most of columns dont change any but to keep the default one

After filling all the details your environment is can be seen on the same screen, wait for the color or environment to change to green which indicates ALL IS WELL…

You are now almost 50% done till this step.

Go to environment url to check the sample code working, some welcome app or sample application screen

If not then there is something wrong and you can check the environment logs or events to check status.

Next go to EC2 section and your newly created instance is waiting for you.

Its now time to access the newly created instance to upload some files.

Allocate one Elastic Ip from the EC2->Elastic IP section to your instance.

In the instance details you can now see the IP allocated. Just to confirm type that IP address in browser and you should see the same app page that was there while checking the environment url.

Next step is set up putty to access the instance.

Convert the generated key in first step to PPK (Putty Private Key) format. An illustrative guide can be found HERE.

For a Quick revision we followed steps as

  1. Generated key pair
  2. Created instance with default environment
  3.  Imported keys to ec2  environment
  4.  Edited instance to allocate the IP to allow SSH
Next step is to actually ssh your newly created instanceGo to instance section under EC2->instances and click on your instance and note down the elastic IP associated.
Now Open the Puty for SSH connection
In connection details put your elastic IP and under SSH -> Auth section browse the ppk file you created.
When prompted enter user name as ec2-user and password as what you selected while creating your key.

Bingo…….

If  ALL IS WELL you can see the favorite black screen for linux console.. Its now your play ground.. and now time to do some settings.
Check for php version and apache version for curiosity, Its not necessary but to start with or to brush your knowledge if you are accessing it after long time like me.
The default apache location is /etc/httpd/
and default webroot will be /var/www/html
Edit some files or create a new one..using VI editor and check on front end..

Database Access Wrapper In Java

Recently started doing some homework for java, I started with few simple concepts like JSP and servlets,

Having experience in PHP the web concepts are clear. Now its time to check whether the same concepts can work in JSP and Servlets or not

I started with simple JSP and Servlet Application for a common functionality forums.

(Github repo is created will commit on regular basis as i move ahead )

I first decided to do one db access class, that will hold the connection and some common functions so each time i dont need to write those repeat lines for open connection and close.

I did some R n D and come up with the following file

DBAccess.java

And sample implementation can be found here

CommonFunctions.java

 

I know I am somewhat fresher to this concept but trying to improve my knowledge in the concepts.

 

If you have any better ideas then please do share with me by putting comments or by dropping the email at abhijeetgk@gmail.com

 

How to configure the amazon relational database service to use in amazon cloud applications

This is new series where in I will explain step by step configuration of the amazon cloud servers to host and run php mysql applications

 

In this session I will first explain to use ARDS (Amazon Relational Database Service) and then binding the ARDS instance to get accessible from your app.

 

The main requirement is you should have AWS account.  Login with your credentials and go ahead to create your first ARDS db

First select RDS tab from the top menu

Then select launch new instance

Select available instances like mysql,oracle,MSSQL etc

Fill in the necessary details like instance class, storage, db identifier, username, password etc

 

 

Once selected all the necessary options the DB instance is now seen on RDS dashboard.

Next Step is to authorize access to your database

Select the db security group from the left hand side options

Possible select the default security group and grant access for application or the specific IP to access the same.

After authorized access you can now access your database

Note the EndPoint url username and password

With this credentials and security settings you can now connect to your db instance.

Fancy Gradient Buttons for Android

Design elements for android is some what complex task in the beginning, but as you explore the internet you will find
Default buttons for android code not so cool so I decided to use the image button instead of regular buttons, then too for some low end devices that is not looking good

After searching for internet I got concept to use the Stylesheet properties for buttons HERE

Its pretty simple and straight forward

First you need to define the xml for color gradient effect. I choose Red color first

File 1 res/drawable/btn_red.xml

<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
    <item android:state_pressed="true" >
        <shape>
            <solid
                android:color="#ef4444" />
            <stroke
                android:width="1dp"
                android:color="#992f2f" />
            <corners
                android:radius="3dp" />
            <padding
                android:left="10dp"
                android:top="10dp"
                android:right="10dp"
                android:bottom="10dp" />
        </shape>
    </item>
    <item>
        <shape>
            <gradient
                android:startColor="#ef4444"
                android:endColor="#992f2f"
                android:angle="270" />
            <stroke
                android:width="1dp"
                android:color="#992f2f" />
            <corners
                android:radius="3dp" />
            <padding
                android:left="10dp"
                android:top="10dp"
                android:right="10dp"
                android:bottom="10dp" />
        </shape>
    </item>
</selector>

After this file you need to define the style resources for button text

Include following style tag in res/values/strings.xml

<style name="ButtonText">
 <item name="android:layout_width">fill_parent</item>
 <item name="android:layout_height">wrap_content</item>
 <item name="android:textColor">#ffffff</item>
 <item name="android:gravity">center</item>
 <item name="android:layout_margin">3dp</item>
 <item name="android:textSize">30dp</item>
 <item name="android:textStyle">bold</item>
 <item name="android:shadowColor">#000000</item>
 <item name="android:shadowDx">1</item>
 <item name="android:shadowDy">1</item>
 <item name="android:shadowRadius">2</item>
</style>

Finally Include the button code where you want the fancy gradient button to appear as follows

<Button
 android:id="@+id/button1"
 style="@style/ButtonText"
 android:layout_width="match_parent"
 android:background="@drawable/btn_red"
 android:text="RedButton" >
</Button>

Note the background attribute and style attribute .