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.

Quick Review on SQLite with PHP

Posted on by Alok Sah

Searching for a minimal size of database i found SQLITE the best. one simple file that not take more space (in KB) and supports multiple table, index view, triggers and many more, all data is stored in one single file and work as a database engine.
Working with php, i use Sqlite3 version.

To execute a sqlite statement webserver should have a full permission to this directory and database file.
Suppose we have a sqlite database file name sqlitedb.s3db (extension is s3db). first let start with creation a database.

Creating a Connection and Database

// creating a connection with database
$dbConnSqlite = new SQLite3("sqlitedb.s3db");
// database creation
$sqlCreateTable="Create table person(
 id INTEGER PRIMARY KEY NOT NULL,
 name text,
 age  INTEGER
 )";
if($dbConnSqlite->query($sqlCreateTable))
{
 $sqlInsert = “Insert into person values(‘xyz’,2)”;
 $dbConnSqlite->query($sqlInsert));
}

Above example open a SQLite3 database “sqlitedb” connection, create a table “person” and insert one row to database.
Sqlite is not rich in datatype but have some common datatype integer, varchar, text, which fullfil our requirement. In this example i am using query() to execute sql statment.

Fetching Data

$sqlPerson= "select * from person";
if($resSql = $dbConnSqlite->query($sqlPerson))
{
 while($rowSql = $resSql->fetchArray(SQLITE3_ASSOC))
 {
 echo "Person name is: " . $rowSql['name'] . "and his age is" . $rowSql['age'] . "\n";
 }
}
else
{
 Echo "Query Failed";
}

In above example call fetchArray to read data from database with passing “SQLITE3_ASSOC” flag.

In Sqlite libraries flags are of three type:
1> SQLITE_ASSOC: fetch associative array
2> SQLITE_NUM: fetch index array
3> SQLITE_BOTH :fetch assoctive array with index

Function to get Number of Rows in table and last insert id

// $numRows give number of rows
$numRows = sqlite3_num_rows($sqlPerson);

// This function return number of rows
public function sqlite3_num_rows($query, $flag="both")
{
 global $dbConnSqlite;
 $result = $dbConnSqlite->query($query);
 if(!$result) //make sure the result is valid
 return NULL;

 if($flag=="assoc")
 $flag = SQLITE3_ASSOC;
 else if($flag=="num")
 $flag = SQLITE3_NUM;
 else
 $flag = SQLITE3_BOTH;
 $numArray = array();
 $i = 0;
 while($res = $result->fetchArray($flag))
 {
 $numArray[$i] = $res;
 $i++;
 }
 $numRows = count($numArray);
 return $numRows;
}

// To get last insert id
$sqlInsert = “Insert into person values(‘johny’,22)”;

if(!$dbConnSqlite->query($sqlInsert))
{
 echo "Query failed.";
 }
else
{
 $insertId = "SELECT last_insert_rowid() AS insert_id";
 $result        = $dbConnSqlite->query($insertId);
 // Call to function
 $numRows    = sqlite3_num_rows($insertId, "num");
 if($numRows > 0)
 {
 $rows = $result->fetchArray(SQLITE3_ASSOC);
 $insert_id    = $rows['insert_id'];
 }
}

First off, we have simple function that return number of rows in table. now to get last insert id in sqlite it has select statment “SELECT last_insert_rowid() AS insert_id”.

In Sqlite we also have unique key, foreign key constraints and can create trigger too as we create similar in other databases. this article is a way to start experimenting  a simple file that give you a big result.

References: www.sqlite.org

Extracting Information Schema Using Postgres Sql

Posted on by Alok Sah

This Tutorial introduces how to extract Information Schema in PostgreSQL with simple Sql queries. Information schema provides information about tables, columns, views, trigger, functions and sequences in a database. We extract these information from simple SQL queries, lets we have a well maintained database and below are simple Sql statements to get various information in our database.


List All Users
SELECT usename
FROM pg_user;

List All Tables Name
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');

List All Views
SELECT table_name
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name !~ '^pg_';

