Category Archives: Misc

Zipcodes in CSV Generation

When exporting to CSV format, then opening in a spreadsheet program like Excel zipcodes that start with a zero or zeroes have the preceding zeros stripped off. Obviously it is because the spreadsheet sees that column as integers and preceding zeros in integers are useless.

A quick and dirty trick to force Excel (hopefully you are using OpenOffice) to display the full zipcode, we wrap it in double quotes and put an equal sign in front of it, to force it to be a string like this:

$zipcode = 00123;
$data = '="' . $zipcode . '"' ;

So if you are doing the straight query to CSV export, using the fputcsv function it would look something like this. Basically just overwrite the value in the row and then continue along.

while ($row = mysql_fetch_assoc($query)){
	$row['zipcode'] = '="'.$row['zipcode'].'"';
	fputcsv($output, $row);

Getty Images Suing Website Owners

If I didn’t see the correspondence myself, I probably would not have believed it. Yes, Getty Images is suing website owners for using their images supposedly without paying for the rights. They have a software that crawls webpages, using an algorithm it can detect Getty images even if they have been cropped, or otherwise slightly altered. The images need not have the Getty watermark to be detected. That’s fine so they find people using their images and then send them a cease and desist letter, right? No they are demanding upwards of 20 times the original image price, threatening legal action. For this reason I will never use Getty Images and will continue to use iStockPhoto. Turns out that Getty acquired iStock photo… Anyone have other suggestions on who to use for low-cost stock photos?

Here is an interesting website devoted to the topic: Getty Extortion Letter


I had someone ask me the other day why they were getting 2147483647 as the value for some operation. Any time you see this number, it should be a red flag that you are hitting the ceiling of the values that can be the 4 byte integer.

So here in this classic newbie mistake, grooming a phone number by casting it to an integer.

$phone = '6195551234';
echo (int) $phone;

Will result in 2147483647.

You also see 2147483647 sometimes in database records where the field type is INT and the value you are inserting is greater than 2147483647. Hope this helps!

Google Analytics API

Unfortunately Google Analytics does not have an API yet… However I will show you a quick and dirty way to get the data you need. The idea is basically to setup an automated report from within Google Analytics, which will email a CSV file attachment, that you can download and parse with a script. For this example I will use the Zend Mail class because I love Zend Mail and it is free.

So the first step is to schedule the report. For this simple example we will use the default Visitors Report. So login to your Google Analytics account, click Visitors on the main left nav, then click the Email icon up at the top of the report, see screenshot below:

GA Screenshot
Google Analytics Screenshot

Now let’s get to the code. First step is to connect to your POP mail server and get the new messages. Don’t forget to download the latest version of Zend at

include ('Zend/Mail/Storage/Pop3.php');
$params = array('host' => "", 'user' => "", 'password' => "secret");
$mail = new Zend_Mail_Storage_Pop3($params);
$num = $mail->countMessages();

In a production environment, you would want some logic to verify that the number of messages is greater than 0, loop through the messages, etc but for this tutorial I have omitted these items for brevity. So next we need to download the first message and get its parts.

$message = $mail->getMessage(1);
$parts = $message->countParts();

Next we spin through the parts, looking for the attachment.

for($b=1; $b<=$parts; $b++){
	$part = $message->getPart($b);
	$headers = $part->getHeaders();
	if (!empty($headers['Content-Disposition']) || !empty($headers['content-disposition'])){

Then we parse the CSV file to an array for simple looping:

$csv = base64_decode($part->getContent());
$lines = explode("\n",$csv);
$data = array();
foreach ($lines as $line){
	$data[] = explode(',',$line);					

The previous code checks the email account, downloads the CSV attachment and parses it to an array. That is all going to stay the same but the matching below will change depending on your needs. For this simplistic example, I am going to get the number of visits from yesterday.

$yesterday = date('F j',mktime(0, 0, 0, date('m'), date('d')-1, date('Y')));
foreach ($data as $row){
	if ($row[0] == '"'.$yesterday){
		$visits = $row[2];
		echo "VISITS: {$visits}";

So that’s basically it. Of course in production you would probably want to write the Google Analytics data to a database or whatever, but you get the point! Hopefully Google Analytics will get an API soon, but until then, this works like a champ!

PPC Conversion Tracking using Javascript

PPC Conversion tracking (Google/Yahoo/MSN) is straightforward for simple HTML websites. You place the conversion pixel on the thank you page. But when you have conversions in Flash sites or when the page is not reloaded after a conversion in the case of an ajaxed site, firing the conversion pixel is not as simple.

I created a simple javascript function that will include the tracking pixel as an iframe in the current page. All you have to do is change the url to the tracking pixel and then call the conversion function from your page or flash file.

function conversion() {
	var iframe = document.createElement('iframe'); = '0px'; = '0px'; 
	iframe.src = '';

Google Analytics Goals

Goals in Google Analytics are one of my favorite features of this amazing free web analytics software. Goals allow you to track actions and then analyze how different traffic segments convert to these actions. Common actions would be completing a lead form, downloading a brochure, clicking the link for directions, etc. Goals are usually implemented by entering the page url where the conversion occurs (Ex: thankyou.html). So how can we implement the goal tracking on sites where the form is the same url as the thank you page, or where the click leaves the site or goes to a non-html page? These can all be done easily by selecting Exact Match as the Match Type and then inserting a fictitious url like /goal/rfi/ for the Goal URL.

Let’s look at the first scenario where someone completes a lead form. Often forms submit to themselves, so you can’t have the goal conversion on the thank you page because it is the same url. These type of dynamic pages can easily fire off goals we just have to do it a different way. When the form is completed successfully, we just make a javascript call in the onload. Notice the /goal/rfi/ from the Goal URL setup.

<body onload="pageTracker._trackPageview('/goal/rfi/');">

So what if you want to track an exit click or a non-html file download (Ex: PDF Brochure). The idea is the same, you simply call the function from the onclick event handler.

<a href="PrettyBrochure.pdf" onclick="pageTracker._trackPageview('/goal/rfi/');">

Be sure that you have the New Tracking Code (ga.js) not the Legacy Tracking Code (urchin.js)

SYLK: file format is not valid

I had a co-worker complain that she was unable to open a CSV file I was generating automatically using PHP. The error she was receiving was “SYLK: file format is not valid”. So I went to Google and searched for it and it turns out that Excel is such a POS that if the first two characters of the CSV file are ID (capitalized) then you will get this error. So I prepended an X to the file and that fixed it. I am fortunate enough to not use Microsoft Office for the past few years. I HIGHLY recommend OpenOffice and the best part is that it is FREE. OpenOffice can read and write the native Microsoft Office formats so you will not have problems shard documents with co-workers. If you are in IT you can save your company a ton in licensing by switching to OpenOffice and never get the ridiculous SYLK: file format is not valid error.

Manually Run Webalizer via SSH – webstatmng

In Plesk say you have a domain and you have not enabled webalizer. Later you need to enable it but no statistics show because it only parses the log files once per day but you want the stats right now. Here is the SSH command you can run to manually run webalizer.

/usr/local/psa/admin/bin/webstatmng –calculate-rotated-statistics –stat-prog=webalizer –

I am sure that this is different for every system, but this worked for me running Plesk 8.1 on Linux.

Automatically Login Bookmark

Here is a quick little trick to automate logging into online applications. Simply create an HTML file and put it on your desktop (or wherever you like), and then by opening this file, you can login to most applications automatically. All you have to change in the file is the url to post the form to and the field names of the values to post. In this simple demo the form will post the variables “username” and “password” to


Logging into Application…


Warning: This is not secure, as the password is stored in plain-text and is accessible to anyone with access to your computer. Please use with extreme caution.

PHP Array of US States

This simple array of states can be used to poulate a dropdown menu on a form, or in some kind of lookup from state code to name, or any loop action you desire.

$states = array('AL'=>"Alabama",  
			'DC'=>"District Of Columbia",  
			'NH'=>"New Hampshire",
			'NJ'=>"New Jersey",
			'NM'=>"New Mexico",
			'NY'=>"New York",
			'NC'=>"North Carolina",
			'ND'=>"North Dakota",
			'PR'=>"Puerto Rico",  
			'RI'=>"Rhode Island",  
			'SC'=>"South Carolina",  
			'SD'=>"South Dakota",
			'WV'=>"West Virginia",  

So a simple example to populate a dropdown form menu of states you could simply spin through the array like so:

foreach ($states as $code => $state){
     echo "<option value=\"{$code}\">{$state}</option>\n";