PHP + Firebird SQL Installation

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on PHP + Firebird SQL Installation

Download the following: (In my case, it’s 64bit)

  1. PHP
  2. Apache
  3. Firebird (http://www.firebirdsql.org/en/firebird-2-5-2-upd1/)

After installation of PHP, edit the php.ini and uncomment:

  • php_interbase.dll
  • php_pdo_firebird.dll

At command prompt, cast php -v and you will have an error:

PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php\ext\php_interbase.dll' - The specified module could not be found.
 in Unknown on line 0
PHP Warning:  PHP Startup: Unable to load dynamic library 'C:\php\ext\php_pdo_firebird.dll' - The specified module could not be found.
 in Unknown on line 0
PHP 5.4.9 (cli) (built: Nov 21 2012 19:54:46)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

Install the Firebird…

Try again casting a command php -v

C:\Workspace\localhost\vshbdata>php -v
PHP 5.4.9 (cli) (built: Nov 21 2012 19:54:46)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

Meaning, the firebird is successfully installed.

Second test is create a php script:

<?php

foreach(PDO::getAvailableDrivers() as $driver) {
  echo $driver.'<br />';
}

?>

You should see firebird in the list…

Sorting Multi-Dimensional Array

PHP, Web Development Comments Off on Sorting Multi-Dimensional Array

I never tried sorting a an array using the inner array as a key for sorting. In this blog, been searching for the solutions and here it is…

Sample Data:

Array
(
    [0] => Array
        (
            [id] => 1
            [user] => c test1
            [data] => Array
                (
                    [a] => 1
                    [b] => 2
                )

        )

    [1] => Array
        (
            [id] => 2
            [user] => b test2
            [data] => Array
                (
                    [a] => 3
                    [b] => 4
                )

        )

    [2] => Array
        (
            [id] => 3
            [user] => a test3
            [data] => Array
                (
                    [a] => 5
                    [b] => 1
                )

        )

)

Sorting Code:

// sort multidimensional array... [start]

$test_data = array(
        array('id' => 1, 'user' => 'c test1', 'data' => array('a' => 1, 'b' => 2)),
        array('id' => 2, 'user' => 'b test2', 'data' => array('a' => 3, 'b' => 4)),
        array('id' => 3, 'user' => 'a test3', 'data' => array('a' => 5, 'b' => 1))
);

// temporary container for sorting...
$sort_data = array();

foreach ($test_data as $key => $row) {
	// sorting by id
	$sort_data[$key] = $row['id'];

	// sorting by user
	$sort_data[$key] = $row['user'];

	// sorting by a in the inner array..
	$sort_data[$key] = $row['data']['a'];

}
// sort as ascending...
array_multisort($sort_data, SORT_ASC, $contract_data);

// sort as descending...
array_multisort($sort_data, SORT_DESC, $contract_data);

// sort multidimensional array... [end]

That’s it.. It will be handy for those who love playing with arrays… 🙂

Remove Redundant Spaces, Tabs and New Lines

PHP, Web Development Comments Off on Remove Redundant Spaces, Tabs and New Lines

It’s been a while that I’ve been dealing with long text. Commonly, users input with redundant new lines, tabs, spaces and long words. Here’s my methods and just like others out there;

Removing redundant new lines or line breaks:

$str = preg_replace('/(?:(?:\r\n|\r|\n)\s*){2}/s', "\n\n", $str);

Removing redundant tabs:

$str = preg_replace("/[ \t]+/", " ", $str);

Removing redundant spaces:

$str = preg_replace("/[ ]+/", " ", $str);

Shorten long word:

function ellipsis($str, $max = 45) 
{
	if (strlen($str) > $max) 
	{
		$str = '<abbr title="'.strip_tags($str).'">'.substr($str,0,$max).'...</abbr>';
	}
	return $str;
}

I think that’s all… Hope this will help you… 🙂

MySQL SPATIAL Index for GeoIP Database

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on MySQL SPATIAL Index for GeoIP Database

Few days ago, I had an experimental project called Website Visitor Tracker which will track the visitor what city or country they from and plot their location to Google WebGL Globe and the source of the data have to query in 2M+ rows for the IP blocks and 290k+ rows for the cities or 91k+ rows for the countries. The problem occurs is the the query which it will take 2 to 4 seconds each IP query from the database and it is bad sign.

I found a solution suggested by BOTP from Kagaya-anon Linux Users Group (Thanks master BOTP) that I have to use SPATIAL Index. So I did and it works like a charm.

This is the output without using SPATIAL Index…

mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b, geoip_locations AS l WHERE b.locID = l.locID AND INET_ATON('203.114.138.95') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id      | city             | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 |   175.6167 |
+---------+------------------+----------+------------+
1 row in set (1.71 sec)

mysql>

So here’s the steps implementing SPATIAL index…

First I created a new table…

create table geoip_blocks (
	id bigint(20) unsigned not null auto_increment primary key,
	locID bigint(20) not null default 0,
	startIpNum bigint(20) unsigned not null,
	endIpNum bigint(20) unsigned not null,
	ip_poly POLYGON not null,
	SPATIAL INDEX(ip_poly)
) engine=myisam;

Note: use MyISAM engine for this to apply SPATIAL index, otherwise you will encounter some issues. 😛

Next, export your current table of your GeoIP…

WINDOWS: 

select locID, startIpNum, endIpNum into outfile 
'c:/geoip_blocks.dat' fields terminated by "," enclosed by "\"" 
lines terminated by "\n" from geoip_ip_blocks;

LINUX:

select locID, startIpNum, endIpNum into outfile 
'/home/user/geoip_blocks.dat' fields terminated by "," 
enclosed by "\"" lines terminated by "\n" from geoip_ip_blocks;

Then import again but this time into the new table where you will insert into POLYGON type field.

WINDOWS:

LOAD DATA LOCAL INFILE "c:/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

LINUX:

LOAD DATA LOCAL INFILE "/home/user/geoip_blocks.dat"
         INTO TABLE geoip_blocks 
         FIELDS
            TERMINATED BY ","
             ENCLOSED BY "\""
         LINES
              TERMINATED BY "\n"
           (
               @locID, @startIpNum, @endIpNum 
             )
       SET
            id := NULL,
            locID := @locID,
           startIpNum := @startIpNum,
           endIpNum := @endIpNum,
           ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
          POINT(@startIpNum, -1),
          POINT(@endIpNum, -1),
          POINT(@endIpNum, 1),
          POINT(@startIpNum, 1),
         POINT(@startIpNum, -1))));

