Monday, April 19, 2010

PHP Cookies

All about Cookies


Cookie is nothing but a small text file that is embeded by server into user's computers each time the computer requests the page through browser.

Cookies can store information in them and they are stored on client side. This way they help us in many ways like storing user preferences, visited sites, used to identify users etc ...

How to Create a Cookie?
We can basically set a cookie by using setcookie() function.

bool setcookie( string $name [, string $value [, int $expire = 0 [, string $path [, string $domain [, bool $secure = false [, bool $httponly = false ]]]]]] )

  • name – name of the cookie.
  • value - The value of the cookie. Assuming the name is 'cookiename', this value is retrieved through $_COOKIE['cookiename']
  • expire - The time the cookie expires. Time()+60*60*24*30 will set the cookie to expire in 30 days. If set to 0, or omitted, the cookie will expire at the end of the session (when the browser closes)
  • path - The path on the server in which the cookie will be available on. If set to '/', the cookie will be available within the entire domain. The default value is the current directory that the cookie is being set in.
  • domain - The domain that the cookie is available. Example : '.example.com'
  • secure - When set to TRUE, the cookie will only be set if a secure connection exists.


Setcookie() defines a cookie to be sent along with the rest of the HTTP headers. Like other headers, cookies must be sent before any output from your script (this is a protocol restriction). This requires that you place calls to this function prior to any output, including html and head tags as well as any whitespace.
Once the cookies have been set, they can be accessed on the next page load with the $_COOKIE or $HTTP_COOKIE_VARS arrays.

setcookie("myCookie", "PHP Tutorial", time()+3600, "/tutorials");

How to Retrieve a Cookie Date?
Now the cookie is set and we need to retrieve the information. As mentioned above the name of each cookie sent by your server accessed with the superglobal array $_COOKIE. In the example below we retrieve the value of the cookie and print out its value on the screen.

echo "The cookie value is ".$_COOKIE['myCookie'];

This would show up on the page as: "myCookie value is PHP Tutorial".

How to Delete a Cookie?
By default, the cookies are set to be deleted when the browser is closed. We can override that default by setting a time for the cookie's expiration but there may be occasions when you need to delete a cookie before the user closes his browser, and before its expiration time arrives. To do so, you should assure that the expiration date is in the past. The example below demonstrates how to do it (setting expiration time 1 minute ago):

setcookie("myCookie", "", time()-60);

What Are Cookies Used For?
One use of cookies is for storing passwords and user ID's for specific web sites. Also, they are used to store preferences of start pages. On sites with personalized viewing, your web browser will be requested to utilize a small amount of space on your computer's hard drive to store these preferences. That way, each time you log on to that web site, your browser will check to see if you have
any pre-defined preferences (a cookie) for that unique server. If you do, the browser will send the cookie to the server along with your request for a web page. Microsoft and Netscape use cookies to create personal start pages on their web sites. Common uses for which companies utilize cookies include: on-line ordering systems, site personalization, and web site tracking.
Site personalization is one of the most beneficial uses for cookies. For example, a person comes to the CNN site, but does not want to see any business news. The site allows the person to select this choice as an option. From then on (or until the cookie expires), the person would not see business news when they access the CNN web pages.

Thursday, April 15, 2010

Selecting from a sub-query

