php excel

Posted on July 15, 2009 by mahesh chari

How to export tabular data from php / mysql to MS excel

In this article, I show how to build a PHP Web application that uses these Excel formats to export the contents of a mysql database table or array to an simple Excel spreadsheet.Microsoft Office 2003 for the Microsoft Windows® make simple Excel as simple xml data with predefined XSLT stylesheet. It also supports HTML table data and delemiter support.Here we take these two advantages.

First technique is just simple as we fetch the data from source like arrays or mysql table in HTML table,force the browser to download the file as xls file.When download completed we can open with MS Excel.

Code I

<?php /* 
This example demonstrates 
how to convert PHP array data to MS Excel 2003 data to download <br>
user data base.
Here we create a simple HTML tabler data and we force the web browser as stream data
instead we simply displaying HTML table
thats all
 */
$data_headers=array('First Name','Last Name','Date of Birth');
$data_array[]=array('Mahesh','Chari','25-08-1983');
$data_array[]=array('Chari','Mahesh','25-08-1980');
$data_array[]=array('Guggilla','Mahesh','25-08-1970');
$data_array[]=array('Mahesh','Guggilla','21-08-1985');
$data_array[]=array('Mahesh Chari','Guggilla','20-08-1950');
$data_array[]=array('Chari','Guggilla','25-08-1950');
// here we prepare a simple HTML table string
$html_string='<table>';
$html_string.='<tr><td>'.implode('</td><td>',$data_headers).'</td></tr>';
	foreach($data_array as $k=>$v){
		$html_string.='<tr><td>'.implode('</td><td>',$v).'</td></tr>';
	}
$html_string.='</table>';
//make time dependent xls name that is always unique
$xlsfile = "excel_example".date("m-d-Y-hiA").".xls";
//stop the browser displaying the HTML table displaying
//force the browser to download as xcel document
//if you make comment below two lines as php comments ,you see a simple HTML table
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=$xlsfile");
 
print  $html_string;?>

Download above Example script

Second technique is just simple as we fetch the data from source like arrays or mysql table in simple tab delimitted text ,force the browser to download the file as xls file.When download completed we can open with MS Excel.

Code II

<?php 
/* 
This example demonstrates 
how to convert PHP array data to MS Excel 2003 data to download <br>
user data base.
Here we create a simple text format like CSV with a delimiter \t tab and new line delimiter for rows
thats all
 */
$data_headers=array('First Name','Last Name','Date of Birth');
$data_array[]=array('Mahesh','Chari','25-08-1983');
$data_array[]=array('Chari','Mahesh','25-08-1980');
$data_array[]=array('Guggilla','Mahesh','25-08-1970');
$data_array[]=array('Mahesh','Guggilla','21-08-1985');
$data_array[]=array('Mahesh Chari','Guggilla','20-08-1950');
$data_array[]=array('Chari','Guggilla','25-08-1950');
// here we prepare a simple  \t delimetered text format 
$delim_string='';
$delim_string.=implode("\t",$data_headers)."\n";
	foreach($data_array as $k=>$v){
	$delim_string.=implode("\t",$v)."\n";
	}
 
//make time dependent xls name that is always unique
$xlsfile = "excel_example".date("m-d-Y-hiA").".xls";
//stop the browser displaying the data as text
//force the browser to download as xcel document
header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=$xlsfile");
 
print $delim_string;
 
?>

Download above Example script

To make advanced excel format like colors,cell formats,work book you can code with PEAR excel package.some opensource packages are available. please refer below for advanced excel convertion.

Download above Examples scripts

wordpress custom page template

Posted on July 14, 2009 by mahesh chari

How to add custom template to wordpress page or post

It is need to some pages of wordpress should be different for different pages or categories as per client requirements.but wordpress theme looks common for all pages and all posts. here we find simple steps to customize the wordpress theme templates.
just make custom template with your requirement.
add below code to start of the file.
upload it to wp_content/thems/your active theme folder via any FTP program.
go to admin panel of your wordpress and click add new link on pages left navigation.
then you can see a select box of templates on below attributes section on right side.
select custompagetemplate from template.
publish the page.
you can see a button view page.click it.
its done.

