Listing N Depth Categories Depth Wise

I am regular user at stackoverflow. Post lunch, while seeing the question list for php I came to a question where user wanted to

List N Depth Categories Depth Wise

The Problem was like

table category_path it is in parent child relationship and its look like this


User wanted to display result like as below:

The question was interesting. Initially I though that it is so easy can be easily done with self joining of table on id to parent_category_id. But I was wrong. It was not so easy as considered.

After spending whole evening I got the solution is as below:

I have considered that user can easily fetch table values into array and then perform as given:


function findValue($key,$src){

return $src[$key];

function inPatentList($val, $patent_list){

return (in_array($val, $patent_list)) ? true : false;

function findFullTraverse($id, $src,&$str){
if(0 != ($value = findValue($id, $src))){
$str .= $value;
$str .= ‘_’.$value;
$id_parent = array(
’1′ => ’0′,
’2′ => ’1′,
’3′ => ’2′,
’4′ => ’1′,
’5′ => ’3′,
’6′ => ’2′,
$parent = array_values($id_parent);
$ids = array_keys($id_parent);

$depth = array();
$keys_for_value = array();
$id_parent = array_reverse($id_parent, true);
foreach($id_parent as $key => $val){

$depth[] = $key.’_’.$key.’_0′;
if(inPatentList($key, $parent)){
$keys_for_value = array_keys($id_parent, $key);
$depth_found[$key] = $keys_for_value;
foreach ($depth_found[$key] as $value){
$str = ”;
findFullTraverse($value, $id_parent,$str);
//echo $value.’=>’.$str.’<br/>’;
$traverse_array = explode(‘_’, $str);
$has_depth = $i + 1;

echo ‘<pre>’;
echo ‘<pre>’;


 Its really gives nice feeling when solving complex problem.

Load Data From XML to MySql Db Table

One of my colleagues was grabbing data from the web. The data grabbed was in XML format  and he asked me “Hey Rajan, Is there any way that we can insert these data into database?”.

I said, “Wait, let me look”. For till to date, I did database to xml but did not perform xml to database. To solve this I googled and get the solution.

The solution was MySql: LOAD XML

The complete solution is as below:

data.xml file

<?xml version=”1.0″ standalone=”yes” ?>







<field name=’id’>1019</field>

<field name=’name’> Best Western </field>



To import this data into database MySql table, first you need to create table. Precautions, The table columns name must match exactly the data containing tag name or to the tag attribute mentioned as name=’column_name’.

So let’s create table xmldata:
CREATE TABLE xmldata (
    name VARCHAR(40) NULL,
    created TIMESTAMP

Once you are done with this now we just need to import data!!!

mysql> LOAD XML LOCAL INFILE 'd:/path_to_/data.xml'
    ->   INTO TABLE xmldata
    ->   ROWS IDENTIFIED BY '<result>';

That’s it. You are done with it.

Creating Attractive Anchor buttons Using CSS

Today, while traversing through stackoverflow, one user had asked question about the anchor link button css. He wanted to show mouse hover high-light effect.  The solution I provided is Below.

The two Image user are here:

Main Back Ground Image

The first image is the main(big) back-ground image and the second(small) image is the left side starting image for button.

The main advantage of this css is that, this css is desinged in such a way that it suits itself according to the length of the anchor text.

<style type=’text/css’>

#header #nav a {
color: #fff;
background:url(“nav-r.png”) 0 0 no-repeat;
#header #nav a span {
background:url(“nav-bg.png”) 100% 0 no-repeat;
#header #nav a:hover { background-position:0 -70px; }
#header #nav a:hover span { background-position:100% -70px; }

<div id=’header’>
<div id=’nav’>
<a href=’#’><span>Rajan </span></a>

Download The sample code here

Conditionally selecting values on mysql table

While working under e-commerce project in PHP – MySql, You may came under the situation where you need to show the product price based on condition. It means if the product is that is going to be displayed in the search result has any special offer till some date then show the “special offer” otherwise show normal product price.

I was confuse How to do it. But then I got solution. It was The mysql magic :). The “CASE WHEN ( condition ) THEN some_column_1 ELSE some_column_2″. This solved my problem exactly.

The product table is below.

Now you may getting clear what I want to say. When special_status column has value 1 and special_expire date is greater than current time and special_price is greater than 0(zero) , at that show special_price and if any of the condition fails then show net_price

I select query is as below:

$sql = “SELECT products_id, net_price, special_price, special_expire, special_status, products_name,
CASE WHEN (special_price > 0 AND special_expire > NOW() AND special_status != 0)
THEN special_price ELSE net_price END AS price FROM product_master
WHERE products_name LIKE ‘%s%’
HAVING price >= 100 AND price <= 1000″;

The query above I have fired in such a way that you can get all the rows in order to understand how exactly works. The result of the select query is as below:

See The last column : price where you can see the magic of mysql

Hope this may be helpful to the guys working for e-commerce projects.

Sorry for misspelled words and bad English if you find any.

Thank for reading.

Rajan Rawal

Format Date Fields Using MySQL DATE_FORMAT()

I use MySQL DATE and DATETIME fields almost as much as VARCHAR fields. I use them mostly to track record creation, record updates, and record expiration dates. The default date format in MYSQL is:
2008-09-10 22:50:02

When I pull this information into the page, I want to show the date in human-readable format. That’s where MySQL’s DATE_FORMAT functionality comes in. DATE_FORMAT allows me to format the date in any manner you’d like.

DATE_FORMAT(NOW(),’%W, %M %e, %Y @ %h:%i %p’)
#yields ‘Sunday, September 20, 2008 @ 12:45 PM’

MySQL DATE_FORMAT() Letter Representations

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above
Be kind to your users and format the date for them!

Zend Helpers: View Helpers and Controller Action Helper

To refer any php function, is the best site but the same is not for Zend, The Zend Documentation is so poor. The best developers those who have given their best to the Zend Framework, but have not given enough to Zend Documentation.

Let’s move to the main topic. Today after a long time I am goin to Show you that how would you create “Zend Controller Action Helper”

Don’t get confuse between the Zend_View_Helper and Zend_Controller_Action_Helper. In one term both are the helpers but for different section as their name suggests. The first one is for View Helper and another is for the Zend Action controller Helper.

1. Zend View Helper:

Creating A Helper Class:

I have created the helper class named Myapp_View_Helper_Adminhelper the exist in the folder Myapp/view/helper/Adminhelper.php

class Myapp_View_Helper_Adminhelper{

public function adminhelper(){
return $this;

public function formatDate($date){
return date(‘d-m-Y h:i:s a’,strtotime($date));

The View Helper class must have function name same as the name of the  class name in small letters, in short kind of constructor that is not called using __construct() . So you may wonder that what if one want to create more than one helpers function in the same class. So I made a trick here is that I have returned the class object in the main function and so now I can call as many functions as I want defined in the helper class.


Class has been created properly but to tell Zend Please dont trow any errors when I call this View Helper class functions in view script, we have to add this path into view helpers path. If you have created _initView() function in your bootstrap.php modify it as below.

protected function _initView(){
// Initialize view
$view = new Zend_View();

// Add it to the ViewRenderer
$viewRenderer = Zend_Controller_Action_HelperBroker::getStaticHelper(‘ViewRenderer’);

// Adding View Helper Path
$view->addHelperPath(APPLICATION_PATH.’/views/helpers’, ‘Myapp_View_Helper’);

// Return it, so that it can be stored by the bootstrap
return $view;

If you dont have _initView() function in your bootstrap.php filen then just create any function that you like (e.g. _initViewHelpers)and inside that funciton the $view object as above and then add code mentioning adding the the View Helper path. That’s it. You can verify that view helpers path is included properly or not by using $view->getHelpersPath() function, which will return all helpers path.

Now we can call view helpers class function as…

<?php echo $this->adminhelper()->formatDate($requestedps->created_on); ?>

In above code $this->adminhelper()  return the object of the same class and hence you can call function formatDate() like as above.

This is what you just need to implement for view helpers. Zend has by default given some view helpers default that you can use it with out any hassle.

Now Lets Continue with action helpers:

2. Zend Controller Action Helper:

I was working in the admin panel coding. In the controller actions, in all the pages that was displaying the all records form the database table I was using zend pagination plugin. But I had to repeat small pagination code snippet in every action. I could have created one normal function in the controller class but this would be limited to that controller only and I wanted pagination to use in Front end also. It was the perfect time to start with Zend Controller Action.

I created one directory named actionhelpers inside the application folder, in short at same level where my views, controllers are. you can give any name what ever you like or you can put folder inside folder. The thing that matter is that as we had registered zend view helpers as same way we have to register Action Helpers and at that time you have to take care while registering it in you bootstrap.php file

class Myapp_Actionhelper_Common extends Zend_Controller_Action_Helper_Abstract{

protected $view;
public function pagination($result_set, $style = ‘digg’){

$page = $this->getRequest()->getParam(‘page’,1);
$records_per_page = $this->getRequest()->getParam(‘shown’,10);

$paginator = Zend_Paginator::factory($result_set);

$record_count = sizeof($result_set);

$view = $this->getView();
$view->page = $page;
$view->records_per_page = $records_per_page;
$view->numrows = $record_count;
$view->pagination_config = array(‘total_items’=>$record_count,’items_per_page’=>$records_per_page,’style’=>$style);
$view->records = $paginator;

public function getView(){

if(null !== $this->view){
return $this->view;

$controller = $this->getActionController();
$this->view = $controller->view;
return $this->view;

Now first you will have to tell zend where your action helper it so it doesn’t throw an error

public function _initActionHelpers(){

// Adding Action Helper Path
Zend_Controller_Action_HelperBroker::addPath(APPLICATION_PATH.’/actionhelpers’, ‘Myapp_Actionhelper’);

Here “APPLICATION_PATH” is the constant value and is the path string to my application folder.

Now in your controller action, you can call this function as

public someAction(){



where $request_res is the record set the i fetch from database table.



Abstraction and interfaces

Abstraction and interfaces are two very different tools. The are as close as hammers and drills. Abstract classes may have implemented methods, whereas interfaces have no implementation in themselves.

Abstract classes that declare all their methods as abstract are not interfaces with different names. One can implement multiple interfaces, but not extend multiple classes (or abstract classes).

The use of abstraction vs interfaces is problem specific and the choice is made during the design of software, not its implementation. In the same project you may as well offer an interface and a base (probably abstract) class as a reference that implements the interface. Why would you do that?

Let us assume that we want to build a system that calls different services, which in turn have actions. Normally, we could offer a method called execute that accepts the name of the action as a parameter and executes the action.

We want to make sure that classes can actually define their own ways of executing actions. So we create an interface IService that has the execute method. Well, in most of your cases, you will be copying and pasting the exact same code for execute.

We can create a reference implemention for a class named Service and implement the execute method. So, no more copying and pasting for your other classes! But what if you want to extend MySLLi?? You can implement the interface (copy-paste probably), and there you are, again with a service. Abstraction can be included in the class for initialisation code, which cannot be predefined for every class that you will write.

Hope this is not too mind-boggling and helps someone. Cheers,
Alexios Tsiaparas



Get every new post delivered to your Inbox.