postgres=#
postgres=# CREATE TABLE employee (
postgres(# ID int,
postgres(# name varchar(10),
postgres(# salary real,
postgres(# start_date date,
postgres(# city varchar(10),
postgres(# region char(1)
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (1, 'Jason', 40420, '02/01/94', 'New York', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (2, 'Robert',14420, '01/02/95', 'Vancouver','N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (3, 'Celia', 24020, '12/03/96', 'Toronto', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (4, 'Linda', 40620, '11/04/97', 'New York', 'N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (5, 'David', 80026, '10/05/98', 'Vancouver','W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (6, 'James', 70060, '09/06/99', 'Toronto', 'N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (7, 'Alison',90620, '08/07/00', 'New York', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (8, 'Chris', 26020, '07/08/01', 'Vancouver','N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (9, 'Mary', 60020, '06/09/02', 'Toronto', 'W');
INSERT 0 1
postgres=#
postgres=# select * from employee;
id | name | salary | start_date | city | region
----+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W
2 | Robert | 14420 | 1995-01-02 | Vancouver | N
3 | Celia | 24020 | 1996-12-03 | Toronto | W
4 | Linda | 40620 | 1997-11-04 | New York | N
5 | David | 80026 | 1998-10-05 | Vancouver | W
6 | James | 70060 | 1999-09-06 | Toronto | N
7 | Alison | 90620 | 2000-08-07 | New York | W
8 | Chris | 26020 | 2001-07-08 | Vancouver | N
9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows)

postgres=#
postgres=# -- Selecting from a sub-query
postgres=#
postgres=# SELECT 'test' AS test, id
postgres-# FROM (SELECT id FROM employee)
postgres-# AS example_sub_query;
test | id
------+----
test | 1
test | 2
test | 3
test | 4
test | 5
test | 6
test | 7
test | 8
test | 9
(9 rows)

postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=#
postgres=#
postgres=#

A simple sub-query - subquery return one value

A simple sub-query: subquery return one value

postgres=#
postgres=# create table job(
postgres(# ID int,
postgres(# title varchar (10));
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into job(ID, title) values(1,'Developer');
INSERT 0 1
postgres=# insert into job(ID, title) values(2,'Tester');
INSERT 0 1
postgres=# insert into job(ID, title) values(3,'Designer');
INSERT 0 1
postgres=# insert into job(ID, title) values(4,'Programmer');
INSERT 0 1
postgres=#
postgres=# select * from job;
id | title
----+------------
1 | Developer
2 | Tester
3 | Designer
4 | Programmer
(4 rows)

postgres=#
postgres=# CREATE TABLE employee (
postgres(# ID int,
postgres(# name varchar(10),
postgres(# salary real,
postgres(# start_date date,
postgres(# city varchar(10),
postgres(# region char(1)
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (1, 'Jason', 40420, '02/01/94', 'New York', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (2, 'Robert',14420, '01/02/95', 'Vancouver','N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (3, 'Celia', 24020, '12/03/96', 'Toronto', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (4, 'Linda', 40620, '11/04/97', 'New York', 'N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (5, 'David', 80026, '10/05/98', 'Vancouver','W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (6, 'James', 70060, '09/06/99', 'Toronto', 'N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (7, 'Alison',90620, '08/07/00', 'New York', 'W');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (8, 'Chris', 26020, '07/08/01', 'Vancouver','N');
INSERT 0 1
postgres=# insert into employee (ID, name, salary, start_date, city, region)
postgres-# values (9, 'Mary', 60020, '06/09/02', 'Toronto', 'W');
INSERT 0 1
postgres=#
postgres=# select * from employee;
id | name | salary | start_date | city | region
----+--------+--------+------------+-----------+--------
1 | Jason | 40420 | 1994-02-01 | New York | W
2 | Robert | 14420 | 1995-01-02 | Vancouver | N
3 | Celia | 24020 | 1996-12-03 | Toronto | W
4 | Linda | 40620 | 1997-11-04 | New York | N
5 | David | 80026 | 1998-10-05 | Vancouver | W
6 | James | 70060 | 1999-09-06 | Toronto | N
7 | Alison | 90620 | 2000-08-07 | New York | W
8 | Chris | 26020 | 2001-07-08 | Vancouver | N
9 | Mary | 60020 | 2002-06-09 | Toronto | W
(9 rows)

postgres=#
postgres=#
postgres=# -- A simple sub-query
postgres=#
postgres=# SELECT name FROM employee WHERE id = (SELECT id FROM job where title = 'Developer');
name
-------
Jason
(1 row)

postgres=#
postgres=#
postgres=# drop table employee;
DROP TABLE
postgres=# drop table job;
DROP TABLE
postgres=#
postgres=#

Why are docx, xlsx, pptx downloading from webserver as zip files

The new Office 2007 file formats are now using an Open XML file format system so they are more compatible with other office programs from Google, Open Office...etc. Essentially they are ZIP files that are full of XML files that when opened with the a proper application turn into a friendly word document. While that's pretty nice of Microsoft, they haven't been to good about release MIME type information for these file formats which can cause some interesting issues when trying to upload and download them to a webserver.

In my case when i was trying to download a xlsx file they would save as .zip files. When they tried to open them up they were just a bunch of XML files which to the untrained eye would be a bunch of gobbledygook.

There are couple of solutions for this. I tried with both of these and was successful. Anyone with similar issue can try either of these. Hope it saves your time and energy :)

Here is the list of Office 2007 MIME type associations. You can add all these in your .htaccess and it should work.

AddType application/vnd.ms-word.document.macroEnabled.12 .docm
AddType application/vnd.openxmlformats-officedocument.wordprocessingml.document docx
AddType application/vnd.openxmlformats-officedocument.wordprocessingml.template dotx
AddType application/vnd.ms-powerpoint.template.macroEnabled.12 potm
AddType application/vnd.openxmlformats-officedocument.presentationml.template potx
AddType application/vnd.ms-powerpoint.addin.macroEnabled.12 ppam
AddType application/vnd.ms-powerpoint.slideshow.macroEnabled.12 ppsm
AddType application/vnd.openxmlformats-officedocument.presentationml.slideshow ppsx
AddType application/vnd.ms-powerpoint.presentation.macroEnabled.12 pptm
AddType application/vnd.openxmlformats-officedocument.presentationml.presentation pptx
AddType application/vnd.ms-excel.addin.macroEnabled.12 xlam
AddType application/vnd.ms-excel.sheet.binary.macroEnabled.12 xlsb
AddType application/vnd.ms-excel.sheet.macroEnabled.12 xlsm
AddType application/vnd.openxmlformats-officedocument.spreadsheetml.sheet xlsx
AddType application/vnd.ms-excel.template.macroEnabled.12 xltm
AddType application/vnd.openxmlformats-officedocument.spreadsheetml.template xltx

.manifest, application/manifest
.xaml, application/xaml+xml
.application, application/x-ms-application
.deploy, application/octet-stream
.xbap, application/x-ms-xbap

.docm, application/vnd.ms-word.document.macroEnabled.12
.docx, application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotm, application/vnd.ms-word.template.macroEnabled.12
.dotx, application/vnd.openxmlformats-officedocument.wordprocessingml.template
.potm, application/vnd.ms-powerpoint.template.macroEnabled.12
.potx, application/vnd.openxmlformats-officedocument.presentationml.template
.ppam, application/vnd.ms-powerpoint.addin.macroEnabled.12
.ppsm, application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.ppsx, application/vnd.openxmlformats-officedocument.presentationml.slideshow
.pptm, application/vnd.ms-powerpoint.presentation.macroEnabled.12
.pptx, application/vnd.openxmlformats-officedocument.presentationml.presentation
.xlam, application/vnd.ms-excel.addin.macroEnabled.12
.xlsb, application/vnd.ms-excel.sheet.binary.macroEnabled.12
.xlsm, application/vnd.ms-excel.sheet.macroEnabled.12
.xlsx, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltm, application/vnd.ms-excel.template.macroEnabled.12
.xltx, application/vnd.openxmlformats-officedocument.spreadsheetml.template


Or, you can just add the following lines in your apache's conf/mime.types file.

application/vnd.ms-word.document.macroEnabled.12 docm
application/vnd.openxmlformats-officedocument.wordprocessingml.document docx
application/vnd.openxmlformats-officedocument.wordprocessingml.template dotx
application/vnd.ms-powerpoint.template.macroEnabled.12 potm
application/vnd.openxmlformats-officedocument.presentationml.template potx
application/vnd.ms-powerpoint.addin.macroEnabled.12 ppam
application/vnd.ms-powerpoint.slideshow.macroEnabled.12 ppsm
application/vnd.openxmlformats-officedocument.presentationml.slideshow ppsx
application/vnd.ms-powerpoint.presentation.macroEnabled.12 pptm
application/vnd.openxmlformats-officedocument.presentationml.presentation pptx
application/vnd.ms-excel.addin.macroEnabled.12 xlam
application/vnd.ms-excel.sheet.binary.macroEnabled.12 xlsb
application/vnd.ms-excel.sheet.macroEnabled.12 xlsm
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet xlsx
application/vnd.ms-excel.template.macroEnabled.12 xltm
application/vnd.openxmlformats-officedocument.spreadsheetml.template xltx


Hope this helps. Happy coding!

Tuesday, April 13, 2010

How to Install Google Analytics on Blogger


Wouldn’t it be nice to know how many people actually visited your blog each day? How about knowing which of your posts are the most popular? Well surprisingly Blogger doesn’t currently offer any native blog analytics but fortunately there is Google Analytics — available for free.


Google Analytics is a great tool that will show you all sorts of useful information about how people found your blog, keywords they searched on to find you, where they came from and even how long they stayed. You’ll learn more about where your visitors come from and how they interact with your blog. This is a must tool if you are trying to build a profitable blog otherwise you are flying blind!
This article will show you how to add Google Analytics to your Blogger blog. It’s a fairly simple process and doesn’t require any technical skill at all.


Step #1 – Setup a Google Analytics Account

Go to Google Analytics and sign in using your Blogger login. If your account doesn’t work for some reason, you can create a new one instead. Once you login you’ll see a screen that looks like this:


Click on the “Sign Up >>” button and proceed to the next step which will ask you for your general information. Website URL, Account Name, Country, and Time Zone. The screen will look like this:


If you noticed, I just put my Blogger url (without the http://) and called it “David’s Account” because this is your top-level container for 1 or 100 different websites so it’s a good idea to name it something more general. The next step will ask you for your contact information which includes your first name, last name, phone number, and country. Easy so far right?


Your last step in signing up will ask you to accept the user agreement terms and conditions which you should read (just kidding…who actually ever reads these entire legal terms anyhow?).


Now this next screen is very important. This is the code you will need to copy and paste into your blogger template. Go ahead and click into the box and it will automatically highlight the entire block of code for you. Now you need to copy that code and paste it into notepad or into a MS Word document. Save it as you’ll need to use it later.


After you click on the “continue >>” button you will be taken to your brand new Google Analytics dashboard! You will see your blogger blog listed but with no analytical data….yet.


Ok, now you are done with setting up your Google Analytics account. The next step is placing the tracking code into your Blogger template so it can report back to Google Analytics and provide you with some cool data points.


Step #2 – Adding GA Tracking Code to Your Blogger Template

This is not a very difficult step even if you are afraid to touch your template code. Login to your Blogger account and then click on the “Layout” => “Edit HTML” tabs. This will bring you to the template code. Before you make any changes, I advice you to back up your template just in case there are any problems. After you’ve done so, continue reading.


Now in the edit template html code window, scroll all the way to the bottom of your template code and look for the body tag. There should only be one of these closing tags in your template. If you can’t find it then your template wasn’t properly created and you should add one right above the html tag.The html tag should always be your last line of code in your template. It signifies the end of your template.


Ok, now go back to the code you saved before in a Word Doc from Google Analytics. You are going to copy it and paste it right above the body tag as illustrated in the image below. The yellow highlighted code is the new GA code I just pasted into my template.


Save your template and you shouldn’t get any error messages. If you do, it’s most likely not related to this GA code and something else with your template itself. Assuming you’ve been successful with your save, you are all done embedding the GA code in your template!


Step #3 – Confirm Google Analytics is Tracking

Go back into your Google Analytics account and look at your dashboard. Most likely you will see a little yellow exclamation mark under the “status” column that looks like this: . This means everything isn’t working properly yet which is fine because we are about to tell GA we just added the code. From your dashboard, click on the “edit” link which is located to the far right.


After you click on that link, you’ll see another screen like below. It will say “Tracking Not Installed” followed by a link “Check Status”. You’ll want to go ahead and click on that link which will tell GA to visit your site and look for the new code you just pasted in your template.


Assuming you pasted the code in there as instructed above, GA will find the new code and begin tracking everything on your blog. If you are still having problems, it’s most likely something to do with GA and you should read their help guide to troubleshoot your problem.


The message seen here, “Waiting for Data” means you have correctly setup GA and data is being gathered! Click on the top left Google Analytics logo and it will take you back to your dashboard. From there click on the “View report” link and that’s where all your very important Blogger visitor data will start appearing!


Now it usually takes an hour or so before you will see any data (maybe longer if you don’t get much traffic to your blog) so please be patient. Trust me, you’ll be logging in at least once a day just to see how much traffic your blog is getting. It’s very addicting and powerful information to learn from. You’ll be surprised which posts are your most popular and what countries people are coming from to read your blog.


Google Analytics is very powerful and we have just learned how to install it into your Blogger template. We haven’t even scratched the surface on the features and reporting it can do. For most Bloggers, this will be enough. Data will be collected and you will just review it. Others with more in-depth goals (like selling products or services, creating a sales funnel, etc) will want to spend more time learning GA. Hope you enjoyed the tutorial and don’t blame me for your new found addiction!