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

My Blog

Here is my awesome blog. You can find information on programming, linux, documentation, tips for code and database optimization, my thoughts and rants, and whatever else I feel like sharing. Feel free to contribute to the blog by posting comments and asking questions.

Programming

« Older Entries
Newer Entries »

MySQL Temporary Tables Example – Optimizing PHP Applications

Posted by chris on November 24th, 2010 Comments (7)

Shortly after starting a new job as Web Application Developer with an e-commerce company I was tasked with rewriting a legacy application. After analyzing and flow charting the current application I found numerous performance penalties, bloated code, linear programming (non-OOP), and many other areas for optimization. Even after refactoring the code and removing these performance barriers the application was a bit sluggish. Though I had improved overall application execution time by 90% I still knew there was more I could do. This is where temporary tables came into play.

MySQL Temporary Tables have the same functionality as standard disk-based tables except they exist in memory. Since memory is not long term storage, they are temporary tables, hence the name. Operating in memory makes working with these tables fast, your only limit is the amount of memory/swap space available to you.

Read the rest of this entry »

In Programming, SQL (, , , , , )

MooTools Table Sorter 0.9.6 released

Posted by chris on November 24th, 2010 Comments (3)

MooTools Table Sorter version 0.9.6 released on November 15, 2010. This post is just for reporting bugs and feature requests for the new version. For documentation please refer to the version 0.9.5 post. This documentation is still valid. 0.9.6 just fixes bugs in Internet Explorer 6 and Internet Explorer 7 as well as validating the code works in IE9.

In JavaScript and Ajax, Programming (, )

Setting up SubVersion (SVN) on Local Ubuntu with Netbeans

Posted by chris on October 5th, 2010 Comments (4)

Assuming you have SVN installed follow these steps from the shell (where “proj” is your project). You can put this anywhere you want really, this is how I decided to do it though to keep it out of my default apache directory. I’ll show steps for configuring this to work with apache if you’re doing this as a PHP repo on your localhost. You may need to sudo yourself for these commands. Also, I’m an SVN novice so follow at your own risk. I’m pretty sure I did this poorly…

Read the rest of this entry »

In Linux, Programming, Software (, , )

Creating a Spider with Python Mechanize to Monitor Your Website

Posted by chris on September 15th, 2010 Comments (2)

Program a Python Web Spider Bot with MechanizeEvery since the SeoMoz conference in Seattle I’ve been wanting to write a spider to do some of the interesting things the speakers mentioned. Well I haven’t gotten an opportunity to write any SEO apps yet, but I did get a chance to take my first crack at Python using the Mechanize spider. Mechanize is fast and light-weight parsing large HTML documents in fractions of a second. There were PHP options I could have used. One was phpdig. I could have also written a spider from scratch. But after 4 years of developing in PHP I wanted to try something new. Enter python + mechanize for monitoring websites.

The opportunity came after some unfortunate circumstances lead to downtime at work related to a data center migration. Needing a way to go beyond simple service monitoring for things like HTTP/Apache and MySQL I built upon mechanize to test for specific HTML tags within the document body.

Read the rest of this entry »

In Programming (, , , )

What is PDO Error HY093: Debug/Fix MySql PHP PDO Error HY093

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

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.

Drop me a comment if this helped your or if you feel I missed something, thanks for reading.

In Programming, SQL (, , , )

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 Programming, Software (, , , )

MooTools Table Sorter 0.9.5

Posted by chris on May 31st, 2010 Comments (16)

Well its been a long weekend and I’m starting to wrap it up. But I couldn’t go to bed without redoing the documentation for my baby the MooTools TableSorter and updating the demo page. The documentation will loosely follow what you see in the demo page.

MooTools Table Sorter

HTML Setup

You’ll need to include 3 files: MooTools 1.2 or higher, the CSS file, and the Table Sorter javascript class. You’ll also want to make sure all the images are there and update the CSS image paths to jive with the location on your web server.

1
2
3

<link rel="stylesheet" media="screen" href="/mootools/table-sorter/table-sorter.css" />
<script type="text/javascript" src="/js/mootools1-2.js"></script>
<script type="text/javascript" src="/mootools/table-sorter/table-sorter.js"></script>

PHP Setup

