Learn In Detail: August 2018

Wednesday 29 August 2018

Pagination in PHP, MYSQL serverside API

Pagination is a useful concept when there is a requirement of retreiving large chunks of data from server. Pagination reduces the load on server and also is user friendly as the end user sees less data in a go.

So here I am using PHP to retreive rows from MYSQL using pagination.

#retreive start and limit values 
if(isset($_POST["start"])) {
        $start = $_POST["start"]-1;
} else {
        $start = 0;
}

##number of rows to be retreived from starting point, can be 10,20,30...
if(isset($_POST["limit"])) {
        $limit = $_POST["limit"] ;
} else {
        $limit = 10 ; //get 10 as default from starting point
}

#set header
header('Content-Type: application/json');

#connection to database
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

#form query
$sql = "Select * from mytable LIMIT $start, $limit ";     ##this query retreives rows from mytable starting from start point and upto $limit rows

#execute query
$retval = mysqli_query( $conn, $sql );

#fetch rows if query executed succesfully else return error
if(! $retval ) {
        $data = array("status"=>"failure","message"=>"Log fetch error!!");
        echo json_encode( $data );
        mysqli_close($conn);
        die('Could not fetch logs: ' . mysqli_error());
}
else {
        while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) {
              $data[] = $row;
        }

##send response to client
                $data = array('status'=>'success','message'=>'Log fetched.','records'=>$data);

#close connection
        mysqli_close($conn);
        echo json_encode( $data );
}

This script will fetch rows from database based on start and limit variables. If these variables are not passed during client request it sets to default of 0 to 10 rows.

Happy Coding.

Monday 27 August 2018

Crontabs - Job scheduler explained

Open crontab using below command:

crontab -e

Its syntax is like below

* * * * * command/script to be executed/

# Example of job definition:

# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed

Listing of Cronjobs can be done using below command:

crontab -l

Examples:

1. Take backup every day midnight.

0 0 * * * /my/path/to/script/backup.sh

2. Start mongo server @reboot

@reboot /usr/bin/mongod

3. Mysql database backup every sunday

* * * * 0 /usr/bin/mysqldump -u root -p{root123} userdb2 &gt; /home/nagaraju/myfile_$(date +\%Y-\%m-\%d).sql&nbsp;&nbsp; #every week on Sunday<br />

Friday 24 August 2018

Saving unicode or utf8 data using PHP-MYSQL

Saving data in MYSQL is almost common in every website. When it comes to unicode date there is a bit of overhead that needs to be taken care of. I am listing those settings step by step.

1. Set table's collation to "utf8_general_ci"

 ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

2. Set the column's collation to "utf8_general_ci"

 ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

3. In PHP use the below code while the data is being inserted into the table.

 mysqli_query($conn,"SET names 'utf8'");

Thursday 23 August 2018

HTTPS to HTTP Ajax Request, Same Origin Policy.

Often there are times where we need to make a request that might not obey the Same Origin Policy . So here I am going to address Different Protocal issue in Same Origin Policy. Suppose we are making a http request from a server with https protocal like following,

$.ajax({
        url: 'http://MyAjaxHTTP Path',
        type: 'POST',
        data: 'param1=value1&param2=value',
        header: "application/x-www-form-urlencoded",

The above request cannot be made because it violates same origin policy. So we have to write a layer code between Javascript and the HTTP server that directly interacts with HTTP. So first we have to choose a server side language for this. I am choosing PHP.

In PHP (phplayer.php):

$param1 = $_POST["param1"];
$param2 = $_POST["param2"];
$data = array(“param1”=>$param1, "$param2"=>$param2);

$data = http_build_query($data);
header('content-type: application/json');
$context_options =  array(
        'http' => array(
                'method' => 'POST',
                'header' => 'Content-type: application/x-www-form-urlencoded',
                'content' => $data
        ));
$context  = stream_context_create($context_options);

//notice that our actual HTTP URL is called here
$result = file_get_contents("http://MyAjaxHTTPPath", false, $context);
echo $result;

In Javascript everything remains same except that we have to make a call to our layer php code that will actually make a http request and get back the response.

$.ajax({
        url: 'phplayer.php',
        type: 'POST',
        data: "param1=value1&param2=value",
        header: "application/x-www-form-urlencoded",

Tuesday 21 August 2018

Installing Mysql in Ubuntu 16.06

To install mysql manually in linux, see steps below:

Installing MySQL 5.5.51 on Ubuntu 16.06

Uninstall any existing version of MySQL
```
 
```
```
sudo rm /var/lib/mysql/ -R
```
```
 
```
Delete the MySQL profile
```
 
```
```
sudo rm /etc/mysql/ -R
```
```
 
```

Automatically uninstall mysql

sudo apt-get autoremove mysql* --purge

sudo apt-get remove apparmor

Download version 5.5.51 from MySQL site

wget https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz

Add mysql user group
```
sudo groupadd mysql
```
```
 
```
Add mysql (not the current user) to mysql user group
```
sudo useradd -g  mysql mysql
```
```
 
```

Extract mysql-5.5.51-linux2.6-x86_64.tar.gz to /usr/local

cd /usr/local
sudo tar -xvf mysql-5.5.49-linux2.6-x86_64.tar.gz

Create mysql folder in /usr/local

sudo mv mysql-5.5.49-linux2.6-x86_64 mysql

Set mysql directory owner and user group
```
cd mysql
sudo chown -R mysql:mysql *
```
```
 
```
Install the required lib package
```
sudo apt-get install libaio1
```
```
 
```

Execute mysql installation script

sudo scripts/mysql_install_db --user=mysql

Set mysql directory owner from outside the mysql directory
```
sudo chown -R root .
```
```
 
```
Set data directory owner from inside mysql directory
```
sudo chown -R mysql data
```
```
 
```

Copy the mysql configuration file

sudo cp support-files/my-medium.cnf /etc/my.cnf

Start mysql

sudo bin/mysqld_safe --user=mysql &
sudo cp support-files/mysql.server /etc/init.d/mysql.server

Initialize root user password

sudo bin/mysqladmin -u root password '111111'

Start mysql server
```
sudo /etc/init.d/mysql.server start
```
```
 
```
Stop mysql server
```
sudo /etc/init.d/mysql.server stop
```
```
 
```
Check status of mysql
```
sudo /etc/init.d/mysql.server status
```
```
 
```

Enable myql on startup

sudo update-rc.d -f mysql.server defaults

Disable mysql on startup (Optional)

sudo update-rc.d -f mysql.server remove

Add mysql path to the system

sudo ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

Now directly use the command below to start mysql
```
mysql -u root -p 
```

PS: One needs to reboot in order for the changes to take place.

Labels