And that’s it.. You are good to go for testing…

OUTPUT AFTER SPATIAL INDEX IMPLEMENTED:

mysql> SELECT geoip_blocks.locID, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------+------------+
| locID  | startIpNum | endIpNum   |
+--------+------------+------------+
| 199902 | 3413280768 | 3413283071 |
+--------+------------+------------+
1 row in set (0.19 sec)

mysql> SELECT geoip_blocks.locID, geoip_locations.city, geoip_blocks.startIpNum, geoip_blocks.endIpNum
    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID
    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.95'), 0)));
+--------+------------------+------------+------------+
| locID  | city             | startIpNum | endIpNum   |
+--------+------------------+------------+------------+
| 199902 | Palmerston North | 3413280768 | 3413283071 |
+--------+------------------+------------+------------+
1 row in set (0.00 sec)

Hope this help for those who encounter the same problem I had… 🙂

Thanks again to Master BOTP & DATA BOB JR.

Reference: http://databobjr.blogspot.co.nz/2010/07/ip-to-country-lookup-table-using-mysql.html

Website Visitor Tracker

Info.Tech, PHP, Web Development Comments Off on Website Visitor Tracker

Last Sunday, February 3, 2013, I visited Google About page and saw their WebGL Globe. Checking the couple of samples, it rang a bell to me to create a website visitor tracker. So I start coding and I called it C3rd Visitor Tracker..

This is an experimental project. I used MaxMind GeoIP for locating the visitor’s city name via their IP address and it took me a day to parse their CSV. Hahaha…

I completed the project after 2.5 days… 🙂

Below is the sample screenshot of Prendstah.com:

prendstah_vtracker

Most of the users are from Philippines, so it marked well and some kind of accurate.

So if you want to track your website visitors, you can use my Visitor Tracker and start monitoring your visitors.

Sign up at http://www.tracker.isourcery.com/

Thanks for the support…

Inserting and Retrieving File into MySQL Database