<?php
/*
Template Name: custompagetemplate
*/
?>

Simple Code

<?php
/*
Template Name: custompagetemplate
*/
<html>
<body>
<h1>This is your customized template .</h1>
</body>
</html>
?>

Download Example Template

custometemplate

long url vs short url

Posted on July 11, 2009 by mahesh chari

Long URL Vs Short URL

We know the fact that present content available in the internet is from web applications not web site! We see many advanced Server side scripts like PHP, Perl, and Java etc. that generates most of the dynamic content on the web.
For example http://www.wikipedia.org/ is a largest data collection or knowledge base. This data can be found through URL on the web. So URL is the key point to dynamic content. Generally we do this with a dynamic parameters append to URL and fetch the data from the server according to parameter. We can also use it for short time, like user activation, forgot password, reset password. So we have some information about URL.

Here we discuss about the length of the URL of the web site. As web developer we ensure that our web application work in all web browsers, all plat forms and all server. Off course it is difficult to ensure it, but we work at least an environment like lowest version of the browser, server, and operating system.

Dyamic URL

Example URL:
http://www.maheshchari.com/products/items.php?id=x&model=y&variety=z
http://www.maheshchari.com/activatepage.php?username=x&email=y&reset=z ….etc

Disadvantages

the length of the URL must work at least IE6, that accepts max length of 2083.
Some servers like Apache sends HTTP 413 error, not sure it depends for different versions and plat forms.Long URLs usually break when quoted in an e-mail message, and then don’t work.
In the above URL the data x, y, z vary to different situations, so it may exceed limit.
It leads some times as security issue.
user can’t remember and type the URL

Best practices

make ensure the data is less.
user URL shortening web services.
encode number of parameters to single parameters.

To make small URL with PHP see how we implement it below code

encoding to Short URL

//making uniform data length link
//we encrypt user data to base 64 codeformat and urlencode format
$param=urlencode(base64_encode('username=x&email=y&reset=z'));
$url='http://www.websitename.com/activate.php?param='.$param;
echo $url;
//output is:http://www.websitename.com/activate.php?param=dXNlcm5hbWU9eCZlbWFpbD15JnJlc2V0PXo%3D 
//we can also  use available url shortening web services like tiny.url ..etc
//they provide like tinyurl.com/xeuess

Decoding to long URL

// decoding and parse to GET method
$getparam=$_GET['param'];
$decoded_param=base64_decode(urldecode($getparam));
echo $decoded_param;
//output :username=x&email=y&reset=z
//we delete this $_GET['param']
//re assign global $_GET variable
parse_str($decoded_param,$_GET);
echo $_GET['username'];
//output is: x

Static URL

Example URL

http://www.maheshchari.com/articles/how-to-become-a-rich-man-with-in-one-day-by-author-example/

http://www.maheshchari.com/articles/worlds-largest-mountains-in-india-and-other-countries-polls-for-2000/

It is a well-known fact nowadays that without SEO a Web site stands many chances of not being indexed by search spiders. that results poor conversion rate.my colleague who is SEO expert had this type issue in past, that he used like above URL in his SEO or search engine optimization as it gives more chances of indexing in most search engine. but result is poor.but it wrong since he gave full URL and the page content is not much relevant to this URL. it may leads some times penalize or some times it may be spammed.

Best practices

Keep URL small as possible as less than 5 to 6 words and make it for human readable not for bots
Keep URL most relevant to the content.
Keep highest keyword starting position even though grammatical mistake.
There is some online web services available that suggests URL according to the content.
Use URL shortening web services while campaign .

break long post wordpress

Posted on July 10, 2009 by mahesh chari

How to Break A post or page on wordpress

Actually i have this problem while posting articles in this blog. so i googled the codex of wordpress and discovered solution.
just simple,while posting just decide where you want break the post or page, place a comment like below,its over worderess symptomatically splits into pages.

Code

<!–nextpage–>

Output

Page 1,2,3..

regular expressions reference

Posted on July 10, 2009 by mahesh chari

