Chris Nizzardini, Salt Lake City Utah, Web Developer Specializing in LAMP+Ajax Since 2006

My Blog

Here is my awesome blog.

Debugging Your WebSite with Google Analytics Custom Reports

Posted by chris on August 28th, 2010 Comments(0)

I’m in Seattle for the SeoMoz Pro Training and figured I should do a blog post meshing SEO with debugging and development. I’m pretty excited about the SeoMoz Pro Training. My SEO skills are no where near my programming skills so this is an awesome opportunity to improve those skills, meet some of the leaders in the SEO field, and see Seattle for my first time.

Problem: Testing your site in multiple browser versions

Lets face it. Browser testing is a bitch. There are 3 versions of IE to test, Safari on Windows and Mac, Firefox, and now Chrome. It really sucks. I absolutely hate it. Recently, while doing a routine testing of a clients site I found an error causing a critical page to not render correctly in Internet Explorer 6. Needless to say this was negatively impacting the clients conversion rates.

Digging In with Analytics

Using Google Analytics I discovered that on this clients site, Internet Explorer (all versions) makes up about half of the total visits. Within IE about 10% of those visitors are still using version 6. So about 5% (about 800 total visits per month) of the clients total users are on Internet Explorer 6. When you consider this clients conversion rate is around 25% that comes up to a potential loss of 200 conversions per month! Digging in deeper I found that the IE6 conversion rate was only at about 7%. Using a quick CSS hack I was able to fix the problem (I used a B.S. IE CSS hack setting zoom:1 on a problematic CSS class).

Creating a Custom Report to Find Browser Errors

Go into custom reports and create a new custom report. Call this something like Browser Conversion Report and add the following metrics: Total Goal Starts, Total Goal Completions, and Goal Conversion Rate (these are found under the Goals metric). Now you will need to add some Dimensions so add Browser and then as a drill-down add Browser Version (both found under System).

Preview the report now.

Now drill down by clicking on a Browser type.

So did the the bug fix work? Absolutely, and now we have a report to back it up. The clients IE6 conversion rate is sitting at around 25% since fixing the CSS issue. Now we’ve created a way to easily monitor our conversion rates among browsers. You can get even more creative with this by adding in Operating System types, connection speeds, or whatever your geeky heart desires.

I’d love to blog all day, but I think its time to go see what Seattle has to offer.

In seo, software (, )

Sages Cafe Is Underwhelming

Posted by chris on August 24th, 2010 Comments(0)

I tried a vegan eat out popular to Salt Lake City the other night. I’d heard good things about this place from vegan and non-vegans alike. While I’m not completely upset with the meal I received it was very underwhelming. First impressions were very good, even though Sages Cafe sits in the heart of downtown Salt Lake it seemed a bit “off the beaten path” to me. I enjoyed the old style building it was in and found the decor to be quaint. We were seated within 5 minutes despite the place being moderately busy.

We started off with the bruschetta for an appetizer and some locally brewed beers. The bruschetta was great. It was a piece of vegan cheese sitting atop bread with some great sauce and sprinkled almond flakes. For our main course my girl friend got the Nut Burger (I knew this was a bad idea but a client had told her it was fantastic) and I got the Picadillo Vegetariano. The Nut Burger was nothing more than a conglomeration of nut paste resting between some wheat bread. Nuff said. Might as well have gotten a PB&J. My dinner was a decent stir fry, but there was nothing fantastic about the “vegan chicken” as it was obvious tofu that anyone could make. For $45.00 I felt we would have left filled up, but upon leaving our plates were as empty as our stomaches.

I’m willing to give this place a shot sometime in the future though. I’ve heard good things about the Magical Wok. At least it was nice to try something new. Now off to fill myself up for once tonight.

In beer/wine/vodka/food (, )

MySQL INNER JOIN on Multiple Parameters and Condtions

Posted by chris on August 19th, 2010 Comments(0)

Wow. This blog has gotten a bit lopsided as of late on the MySQL side of stuff. A symptom of all the MySQL wizardry I’ve been doing and learning at my new job. I found out today that you can add multiple parameters/conditions to an INNER JOIN statement and I feel kind of dumb for not knowing this before. Using multiple parameters on your inner join you can remove the need for nasty sub queries!

Take this query for instance:

SELECT DISTINCT
    wp_posts.*, wp_users.user_nicename, 
	((SELECT meta_value FROM wp_postmeta meta WHERE meta.meta_key = 'views' AND meta.post_id = wp_posts.ID)*1) as views
FROM
	article_to_categories
INNER JOIN
    wp_posts ON wp_posts.ID = article_to_categories.wp_post_id
INNER JOIN
    wp_users ON wp_users.ID = wp_posts.post_author