Info.Tech, PHP, SQL: Structured Query Language, Web Development Comments Off on Inserting and Retrieving File into MySQL Database

Today,  I encounter a task to save the file into the database and its been a while that I have been saving files into the database method. I usually save just the path and filename. So this is just recall… 🙂

create table pdf_files (
	id bigint(20) unsigned not null auto_increment primary key,
	filename varchar(64),
	file_path varchar(64),
	binfile blob,
	created datetime
);

There’s a lot of method out there, but here’s my way of inserting the file to the database.

$path = "public/pdf/";
$file = "test.pdf";

$fileHandle = fopen($path.$file, "r");
$fileContent = fread($fileHandle, filesize($path.$file));
$fileContent = addslashes($fileContent);
fclose($fileHandle);
mysql_query("INSERT INTO pdf_files (filename,file_path,binfile,created) VALUES ('$file','$path','$fileContent',NOW())");

You can also use LOAD_FILE(”/path/filename.xxx’) in MySQL to save the file in the database.. 🙂

Again, there’s a lot of way… and this is my way how to retrieve the file…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	echo '<script>
            location.href="/'.$filename_target.'";
          </script>';
} else {
	echo 'File not found!';
	die();
}

And another way, force-download…

$res = mysql_query(sprintf("SELECT filename,file_path,binfile FROM pdf_files WHERE id = %d", $xid));
if (mysql_num_rows($res) > 0) {
    $row = mysql_fetch_array($res);
    $bin_data = $row['binfile'];
    $filename_target = $row['file_path'].md5($row['filename'].'_'.time()).'.pdf';
    file_put_contents($filename_target, $bin_data);
	// let the user download the file...
	header('Content-Description: File Transfer');
    header('Content-Type: application/force-download');
    header('Content-Disposition: attachment; filename='.basename($filename_target));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . filesize($filename_target));
    ob_clean();
    flush();
    readfile($filename_target);
    exit;
} else {
	echo 'File not found!';
	die();
}

Hope this will help you… Cheers!

[ZF2] Zend Framework 2 : Getting Started

Info.Tech, PHP, Web Development, Zend Framework Comments Off on [ZF2] Zend Framework 2 : Getting Started

Since you already installed your PHP composer, next is you will download the Zend Skeleton Application at

https://github.com/zendframework/ZendSkeletonApplication

Extract or save the files to your apache/httpd public folder. In my case, it was in;

D:\localhost\zend2>cd HelloWorld

D:\localhost\zend2\HelloWorld>dir
 Volume in drive D is WorkFiles
 Volume Serial Number is 3220-C192

 Directory of D:\localhost\zend2\HelloWorld

10/11/2012  04:20 p.m.    <DIR>          .
10/11/2012  04:20 p.m.    <DIR>          ..
10/11/2012  04:20 p.m.                83 .gitignore
10/11/2012  04:20 p.m.                92 .gitmodules
10/11/2012  04:20 p.m.               336 composer.json
10/11/2012  04:20 p.m.           570,295 composer.phar
10/11/2012  04:20 p.m.    <DIR>          config
10/11/2012  04:20 p.m.    <DIR>          data
10/11/2012  04:20 p.m.             1,812 init_autoloader.php
10/11/2012  04:20 p.m.             1,548 LICENSE.txt
10/11/2012  04:20 p.m.    <DIR>          module
10/11/2012  04:20 p.m.    <DIR>          public
10/11/2012  04:20 p.m.             1,753 README.md
10/11/2012  04:20 p.m.    <DIR>          vendor
               7 File(s)        575,919 bytes
               7 Dir(s)  111,679,717,376 bytes free

The composer.json is the requirement for the PHP composer.phar to update and install components/packages for the Zend Framework 2 (ZF2).

The next you need to do is to self-update and install. Follow the command below;

D:\localhost\zend2\HelloWorld>php composer.phar update
This dev build of composer is outdated, please run "composer.phar self-update" to get the latest version.
Loading composer repositories with package information
^C
D:\localhost\zend2\HelloWorld>php composer.phar self-update
Updating to version dea4bdf.
    Downloading: 100%

D:\localhost\zend2\HelloWorld>php composer.phar install
Loading composer repositories with package information
Installing dependencies
  - Installing zendframework/zendframework (2.0.6)
    Downloading: 100%