Regular Expressions Reference or Cheat Sheet

The regular expression, as a pattern, can match all kinds of text strings helping our web application validate, compare, sanitize data, compute, decide , format , data extraction etc. It can do simple or very complex string manipulations. The list of possibilities is enormous when it comes to what you can achieve using regular expressions.Below table shows a quick referece to basic syntax , definations and examlple.

Character	Definition	Example
^	The pattern has to appear at the beginning of a string.	^cat matches any string that begins with cat
$	The pattern has to appear at the end of a string.	cat$ matches any string that ends with cat
.	Matches any character.	cat. matches catT and cat2 but not catty
[]	Bracket expression. Matches one of any characters enclosed.	gr[ae]y matches gray or grey
[^]	Negates a bracket expression. Matches one of any characters EXCEPT those enclosed.	1[^02] matches 13 but not 10 or 12
[-]	Range. Matches any characters within the range.	[1-9] matches any single digit EXCEPT 0
?	Preceeding item must match one or zero times.	colou?r matches color or colour but not colouur
+	Preceeding item must match one or more times.	be+ matches be or bee but not b
*	Preceeding item must match zero or more times.	be* matches b or be or beeeeeeeeee
()	Parentheses. Creates a substring or item that metacharacters can be applied to	a(bee)?t matches at or abeet but not abet
{n}	Bound. Specifies exact number of times for the preceeding item to match.	[0-9]{3} matches any three digits
{n,}	Bound. Specifies minimum number of times for the preceeding item to match.	[0-9]{3,} matches any three or more digits
{n,m}	Bound. Specifies minimum and maximum number of times for the preceeding item to match.	[0-9]{3,5} matches any three, four, or five digits
\|	Alternation. One of the alternatives has to match. ‘or’ operater	July (first\|1st\|1) will match July 1st but not July 2
POSIX Character Classes
Character	Definition	Example
[:alnum:]	alphanumeric character	[[:alnum:]]{3} matches any three letters or numbers, like 7Ds
[:alpha:]	alphabetic character, any case	[[:alpha:]]{5} matches five alphabetic characters, any case, like aBcDe
[:blank:]	space and tab	[[:blank:]]{3,5} matches any three, four, or five spaces and tabs
[:digit:]	digits	[[:digit:]]{3,5} matches any three, four, or five digits, like 3, 05, 489
[:lower:]	lowercase alphabetics	[[:lower:]] matches a but not A
[:punct:]	punctuation characters	[[:punct:]] matches ! or . or , but not a or 3
[:space:]	all whitespace characters, including newline and carriage return	[[:space:]] matches any space, tab, newline, or carriage return
[:upper:]	uppercase alphabetics	[[:upper:]] matches A but not a
Perl-Style Metacharacters
Character	Definition	Example
//	Default delimiters for pattern	/colou?r/ matches color or colour
i	Append to pattern to specify a case insensitive match	/colou?r/i matches COLOR or Colour
\b	A word boundary, the spot between word (\w) and non-word (\W) characters	/\bfred\b/i matches Fred but not Alfred or Frederick
\B	A non-word boundary	/fred\B/i matches Frederick but not Fred
\d	A single digit character,this is equivalent to the class [0-9].	/a\db/i matches a2b but not acb
\D	A single non-digit character,this is equivalent to the class [^0-9].	/a\Db/i matches aCb but not a2b
\n	The newline character. (ASCII 10)	/\n/ matches a newline
\r	The carriage return character. (ASCII 13)	/\r/ matches a carriage return
\s	A single whitespace character,this is equivalent to the class [^ \t\n\r\f\v].	/a\sb/ matches a b but not ab
\S	A single non-whitespace character,this is equivalent to the class [^ \t\n\r\f\v].	/a\Sb/ matches a2b but not a b
\t	The tab character. (ASCII 9)	/\t/ matches a tab.
\w	A single word character – alphanumeric and underscore,this is equivalent to the class [a-zA-Z0-9_].	/\w/ matches 1 or _ but not ?,
\W	A single non-word character,this is equivalent to the class [^a-zA-Z0-9_].	/a\Wb/i matches a!b but not a2b

