My Notes

Wednesday, October 5, 2016

Simple Pagination With PHP and MySQL

This is my personal note to create simple pagination using php and mysql. I used mysqli object oriented style for future php support. Version MySQL and PHP are:

MySQL Version 5.5
PHP Version 5.6

To create your own demo, create your database and populate it.
CREATE TABLE `kci_logipv4` (
`logdate` datetime DEFAULT NULL,
`logipv4` int(11) unsigned DEFAULT NULL,
`logmsg` varchar(1000) DEFAULT NULL,
`logfrom` int(11) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT
 INTO `kci_logipv4` VALUES ('2016-09-28 
23:22:11',3104583205,'SSH',1),('2016-09-28 
23:22:11',3104583205,'SSH',1),('2016-09-29 
09:07:50',1347119510,'SSH',1),('2016-09-29 
09:07:51',1347119510,'SSH',1),('2016-09-29 
20:28:27',3743330746,'SSH',1),('2016-09-29 
20:28:28',3743330746,'SSH',1),('2016-09-29 
20:59:51',1033072143,'SSH',1),('2016-09-29 
20:59:53',1033072143,'SSH',1),('2016-09-29 
21:06:55',1907273049,'SSH',1),('2016-09-29 
21:06:57',1907273049,'SSH',1),('2016-09-30 
03:20:08',3075559489,'SSH',1),('2016-09-30 
03:20:09',3075559489,'SSH',1),('2016-09-30 
11:47:19',1323303364,'SSH',1),('2016-09-30 
11:47:21',1323303364,'SSH',1),('2016-09-30 
21:59:33',3565240261,'SSH',1),('2016-09-30 
21:59:35',3565240261,'SSH',1),('2016-10-01 
03:09:18',1794631394,'SSH',1),('2016-10-01 
03:09:18',1794631394,'SSH',1),('2016-10-01 
10:02:35',2746017717,'SSH',1),('2016-10-01 
10:02:36',2746017717,'SSH',1),('2016-10-01 
10:29:18',1963359914,'SSH',1),('2016-10-01 
10:29:19',1963359914,'SSH',1),('2016-10-01 
10:57:30',2000423175,'SSH',1),('2016-10-01 
10:57:31',2000423175,'SSH',1),('2016-10-01 
21:33:06',1757971329,'SSH',1),('2016-10-01 
21:33:07',1757971329,'SSH',1),('2016-10-02 
21:00:31',1033072141,'SSH',1),('2016-10-02 
21:00:34',1033072141,'SSH',1),('2016-10-03 
02:35:10',2065638212,'SSH',1),('2016-10-03 
02:35:11',2065638212,'SSH',1),('2016-10-03 
06:18:41',3754290014,'SSH',1),('2016-10-03 
06:18:43',3754290014,'SSH',1),('2016-10-03 
15:14:53',1912501632,'SSH',1),('2016-10-03 
15:14:53',1912501632,'SSH',1),('2016-10-04 
00:04:44',3281659836,'SSH',1),('2016-10-04 
00:04:45',3281659836,'SSH',1),('2016-10-04 
01:45:32',1168426137,'SSH',1),('2016-10-04 
01:45:33',1168426137,'SSH',1),('2016-10-04 
12:04:05',2065638165,'SSH',1),('2016-10-04 
12:04:05',2065638165,'SSH',1),('2016-10-04 
13:11:16',457715326,'SSH',1),('2016-10-04 
13:11:16',457715326,'SSH',1),('2016-10-04 
16:50:40',1839072489,'SSH',1),('2016-10-04 
16:50:40',1839072489,'SSH',1),('2016-10-05 
09:20:48',1760484871,'',1),('2016-10-05 09:20:48',1760484871,'',1);

Here is the kci_logread.php code you can view it live
<?php

/*
database: database
table: kci_logipv4
logdate datetime
logipv4 int(11)
logmsg varchar(1000)
logfrom int
*/

// host, user, password, database, [port]
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
 die('Internal Server Error maybe database');
}

//This is the page number want to display
//retrieve from user navigation via method get or post
$page = 1;
if(isset($_GET["page"])){
 $page = intval($_GET["page"]); // sanitize input
}
if(isset($_POST["page"])){
 $page = intval($_POST["page"]); // sanitize input
}
//This is the number of results displayed per page
$page_rows = 15;

//count all result
//must equal to query to display data
$res = $mysqli->query("select Count(*) as total from kci_logipv4");
$rows = $res->num_rows;
//number of data available
$total=0;
if($rows) {
 $row = $res->fetch_assoc();
 $total = $row['total'];
}

//this is which first row should be retrieve
$start = ($page_rows * $page) - $page_rows;
//query to display data
//read $page_row data start from row $start
$res = $mysqli->query("SELECT * FROM kci_logipv4 order by logdate desc Limit $start, $page_rows ");
//number of data to display
$rows = $res->num_rows;

//This tells us the page number of our last page
$last = ceil($total/$page_rows);

?>
<html>
<body>
Server Farm Info
<?php
if ($res) {
 echo "Total: $total";
 if ($rows>0) {
?>
<table border="1">
 <tr>
 <th>From</th>
 <th>Datetime</th>
 <th>IP</th>
 <th>Comment</th>
 </tr>
<?php
 while ($row = $res->fetch_assoc()) {
?>
 <tr>
 <td>Server farm <?php //echo $row['logfrom']; ?></td>
 <td><?php echo $row['logdate']; ?></td>
 <td align="center"><?php echo long2ip($row['logipv4']); ?></td>
 <td><?php echo $row['logmsg']; ?></td>
 </tr>
<?php
 }
 } else {
 echo "No Data";
 }
?>
</table>
<?php
 if ($last>1) {
 echo "";
 if ($page>1) {

            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=1\">First</a> ";

}
 if ($page>2 && $page<=$last) {
 echo "... ";
 }
 if ($page>1) {

   
         echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], 
ENT_QUOTES, "utf-8")."?page=".($page-1)."\">".($page-1)."</a> 
";

}
 echo "$page ";
 if ($page+1<=$last) {

   
         echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], 
ENT_QUOTES, "utf-8")."?page=".($page+1)."\">".($page+1)."</a> 
";

}
 if ($page+1<$last) {
 echo "... ";
 }
 if ($page<$last) {

            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=$last\">Last</a> ";

}
 echo "| Number of pages: $last ";
} else {
 echo "Query Fail";
}
?>
</body>
</html>