WHERE
    article_to_categories.categories_id = $id
ORDER BY
    views DESC

On a production server this query is sadly executing in 0.1 seconds. This is due almost entirely to that nasty little sub select in there. In my opinion, sub selects should be avoided at all cost. They are a symptom of at best an unoptimized query and at a worst a poorly designed database. Sometimes I’m a guilty party. Our goal is to get rid of the sub query:

SELECT meta_value FROM wp_postmeta meta WHERE meta.meta_key = 'views' AND meta.post_id = wp_posts.ID

We can do this by specifying multiple parameters and conditions on our join statement.

SELECT DISTINCT
    wp_posts.*, wp_users.user_nicename, (wp_postmeta.meta_value*1) as views 
FROM
    article_to_categories
INNER JOIN
	wp_posts ON wp_posts.ID = article_to_categories.wp_post_id 
INNER JOIN
	wp_users ON wp_users.ID = wp_posts.post_author 
INNER JOIN 
	wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_key = 'views') 
WHERE
    article_to_categories.categories_id = $id
ORDER BY
	views DESC

Notice that when joining on wp_postmeta I specify that I want all records matching wp_posts.ID and that match the meta_key views. By moving away from the sub select I was able to trim the query execution time to 0.03 seconds. Thats a massive improvement especially for a query that is run in bulk!

INNER JOIN 
	wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_key = 'views')

I’ve got a feeling I’ll need to go back through some of my older code and introduce some needed query optimizations. How about you?

In SQL (, )

What is PDO Error HY093

Posted by chris on August 11th, 2010 Comments(0)

I’ve been working with PDO at my new job and encountered this HY093 error. Infact that was the only information I received back from my PDO object. Usually there is a lot more information. My guess is that generally PDO is passing back the MySQL error directly from the MySQL server. However, HY093 is strictly an error issued by PDO.

In this case I received the HY093 error because I had improperly binded a data type to a variable.

I was using the form:

$db->prepare("SELECT * FROM tbl WHERE name = :name");
$pdo->bindValue(':name',$phpVar,PARAM_STR);
$pdo->execute();

Notice on line 2 I used “PARAM_STR” this should be PDO::PARAM_STR likewise had this been an integer it would have been PDO:PARAM_INT. Hopefully that helps some people.

In SQL, php (, )

How to Create a WordPress Plugin

Posted by chris on July 30th, 2010 Comment(1)

I recently had the opportunity to create a wordpress plugin for my company’s website. This is a simple plugin that associates blog posts to product pages on our shopping cart based on word press tags. The plugin simply displays links to the products page (based on the tags) on the wordpress post page for a given blog post.

First create a file in the wordpress plugins folder and add the following comments:

/*
Plugin Name: Article to Categories 
Plugin URI: http://www.foo.com/
Description: ...
Version: 1.0
Author: ...
Author URI: http://www.cnizz.com
License: ...
*/

Wordpress will read in this information on the plugin page. Next we are going to hook into the core wordpress operations. Prior to this project I didn’t think much of wordpress. I viewed it as a great blogging platform with really horrid looking code, but the WP developers have a done a great job creating a really robust API. This hook will tell wordpress to call the show_category_pages_meta_box() function when it loads the admin menu. Now we need to define that function.

add_action('admin_menu','show_category_pages_meta_box');

This function defines a new meta box with an element ID of myplugin_sectionid. The title is Article to Categories and it will be displayed on post pages only. When loaded it will call show_category_pages() which will actually echo out the text to appear in the new meta box.

function show_category_pages_meta_box(){
	add_meta_box( 'myplugin_sectionid', 'Article to Categories', 'show_category_pages', 'post', 'side', 'high');
}

Finally we need to define show_category_pages() and populate our new meta box.

function show_category_pages(){
echo 'Some awesome product pages!';
}

Creating this wordpress plugin was pretty fun and I hope I get a change to do it again in the future.

Resources:
WordPress: Writing a Plugin
Plugin API
Action Reference

In php, software (, )

Use MySQL HAVING to Get Past Error 1111 Invalid Use of Group Function

Posted by chris on June 22nd, 2010 Comments(0)

I was working on some new reporting features for a client this evening when I encountered MySQL Error 1111 Invalid Use of Group Function. I was a bit befuddled by this as I was simply using a SUM total in my WHERE clause. Of course after a bit of googling I found that I cannot use an aggregate function such as SUM or COUNT as a WHERE clause.

Here is my original query.

SELECT SQL_CALC_FOUND_ROWS 
  city.name AS city, state.abbrev AS state_abbrev, zip.zipcode, zip.city_id, COUNT(zip.zipcode) AS zip_count, SUM(assigned.is_primary) AS primary_count, SUM(assigned.is_backup) AS backup_count 