jquery ajax error handling

Posted on July 9, 2009 by mahesh chari

How to handle ajax errors using jQuery ?

jQuery is the most awesome javascript library that made easy for asynchronous ajax calls.it has global ajax function and some pre defined ajax functions like $.get, $.post, load .etc. but we don’t find any error messages by default with this library. we can see the errors with firefox’s addon firebug or with IE developer toolbar.So we manage most common ajax errors in our application with global ajaxSetup function which come with jQuery by default. We can set many options,to see available ajaxsetup options please check here.

most ajax errors we get errors are server response errors and some JSON parse errors. To know more about the HTTP errors details please check here. To test this error handling just copy and paste it to your head section of HTML document.

Javascript Code for setting global error handling.

$().ready(function(){
	$.ajaxSetup({
		error:function(x,e){
			if(x.status==0){
			alert('You are offline!!\n Please Check Your Network.');
			}else if(x.status==404){
			alert('Requested URL not found.');
			}else if(x.status==500){
			alert('Internel Server Error.');
			}else if(e=='parsererror'){
			alert('Error.\nParsing JSON Request failed.');
			}else if(e=='timeout'){
			alert('Request Time out.');
			}else {
			alert('Unknow Error.\n'+x.responseText);
			}
		}
	});
});

In the above example we handle following common error and show response text for other rare errors.

0-xhr status ,is it initialized or not that means user is offline.
404-page not found
500-Internel Server error.
request timeout – jQuery custom error.
parseerror-jQuery custom error when parsing JSON data.
we just throw other errors with response error

Example1

1 2	params={name:'mahesh'}; $.get(url,params,function(){})

In above example,jQuery automatically raises all available errors,except parse error. because we didn’t give any return type format.

Example2

1
2
3

params={name:'mahesh'};
$.get(url,params,function(){},'json');
$.getJSON(url,params,function(){});

In above example,jQuery automatically raises all available errors,because we added return type format at end. $.getJSON automatically adds the return type of parameter to “JSON”

Example3

1 2	params={name:'mahesh'}; $.get(url,params,function(){},'xml');

In the above example 3 ,we gave xml type return parameter, here also jQuery automatically raises all errors even parse error also.

stop browser cache

Posted on July 4, 2009 by mahesh chari

How to stop the web browser caching the document

All web documents, media and other web resources retrieved by a Web browser, are often saved, or cached, locally on the user’s hard drive. The next time that media or web resources is requested the Web browser may load the file from the cache instead of downloading it over the internet. This might be desirable for a some web document content doesn’t change often but undesirable for documents are updated frequently with new content or information.
All web servers sends to the browser with some basic information about the document before it sent .in the above example we can see some of them.for full details please refer here . we can use this server side headers to prevent browser caching .we learn here only PHP related headers.

preventing browser cache with javascript.

Here i am showing basic jQuery plugin that appends a random time to each links in document.

(function($){
	$.fn.nocachelink=function(options){
	//itereate trough each element in given element collection
		return this.each(function(){
			var href=$(this).attr('href');
			if(href.lastIndexOf('?')!=-1){ //if query string is set or not for link
			href=href+'?'+new Date().getTime();
			}else{
			href=href+'&'+new Date().getTime();
			}
			$(this).attr('href',href); // update new href attribute
		});
	}
})(jQuery);

An example how to use it

$().ready(function(){
	$('.nocache').nocachelink();
});

preventing browser cache with meta tags.

to prevent cache by means of html meta tags add following tags to document head section.

<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Expires" content="-1">
<meta http-equiv="CACHE-CONTROL" content="NO-CACHE">

preventing browser cache with server side headers.

we can also use HTTP headers by php also as below,remember you must add this lines to start of code.

//  @-silenced or error suppress operater
	@header( 'Expires: Wed, 11 Jan 1984 05:00:00 GMT' );
	@header( 'Last-Modified: ' . gmdate( 'D, d M Y H:i:s' ) . ' GMT' );
	@header( 'Cache-Control: no-cache, must-revalidate, max-age=0' );
	@header( 'Pragma: no-cache' );