Tuesday, October 4, 2016

Odoo 9: restore using curl

Additional requirement:

zip & unzip
curl

Install additional requirement

# apt-get install zip unzip curl

To restore Odoo 9 via web front end using curl, you need to parse 4 parameters, they are:

master password: master_pwd
name: name
backup file (in zip): backup_file
restore option (copy or move): copy=true

Regarding to web front end we can use copy option for almost restoring Odoo 9.

In order to avoid conflicts between databases, Odoo needs to know if this database was moved or copied. If you don't know, answer "This database is a copy".

Here is the command to restore Odoo 9 via web front end using curl:

$ curl -F 'master_pwd=password' -F 
backup_file=@/home/user/test.zip -F 'copy=true' -F 'name=dbname' 
http://localhost:8069/web/database/restore

You can automate restore your Odoo 9 in backup site using cron. For back up, please refer to backup procedure.

References:

Monday, October 3, 2016

Fail2ban: install dependency python library in Debian Jessie

These are some python library that may be used in Fail2ban:

pyinotify >= 0.8.3 (Linux >= 2.6.13)
gamin >= 0.0.21
systemd >= 204
dnspython

These are my /etc/apt/sources.list:

deb http://kambing.ui.ac.id/debian/ jessie main
deb-src http://kambing.ui.ac.id/debian/ jessie main
deb http://security.debian.org/ jessie/updates main
deb-src http://security.debian.org/ jessie/updates main
#backport
deb http://ftp.debian.org/debian jessie-backports main

Install them all:

# apt-get install gamin systemd python-pyinotify python-dnspython python3-pyinotify