You’ll want to use the TableSorter php class I included to make things easy one you.

Notice setting the table headers is important. For instance when a user clicks on city the TableSorter will send the ORDER BY stored in the TH Title. This is how your server side code will know what to order by. Reply to this post if you have any questions on the server-side PHP code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

include_once 'TableSorter.class.php';
function returnGeoHtmlTableStr($whereClause='',$startingFromRecord=0,$rowsPerPage=100,$orderBy='city ASC')
{
	mysql_connect('localhost','user','password','database');
	mysql_select_db('database');
 
	$startingFromRecord = (int) $startingFromRecord;
	$rowsPerPage = (int) $rowsPerPage;
 
	$sql = "SELECT 
				count(*) as count 
			FROM 
				cnizz_geo 
 
			$whereClause
 
			";
	$result = mysql_query($sql);
	$countArr = mysql_fetch_assoc($result);
	$totalRows = $countArr['count'];
 
	if($orderBy==''){
		$orderBy='city ASC';
	}
 
	$sql = "SELECT SQL_CALC_FOUND_ROWS
				* 
			FROM 
				cnizz_geo 
 
			$whereClause
 
			ORDER BY 
				$orderBy
			LIMIT 
				$startingFromRecord,$rowsPerPage
			";
	$result = mysql_query($sql);
	$arr = array();
	while($row=mysql_fetch_assoc($result)){
		$arr[]=$row;
	}
 
	$sql = "SELECT FOUND_ROWS() as totalRows";
	$result = mysql_query($sql);
	$foundArr=mysql_fetch_assoc($result);
 
	$str = '
		<table class="DefaultTable" style="width:500px;">
		'.TableSorter::returnMetaStr($startingFromRecord,$rowsPerPage,$foundArr['totalRows'],count($arr),$orderBy).'
		<tr style="background:#FFF;"><td colspan="10" style="height:5px;">&nbsp;</td></tr>
		<tr id="GeoHead" class="DefaultTableHeader">
			<th id="City" title="'.(($orderBy=='city ASC')?'city DESC':'city ASC').'">City</th>
			<th id="State" title="'.(($orderBy=='state ASC')?'state DESC':'state ASC').'">State</th>
			<th id="Abbrev" title="'.(($orderBy=='state_abbrev ASC')?'state_abbrev DESC':'state_abbrev ASC').'">State</th>
			<th id="County" title="'.(($orderBy=='county ASC')?'county DESC':'county ASC').'">County</th>
		</tr>
		';
	$x=1;
 
	foreach($arr as $i)
	{
		if($x%2){
			$class='';
		}
		else{
			$class='zebra';
		}
		$str.= '<tr class="'.$class.'">
							<td><a id="'.$i['city'].'" title="'.stripslashes($i['city_id']).'">'.stripslashes($i['city']).'</a></td>
							<td>'.$i['state'].'</td>
							<td>'.$i['state_abbrev'].'</td>
							<td>'.$i['county'].'</td>
						</tr>';
		$x++;
	}
 
	$str.= '<tr style="background:#FFF;"><td colspan="10" style="height:5px;">&nbsp;</td></tr>';
	$str.=$meta.'</table>';
	return $str;
}

Your Server Side Controller

1
2
3

if($_GET['action']=='returnGeoHtmlTableStr'){
	die(returnGeoHtmlTableStr('',$_GET['start'],$_GET['rows'],$_GET['orderBy']));
}

Now the JavaScript

1
2
3
4
5
6
7
8
9
10
11
12
13
14

window.addEvent('domready',function(){
	sorter = new TableSorter({
		request: 'action', 
		action: 'returnGeoHtmlTableStr', 
		destination: 'XhrDump', 
		prev: 'PagePrev', 
		next: 'PageNext', 
		head: 'GeoHead',
		rows: 100,
		defaultStartEndWaitEnabled: 1,
		startWait: '',
		endWait: ''
	});
})