List All Table Name
SELECT  table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema NOT IN
('pg_catalog', 'information_schema')

List Name of the field, data type of table.
// Suppose emp is table name

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'emp';

List Table Indexes
// Suppose emp is table name

SELECT  relname FROM pg_class
WHERE oid IN
    (SELECT indexrelid FROM pg_index, pg_class
       WHERE pg_class.relname='emp'
        AND pg_class.oid=pg_index.indrelid
        AND indisunique != 't' AND indisprimary != 't')

List Table Constraints
SELECT  constraint_name, constraint_type FROM information_schema.table_constraints
WHERE table_name = 'emp' AND constraint_type!='CHECK'

List Triggers
SELECT DISTINCT trigger_name FROM information_schema.triggers
WHERE event_object_table = 'emp'
AND trigger_schema NOT IN ('pg_catalog', 'information_schema')

List Functions
SELECT routine_name FROM information_schema.routines
WHERE specific_schema NOT IN ('pg_catalog', 'information_schema')

List Sequences
SELECT relname FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN
                   (SELECT oid FROM pg_namespace
                      WHERE nspname NOT LIKE 'pg_%'
                       AND nspname != 'information_schema')

Execute these queries in your database and get all result.

List Box Manipulation using jQuery

Posted on by Alok Sah

Working on listbox I assume that jquery is very simple to move data from one list box to another. Yes I implemented it.
Here I implemented on submit button and on double click on object. To implement this first include jquery to simple html page

<script type="text/javascript" src="jquery.js" charset="utf-8"></script>

Suppose you have two list boxes in an html table having some values and two button to move data from listbox first to another a list box and two buttons are like:

<select id="firstList" multiple="multiple" style="height:420px;width: 250px;" >
<option value="1">PHP</option>
<option value="2">.Net</option>
<option value="4">Paste</option>
<option value="5">Pea</option>
<option value="6">Pamp</option>
</select>
<input id="to2" type="button" name="to2"  title='assign' value=">" /><br/><br/>
<input id="to1" type="button" name="to1" title='unassign' value="<">
<select id="secondList" multiple="multiple" style="height:420px;width: 250px;" ></select>

All is the game of jquery functioning how we can obtained to move data from one list box to another.
Create two functions in assignList and unassignList like

// function: UnAssignment
function assignList()
{
// loop through first listbox and append to second listbox
$('#firstList :selected').each(function(i, selected){
// append to second list box
$('#secondList').append('<option value="'+selected.value+'">'+ selected.text+'</option>');
// remove from first list box
$("#firstList option[value='"+ selected.value +"']").remove();
});
}
// function: UnAssignment
function unassignList()
{
// loop through second listbox and append to first listbox
$('#secondList :selected').each(function(i, selected){
// append to first list box
$('#firstList').append('<option value="'+selected.value+'">'+ selected.text+'</option>');
// remove from second list box
$("#secondList option[value='"+ selected.value +"']").remove();
});
}

To make this functioning add jquery button and double click event to button and object itself.

$(document).ready(function(){
$('#firstList').dblclick(function() {
assignList();
});

$('#secondList').dblclick(function() {
unassignList();
 });

$('#to2').click(function()
{
assignList();
});

$('#to1').click(function()
{
unassignList();
});
 });

As we now one important thing to notice if list box is full of values and we have to search any particular value so it is difficult to search whole list box, for searching through list box take a text box and below how this function searchFirstList filter
No for this I have a textbox to filter
<input id=”firstFilterSearch” type=”text”>

// Function for Filtering
function searchFirstList(inputVal, searchArea)
{
var allCells = $(searchArea).find(‘option’);
if(allCells.length > 0)
{
var found = false;
allCells.each(function(index, option)
{
var regExp = new RegExp(inputVal, ‘i’);
if(regExp.test($(option).text()))
$(option).show();
else
$(option).hide();
});
}
}
[/javascript]

Calling this function


$('#firstFilterSearch').keyup(function()
{
var searchArea = $('#firstList'); // Select Box ID
searchFirstList($(this).val(), searchArea);
});

Here is example EXAMPLE