References:

https://github.com/fail2ban/fail2ban

Tuesday, September 27, 2016

Debian Jessie: installing MySQL Connector/J 5.1.39 into openjdk version 1.8

Install openjdk 1.8

Edit /etc/apt/sources.list

# /etc/apt/sources.list
...
deb http://ftp.debian.org/debian jessie-backports main
...

Install openjdk 1.8

# apt-get install openjdk-8-jdk

Download and configure MySQL Connector/J

Download

# wget http://dev.mysql.com/get/Downloads/Connecctor-J/mysql-connector-java-5.1.39.tar.gz

extract

# tar -xf mysql-connector-java-5.1.39.tar.gz

Copy binary jar into openjdk 1.8

# cp mysql-connector-java-5.1.39-bin.jar /usr/lib/jvm/java-8-openjdk-amd64/jre/lib/ext/

Test Connection test.java

You don't have to run this test.java as root user. MySQL Connector/J will available to all user.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

class test {
Connection mycon = null;

public static void main(String[] args) {
System.out.println("Test");
try {
   Class.forName("com.mysql.jdbc.Driver");
   mycon = DriverManager.getConnection("jdbc:mysql://localhost/test?user=test&password=test");
} catch (SQLException e) {
   System.out.println(e.getMessage());
} catch (ClassNotFoundException e) {
   System.out.println(e.getMessage());
}
}
}

Wednesday, September 21, 2016

Debian Jessie: add ssh key into remote server

Requirement:

openssh
rsync

Create key pair in your host is straight forward:

$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/foo/.ssh/id_rsa):
Created directory '/home/foo/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/foo/.ssh/id_rsa.
Your public key has been saved in /home/foo/.ssh/id_rsa.pub.
The key fingerprint is:
XXXXXX foo@SERVER_A
The key's randomart image is:
....

You have to copy foo's public key into remote site

Method 1: Manual copy public key to remote

on host

$ scp -P 22 ./.ssh/id_rsa.pub bar@SERVER_B:/home/bar/
The authenticity of host '[SERVER_B]:22 ([SERVER_B]:22)' can't be established.
ECDSA key fingerprint is XXXXXX.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '[SERVER_B]:22' (ECDSA) to the list of known hosts.
bar@SERVER_B's password:
id_rsa.pub 100% 390 0.4KB/s 00:00

on remote site

$ cat /home/bar/id_rsa.pub >> /home/bar/.ssh/authorized_keys

Method 2: Direct add public key to remote

$ ssh-copy-id -p 22 bar@SERVER_B
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
bar@SERVER_B's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh -p '22' 'bar@SERVER_B'"
and check to make sure that only the key(s) you wanted were added.

Done, you can ssh using user bar @ SERVER_B without entering password (using your key), from foo @ SERVER_A.

My Notes

Wednesday, October 5, 2016

Simple Pagination With PHP and MySQL

Tuesday, October 4, 2016

Odoo 9: restore using curl

Additional requirement:

Install additional requirement

To restore Odoo 9 via web front end using curl, you need to parse 4 parameters, they are:

Regarding to web front end we can use copy option for almost restoring Odoo 9.

Here is the command to restore Odoo 9 via web front end using curl:

References:

Monday, October 3, 2016

Fail2ban: install dependency python library in Debian Jessie

These are some python library that may be used in Fail2ban:

Install them all:

References:

Tuesday, September 27, 2016

Debian Jessie: installing MySQL Connector/J 5.1.39 into openjdk version 1.8

Install openjdk 1.8

Edit /etc/apt/sources.list

Install openjdk 1.8

Download and configure MySQL Connector/J

Download

extract

Copy binary jar into openjdk 1.8

Test Connection test.java

Wednesday, September 21, 2016

Debian Jessie: add ssh key into remote server

Requirement:

Create key pair in your host is straight forward:

You have to copy foo's public key into remote site

Method 1: Manual copy public key to remote

on host

on remote site

Method 2: Direct add public key to remote

References:

Blog Archive

Total Pageviews