4123 File(s) copied
5 File(s) copied
6 File(s) copied
1797 File(s) copied
37 File(s) copied
2268 File(s) copied
1 File(s) copied

zendframework/zendframework suggests installing doctrine/common (Doctrine\Common >=2.1 for annotation features)
zendframework/zendframework suggests installing pecl-weakref (Implementation of weak references for Zend\Stdlib\CallbackHandler)
zendframework/zendframework suggests installing zendframework/zendpdf (ZendPdf for creating PDF representations of barcodes)
zendframework/zendframework suggests installing zendframework/zendservice-recaptcha (ZendService\ReCaptcha for rendering ReCaptchas in Zend\Captcha and/or Zend\Form)
Writing lock file
Generating autoload files

D:\localhost\zend2\HelloWorld>

That’s should do it… So you can view the installed ZF2 to your browser. It should look like the image below;

Installed Zend Framework 2.0.6

I hope this will help in your pursuance in studying Zend Framework or if you already into ZF but at version 1.x, then this is a good start for the ZF2.

Installing PHP composer.phar

Info.Tech, PHP, Web Development, Zend Framework Comments Off on Installing PHP composer.phar

This guide is installing PHP composer.phar and proceed to ZendFramework 2.x setup/installation.

First, go the your PHP installed directory then cast the command;

cd C:\php\bin
C:\php\bin>php -r "eval('?>'.file_get_contents('https://getcomposer.org/installer'));"

At the same working directory, C:\php\bin, create a file named composer.bat and it should contains the following;

@ECHO OFF
SET composerScript=composer.phar
php "%~dp0%composerScript%" %*

Then test it if its working…

C:\php>composer.bat
   ______
  / ____/___  ____ ___  ____  ____  ________  _____
 / /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version dea4bdf

Usage:
  [options] command [arguments]

Options:
  --help           -h Display this help message.
  --quiet          -q Do not output any message.
  --verbose        -v Increase verbosity of messages.
  --version        -V Display this application version.
  --ansi              Force ANSI output.
  --no-ansi           Disable ANSI output.
  --no-interaction -n Do not ask any interactive question.
  --profile           Display timing and memory usage information
  --working-dir    -d If specified, use the given directory as working directory.

Available commands:
  about            Short information about Composer
  config           Set config options
  create-project   Create new project from a package into given directory.
  depends          Shows which packages depend on the given package
  dump-autoload    Dumps the autoloader
  dumpautoload     Dumps the autoloader
  help             Displays help for a command
  init             Creates a basic composer.json file in current directory.
  install          Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
  list             Lists commands
  require          Adds required packages to your composer.json and installs them
  search           Search for packages
  self-update      Updates composer.phar to the latest version.
  selfupdate       Updates composer.phar to the latest version.
  show             Show information about packages
  status           Show a list of locally modified packages
  update           Updates your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  validate         Validates a composer.json

C:\php>composer.bat -V
Composer version dea4bdf

That’s it… You can now proceed in downloading the Zend Framework or Symfony. The next guide, I will be using Zend Framework 2.x in my case.

Reference: http://getcomposer.org/doc/00-intro.md#installation-windows

oMarket – New Zealand Free Posting Classified Ads

Info.Tech, Personal, PHP, Web Development 2 Comments »

oMarket is New Zealand Free Posting Classified Ads. This is my new personal project and my first website in NZ. Hope kiwis or new zealanders will post their classified ads at oMarket.

VICIdial Registration Form script

Info.Tech, PHP, Web Development 4 Comments »

Few days ago, an online friend, Ruben Lacumba, ask some help to create a registration script for VICIdial. Though, I have a little background on VICIdial and Asterisk — I still accomplished to create one. Yipee!

Purpose: If you want to make your VICIdial server available to the public or you want to make money out of it by Rent Service, you can use this form for the registration process.

VICIdial Registration Form script v0.1.0

Enjoy the script!

PS: If you have questions, suggestions and other related matters, please PM me.

Download Version 0.1.0: VICIdial Registration Form v0.1.0 (784)

Download Version 0.1.1: VICIdial Registration Form script v0.1.1 (862)

Change log:

  1. Version 0.1.0 – Initial Release
  2. Version 0.1.1 – Can add more agents (define by you); Can refresh/reload the captcha.
WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in