FROM 
  tbl_zipcode zip 
INNER JOIN 
  tbl_city city ON city.city_id = zip.city_id 
INNER JOIN 
  tbl_state state ON state.state_id = city.state_id 
LEFT JOIN 
  tbl_affiliate_assigned_location assigned ON assigned.zipcode = zip.zipcode 
WHERE 
  zip_count > primary_count 
GROUP BY 
  city.city_id 
ORDER BY 
  count(zip.zipcode) DESC, city.name ASC 
LIMIT 0,50

I fixed this using a HAVING clause on the GROUP BY.

SELECT SQL_CALC_FOUND_ROWS 
  city.name AS city, state.abbrev AS state_abbrev, zip.zipcode, zip.city_id, COUNT(zip.zipcode) AS zip_count, SUM(assigned.is_primary) AS primary_count, 
  SUM(assigned.is_backup) AS backup_count 
FROM 
  tbl_zipcode zip 
INNER JOIN 
  tbl_city city ON city.city_id = zip.city_id 
INNER JOIN 
  tbl_state state ON state.state_id = city.state_id 
LEFT JOIN 
  tbl_affiliate_assigned_location assigned ON assigned.zipcode = zip.zipcode 
GROUP BY 
  city.city_id HAVING zip_count > primary_count 
ORDER BY 
  count(zip.zipcode) DESC, city.name ASC 
LIMIT 0,50

The HAVING clause seems to be one of those things I use so rarely that I forget about it just before I need it again. Hopefully since I wrote on a blog on it this time it sticks around in my memory bank a bit longer. Hope this helped someone. I also found out that you can have multple HAVING clauses using the following syntax.

HAVING zip_count >  primary_count OR primary_count IS NULL
In SQL (, , , )

MySQL InnoDb inserts are slow, really slow!

Posted by chris on June 12th, 2010 Comments (6)

So I was doing some MySQL database optimization tests this morning when I got my database engines mixed up. Thats when I stumbled on something horrible….InnoDb is really slow when it comes to inserts! Like really slow. I performed 10,000 inserts on MyIsam and it executed in about 1/10th of a second. The same inserts on an InnoDb table took over 21 seconds! I was seriously dumbfounded by this. I knew MyIsam would be faster, but I figured it would be only nominally faster. Out of curiosity I did a single insert with MyIsam which executed in 0.000762939453125 seconds whereas the same insert in InnoDb executed in 0.032792091369629 seconds. I must say this is a bummer for anyone using InnoDb in a high-insert environment. I’ll have to see what the differences are on updates, selects, deletes, and joins.

In SQL (, , )

Night Harvast Wine, it blows.

Posted by chris on June 6th, 2010 Comments(0)

This blog started as a place for me to store technical information so I’ve decided to expand to storing information on good beer, wine, vodka, and food I might want to have again…and that I might want to avoid. Night Harvast falls in the avoid category. Yeah it sucks. My girl friend and I purchased this from the local liquor store to enjoy a low-key Friday evening. Sure its only a $10.00 bottle of wine, but this stuff was very flat and tasteless even for a cheap Merlot. There are definitely Merlots for slightly more and even slightly less that beat this stuff in a taste test. No good, wine fail!

In beer/wine/vodka/food (, )

MGMT at In the Venue

Posted by chris on June 6th, 2010 Comments(0)

I went and saw MGMT at In The Venue Friday night. Since this is in the newly formed beer/wine/vodka/food section of my blog I’ll keep the MGMT comments light, but for as much as I like this band they performed poorly. It may have been because this is a small venue and the sound system might not be too good, but this is also an awesome venue.

They have half the building open to the outside with a sky bar for those over 21. The bartenders made some incredible Long Islands and an awesome bartenders choice shooter for my girlfriend and I. The crowd was incredible despite the performance. The only downsides are its in an industrial part of town and the parking is shady, but I highly suggest seeing a show at In The Venue if the sky bar is open!

In beer/wine/vodka/food (, , )

Blue Plate Diner…thanks, but no thanks.

Posted by chris on June 6th, 2010 Comments(0)

Went here for the second time ever today. This is a popular spot for people living in Salt Lake City and its the closest diner relative to my flat. Here’s what I don’t recommend getting there. The Chicken Caesar salad was horrible. The croutons weren’t crunchy and the chicken tasted like it had been boiled then grilled. It was like flavorless. I also got an appetizer of hummus and its probably the worst hummus I’ve ever eaten. Hummus should not be salty, yuck! My girlfriend wasn’t very impressed with her salad either. I will probably not be coming back.

In beer/wine/vodka/food (, , )
« Older Entries