Dynamic Table Name In Postgresql Trigger

Posted on by Alok Sah

Dynamic table name in PostgreSQL trigger used as “TG_TABLE_NAME”
“TG_TABLE_NAME” is a name of the table that caused the trigger invocation.

Like If you want to copy data from one table to another on updating first table

Then start using triggers

Lets say you have table name “first_table”

Create same table as log table to store log of first table data.

Now using PostgreSQL to create functions and trigger:

Firstly, Create a trigger on first table ON UPDATE EACH Rows like

CREATE TRIGGER trigger_name AFTER DELETE OR UPDATE ON first_table FOR EACH ROW EXECUTE PROCEDURE updation_log()

Now Create Function To Execute This Trigger

BEGIN
EXECUTE format('INSERT INTO %I values(($1).*)', TG_TABLE_NAME ||'log') USING OLD ;
RETURN NEW;
END;

format() is simply a string function like as used in C language sprintf().

This procedure explain that first table date is copied to second table defined as “TG_TABLE_NAME ||’log’ ”
if table name is first_table then data is copied to first_tablelog.

Asynchronously files upload with jQuery

Posted on by Alok Sah

Have you ever uploaded files with jquery without using any plugin.
If “NO”, then try to do it using Ajax request and Formdata objects.

Finally done it,after experiment.

To upload files please start with “html form ”


<form id="upload_form" enctype="multipart/form-data">
<input name="file_name" type="file" id="file_name"  />
<input type="button" value="Upload File" />
</form>

Initiate formData object and then appending file  field to it using append() method it to file as:


var formData = new FormData();
formData.append( 'file', $( '#file_name' )[0].files[0]);

var FileUrl    = 'upload.php?action=uploadfile';
var uploadRes    = ajaxFileUpload(FileUrl,formData);

Now, to handle file , upload use ajax as function ajaxFileUpload as below :


function ajaxFileUpload(FileUrl,formData, request_timeout)
{
if(!request_timeout)
request_timeout = 50000;

$.ajax({
url: strFileUrl,  //server script to process data
type: 'POST',
timeout: request_timeout,
// Form data
data: formData,
//Options to tell JQuery not to process data or worry about content-type
cache: false,
contentType: false,
processData: false
});

}

Hope, you have got the solution !!

PHP Encoding with base64

Posted on by Alok Sah

Suppose you have a long file data and you have to save the same in the database. this data is too long and contains some especial character like (\, /, *…etc ) that can corrupt your database or can’t be inserted.
I was working on some configuration file and have to insert it in the database, but didn’t get success in inserting it as it is, because of special characters.

So let’s take “php.ini” configuration file to save this content on database, use php function base64_encode() which encodes data with MIME base64


// its too big file and and have lot of special character
$file = "php.ini";
$file_data	= file_get_contents("$file");
$encode_config_file = base64_encode(implode("\n", $file_data));

now above variable “$encode_config_file” can store this data into the database as a string.
and when we want to fetch and work with this data we need to decode with php function base64_decode()

below code show the fetching of data using base64_decode function:

// $file_data keep encoded string fetched from database
$file_decode = base64_decode($file_data);
echo $file_decode;

/// Output Some part of php.ini like:

// ; allow_call_time_pass_reference
// ;   Default Value: On
// ;   Development Value: Off
// ;   Production Value: Off

Happy Coding…:)

Linux command and PHP

Posted on by Alok Sah

In Operating systems either it is linux, unix or windows system, there are programs which are running on background to keep system functional, these programs are called processes. in linux or unix like systems we have ps command to view processes running on system.
ps command is used to check currently running process status on linux or unix systems. To execute this command just type ps and enter you will have all the currently running process.

$ ps
 PID  TT  STAT      TIME COMMAND
 1175  v0  Is+    0:00.00 /usr/local/man/ps
 1176  v1  Is+    0:00.00 /usr/bin/prox
 63552   0  R+     0:00.00 ps

If you have something wrong or problem from any process you can kill that process from knowing his process id and type command

 $ kill 1175
 $ kill 1175 1176 # for multiple killing process

All linux command has many options to display the description that we can view by man command.
Suppose we have to view the process for some specific user.

 $ man ps # display command description
 $ ps –auwx | grep xyz_user | grep php
 xyz_user  1302  0.0  1.0 60804 10140  ??  I     4Apr12   0:01.65 /usr/local/bin/php /usr/local/www/apache/phpCli.php
 xyz_user  7471  0.0  1.0 60804 10516  ??  I    Wed11AM   0:01.12 /usr/local/bin/php /usr/local/www/apache2/commxyz.php

Above ps command show how can we do pattern searching with the help of grep command, here we search all the currently running process for specific user and having matched string ‘php’ . In result we get PHP files for that specific user running on system along with process id, time and path of that file.

Let’s try now second command to view process, top: its show all running top process like in windows we watch process in “Task manager” process. Simple we can get this by top in linux.

 $ top
 last pid: 72169;  load averages:  0.00,  0.01,  0.00 up 12+22:19:36  10:29:31
 59 processes:  1 running, 58 sleeping
 CPU:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% idle
 Mem: 114M Active, 524M Inact, 216M Wired, 41M Cache, 111M Buf, 56M Free
 Swap: 2011M Total, 220K Used, 2011M Free
 PID USERNAME       THR PRI NICE   SIZE    RES STATE   C   TIME   WCPU COMMAND
 70726 www              1  45    0   131M 18188K select  1   0:02  0.29% httpd
 1025 pgsql            1  44    0 23820K  4444K select  0   2:11  0.00% postgres
 1022 pgsql            1  44    0 64132K 16032K select  1   1:17  0.00% postgres
 1053 root             1  44    0   129M 12848K select  1   0:26  0.00% httpd

This top command result so many information like last processed id, load average of system, uptime, CPU States, memory and swap. If you watch out the screen, you must view the processes running currently and all his information as PID, username time CPU usage etc.

Now if you want these commands to run from php script, there are many php inbuilt function to execute linux commands like exec, system, passthru, shell_exec , backtick operator (` `) etc. we can try any of these to execute linux command by php.
Suppose we have to execute above process command by command line script

 // create a variable
 $ps_cmd = "ps -auwx | grep xyz_user| grep php";
 // execute by backtick operator
 $ps_exec = `$ps_cmd`;
 // output
 var_dump($ps_exec);
 OR
 $psexc = exec($ps_cmd, $ps_output, $ps_result);
 var_dump($ps_output);
 var_dump($ps_result);

This will output as same above all the currently running process for specific user and having matched string ‘php’. In case of using backtick operator we get above result. Using exec we get output in two parameter: output and result. output gives an array and result give either command executes successfully or not.