Lets explain this line by line.

  1. request: ‘action’, This is how you name the GET parameter. So in this instance the GET looks like $_GET['action']
  2. action: ‘returnGeoHtmlTableStr’, This is the value of GET parameter defined in step 1, so $_GET['action'] = ‘returnGeoHtmlTableStr’
  3. destination: ‘XhrDump’, This is the element id where TableSorter will write results too
  4. prev: ‘PagePrev’, This is the element that the class will listen on for Previous Page requests.
  5. next: ‘PageNext’, Same as above except it goes to the next page when this element is clicked
  6. head: ‘GeoHead’, This tells the class where to look for all those TH tags, which it uses to do sorts on if a Title is defined on the TH tag.
  7. rows: 100, How many rows should the request return.
  8. page: location.href, Where to send the GET request
  9. method: ‘get’, The type of method (GET or POST). By default the method is get, but you can change it to post.
  10. defaultStartEndWaitEnabled: ’1′, this is set to 1 by default. Setting this to 0 disables the built-in visuals (fades, image etc.) you see when performing a sort.
  11. startWait: ”, empty by default, will do a callback function only if defaultStartEndWaitEnabled is set to 0. This is for doing your own custom visuals.
  12. endWait: ”, empty by default, will do a callback function only if defaultStartEndWaitEnabled is set to 0.

Other cool stuff you can do

You can add additional parameters to the GET request in the form of parameter name => parameter value.

1

sorter.addParameter('zipcode','84101');

You can remove these user-defined parameters by calling removeAllParameters.

1

sorter.removeAllParameters();

You can remove a specific parameter by calling removeParameter.

1

sorter.removeParameter('zipcode');

You can initiate a sort anytime you want by calling sort.

1

sorter.sort();

There are some known issues in IE where the up/down arrows will not appear on sorts. Also the visual-effects are a bit degraded in IE as well. When posting questions or bugs you can use the HTML pre tag and set language=”javascript” to get syntax highlighting (wp-syntax plugin). Just makes things easier for me and other readers. Take a look at the source on my syntax highlighting and you’ll see what I mean.

Please reply to this blog post with any comments, bugs, or questions. Enjoy.

In JavaScript and Ajax, Programming (, )

Optimize MySQL Queries – Fast Inserts With Multiple Rows

Posted by chris on May 31st, 2010 Comment(1)

I was programming some code that needed to do a lot of inserts a few months back. I hypothesized that creating a big SQL insert statement would be faster than executing a bunch of small insert statements. I didn’t have time to create a test, but I wanted to go back and test this theory at some point. The idea was that even though there is a penalty for looping through an array and accessing a variable to build the SQL statement that it would still be faster than asking PHP to send a bunch of tiny querries.

My test server is my local machine running Ubuntu 10.04, dual 1.80Ghz core Intel processors with 2 GB of memory. Its running the latest stable release of PHP5, Apache2, and MySql5. I used the standard php mysql_connect function, not mysqli. The database engine used in this test was MyIsam. I performed 1000 inserts in the first test, I then altered the code to build one giant insert. I restarted the apache and mysql server after the first scenario.

Read the rest of this entry »

In Programming, SQL (, , , )

Type Casting In PHP To Prevent XSS and SQL Injection

Posted by chris on March 14th, 2010 Comment(1)

Lots of developers think the best way to prevent XSS and SQL injection attacks are by passing all user input through a filter function. If you’re one of these developers don’t worry, you’re still right. There is a better (less code and less CPU cycles) way to do this on certain user inputs though. Type casting to integers should be used on all user inputs that should be a numeric value. This ensures that a valid data type is being used and it automatically converts any strings to an integer. This effectively prevents any SQL injection or XSS attacks.

1

$customer_id = (int) $_POST['customer_id'];

This automatically prevents someone from being able to pass in something like:

1

1' OR 1='1

or

1

window.location 'aol.evilcloneofaol.com'

It should be noted that when casting a variable to an integer there are limitations to just how big that integer can be. On 32-bit systems the limit is 2,147,483,647 and on 64-bit systems the limit is 9,223,372,036,854,775,807. I’ve hit the limit on 32-bit systems, but never on a 64-bit. When the variable you are casting to an int is too large PHP will always just return the highest number it can. Leading to lots of confusion when trying to debug this error.

PHP Reference on Integers

In Programming (, , , , )

MySQL engines, InnoDb versus MyISAM for web developers

Posted by chris on February 23rd, 2010 Comments (5)