preventing browsers cache with ajax calls.

we can also set headers with ajax response headers.

preventing browser cache with other methods.

By default all browser don’t cache SSL layered documents.
below example document won’t cached by browsers.

https://www.yourdomain.com

Attitude Is Everything!!!!!

Posted on July 4, 2009 by krishnapriya

Attitude…..

An old man lived alone in Minnesota . He wanted to spade his potato garden, but it was very hard work. His only son, who would have helped him, was in prison. The old man wrote a letter to his son and mentioned his situation:

Dear Son,
I am feeling pretty bad because it looks like I won’t be able to plant my potato garden this year. I hate to miss doing the garden because your mother always loved planting time. I’m just getting too old to be digging up a garden plot. If you were here, all my troubles would be over. I know you would dig the plot for me, if you weren’t in prison.

Love,Dad Shortly, the old man received this telegram:

‘For Heaven’s sake, Dad, don’t dig up the garden!! That’s where I buried the GUNS!!‘,at 4 a.m. the next morning, a dozen FBI agents and local police officers showed up and dug up the entire garden without finding any guns.

Confused, the old man wrote another note to his son telling him what had happened, and asked him what to do next.

His son’s reply was: Go ahead and plant your potatoes, Dad. It’s the best I could do for you, from here.

NO MATTER WHERE YOU ARE IN THE WORLD, IF YOU HAVE DECIDED TO DO SOMETHING DEEP FROM YOUR HEART, YOU CAN DO IT. IT IS THE THOUGHT THAT MATTERS, NOT WHERE YOU ARE OR WHERE THE PERSON IS.

real ip address

Posted on June 30, 2009 by mahesh chari

A simple function to find the real IP address of the client with php

As the internet growing as long as spamming,hacking,fishing,bots also increasing,there by normal user can easly decepted by with them. in many security application we need to find the real ip address to ban or record the ip related activities.here we use small function that returns and modify the PHP global remote address variable.

Usually we’ll use $_SERVER['REMOTE_ADDR'] to get clients IP address. But this doesn’t return the real IP address all time. Basically when someone using PROXY that valiable returns proxy IP the client using. So here’s a function you can use to detect real ip address of client. Here are extra Server variable which might be available to determine the exact IP address of the client’s machine in PHP, they are HTTP_CLIENT_IP and HTTP_X_FORWARDED_FOR

function real_ip(){
	if(isset($_SERVER['HTTP_X_FORWARDED_FOR'])){
		$_SERVER['REMOTE_ADDR']=$_SERVER['HTTP_X_FORWARDED_FOR'];
	}elseif(isset($_SERVER['HTTP_X_REAL_IP']){
		$_SERVER['REMOTE_ADDR']=$_SERVER['HTTP_X_REAL_IP'];
	}
return $_SERVER['REMOTE_ADDR'];
}

check ajax request

Posted on June 28, 2009 by mahesh chari

how to find the given request from ajax with php

In my recent project ,i have to restrict a page from direct accessing from browser with typing address on browser .this page must only accessible with ajax only.thank god there is a solution to find this with php server global variable that works with php5.then i made it a simple following function.

function is_Ajax() {
return (isset($_SERVER['HTTP_X_REQUESTED_WITH']) &&
($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest'));
}

How to export tabular data from php / mysql to MS excel

Code I

Code II

How to add custom template to wordpress page or post

Simple Code

Download Example Template

Long URL Vs Short URL

Dyamic URL

Disadvantages

Best practices

encoding to Short URL

Decoding to long URL

Static URL

Best practices

How to Break A post or page on wordpress

Code

Output

Regular Expressions Reference or Cheat Sheet

POSIX Character Classes

Perl-Style Metacharacters

How to handle ajax errors using jQuery ?

Javascript Code for setting global error handling.

Example1

Example2

Example3

How to stop the web browser caching the document

preventing browser cache with javascript.

preventing browser cache with meta tags.

preventing browser cache with server side headers.

preventing browsers cache with ajax calls.

preventing browser cache with other methods.

Attitude…..

A simple function to find the real IP address of the client with php

how to find the given request from ajax with php