Let me start out by saying I think MyISAM sucks. I hate it. It’s the default MySQL database engine, but its non-relational so a lot of people just start using it without exploring the other options. Hey thats okay, I did the same thing until some smart guys over at my last job introduced be to InnoDb. MyISAM is probably the best way to go for newer web developers just trying to cut their teeth on web application development. At some point its time to a pick up a book and learn how InnoDb can save you time, save you headaches, reduce the amount of code you write, and make the world a better place (okay that last one is a reach).

The best part about InnoDB is that its relational. Its transaction-safe too, but I’ll just focus on the relation side of things for now. What is a relation? A relation joins two tables together on a common value. Typically this is a parent-child relationship known as a one-to-many, but it can be a one-to-one relation too. Lets look at three tables.

tbl_profile
—————
profile_id
profile_name

tbl_profile_setting
———————–
profile_setting_id
profile_setting_name

tbl_profile_has_setting
—————————
profile_id
profile_setting_id

We have a profile table for storing whatever, then a profile can have settings. It doesn’t really matter what these settings are for the purpose of this article, but a profile can have multiple of these settings. You could have this same structure in MyISAM, but you would have to store the relations in your code. Your code is prone to errors. It happens, in fact is happens enough that I try to write as little code as possible. My goal is to leverage as much pre-written code as possible, because its been reviewed by more people and if I’m using that code I likely trust the source. InnoDB is an awesome example of this. Its widely deployed and written by people with more skills than me. No inferiority-complex here, thanks InnoDB.

For creating a relation. We’ll use phpMyAdmin. MySQL Administrator works great too and if your nutty enough you can look up the SQL for doing it in the mysql command line console. Go into the tbl_profile_has_setting table. In the structure tab you will see a link called Relation View. Click on this. You’ll notice a drop down next profile_id and profile_setting_id (these will only appear if you made these primary keys). You’ll need to create indexes on these two columns in the tbl_profile_has_setting table as well. Select the tbl_profile.profile_id and tbl_profile_setting.profile_setting_id for their respective columns. For the On Delete drop down select cascade.

What you’ve just done is create relations that have the following rules enforced by the database engine.

  1. When you delete a profile, its corresponding record(s) in tbl_profile_has_settings is deleted automajically
  2. When you delete a profile setting, its corresponding record(s) in tbl_profile_has_settings is deleted automajically
  3. When you add a record to tbl_profile_has_setting the profile_id and profile_setting_id must exist in their respective tables

Guess what, you don’t have to verify that the setting exists anymore when inserting into tbl_profile_has_setting and you don’t even need to worry about the profile existing. MySQL will return an error if these rules are violated. You now have referential integrity, clean data, and happy reports. You made all this possible just by creating the relation. So what did the cascade option do? That created rule 1 and 2 above. The auto-delete. Cascade should be used wisely as it can have devastating consequences (you records are automatically deleted), but when you implement a cascade this is normally what you want.

So why doesn’t everyone use InnoDb over MyISAM. There are several reasons:

  1. You need to be more knowledgeable to use it. This isn’t just throw data in grab it out anymore. It takes more thought and for bigger projects you’ll want to create ER diagrams to flowchart out your database.
  2. Performance penalty. Since you’ve offloaded the work to the database engine your database now runs slower. I scoff when people use this as an argument against InnoDb. If your application has gotten to be so successful that InnoDb is the sole reason of your slow down then congratulations, not many people are as successful as you. Plus InnoDb operates in a lower level language that is faster than the PHP code you are writing. Also most of your slowdowns in PHP web applications can be attributed to poorly written queries, bad database design to begin with, and lack of innovation to come up with solutions to improve speed.
  3. Harded to backup. Yes you can still use the mysqldump to backup your data, but you can’t copy the actaul database file like you could with MyIsam. This is a crappy form of database backup anyways. If you’re big enough to wear the mysqldump is no longer a sane method of doing backups then just stop being cheap and go buy the enterprise software to manage your data. Your data is important to you right?

Hope this helps some people and I hope it offends some people as well. This is one of those things that I cannot find common ground on, its debated often between me and co-workers. On a side note, don’t let your domain expire while on vacation. You’ll lose your SERPs fast.

In Programming, SQL (, , )
« Older Entries
Newer Entries »