MyISAM vs InnoDB

I'm going to pit two of MySQL's most popular table engines against each other using the four S's as a guide: Speed, Storage, Stability, and Special features.

To test these catagories, I created two identical tables in the same database. One using MyISAM, one using InnoDB.

 CREATE TABLE `test`.`myisam` (
`field1` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`field2` VARCHAR( 32 ) NOT NULL ,
`field3` TEXT NOT NULL ,
PRIMARY KEY ( `field1` )
) ENGINE = MYISAM 

 CREATE TABLE `test`.`innodb` (
`field1` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`field2` VARCHAR( 32 ) NOT NULL ,
`field3` TEXT NOT NULL ,
PRIMARY KEY ( `field1` )
) ENGINE = INNODB 

1. Speed

   I devised a few tests to compare the relative speeds of the two tables. I repeated each query 3 times and took the average execution time for each table.

   Insert 1,000 rows in one query

   MyISAM: An incredible .0094 second average
   InnoDB: A respectable .0849 second average

   SELECT 10,000 Rows

   MyISAM: A fast .0121 second average
   InnoDB: A decent .0302 second average

   Advanced UPDATE Query

   /* 1,000 rows with md5 hashes (21 rows match) */
   UPDATE innodb SET field3 = 'changed'
     WHERE field3 LIKE '%a12%';
   UPDATE myisam SET field3 = 'changed'
     WHERE field3 LIKE '%a12%';
   

   MyISAM: A good .0112 second average
   InnoDB: A slightly worse .0282 second average

   Conclusion

   I left out some measures of speed, but these numbers are pretty clear. MyISAM was anywhere from 2 to 10 times faster than InnoDB when performing the same tasks. MyISAM wins this round.

2. Storage

   Here I'm comparing the disk space the two tables use when storing data.

   Empty Table

   MyISAM: 1kb of disk space
   InnoDB: 32kb of disk space

   1,000 Rows

   MyISAM: 166kb of disk space
   InnoDB: 256kb of disk space

   10,000 Rows

   MyISAM: 1,341kb of disk space
   InnoDB: 2,080kb of disk space

   Conclusion

   MyISAM is again the clear winner. MyISAM appears to use about 60% of the disk space that InnoDB uses to store the same amount of data.

3. Stability

   One measure of stability is the ability of the table to handle simultaneous connections to the database without failing. I haven't tested this myself, but the consensus seems to be that InnoDB is better at this.

   Conclusion

   I personally haven't had stability issues with either table engine, but then again, I haven't had to manage hundreds of simultaneous connections. I'll trust the people that have and declare InnoDB the winner for this round.

4. Special Features

   This is the category that matters most when choosing an engine type. Both MyISAM and InnoDB have very useful features the other one doesn't. I'll take a look at the main unique feature from each table type.

   Transaction Support

   InnoDB is transaction safe. This means that you can roll back changes you make to a database. This is primarily used for rolling back changes if an error occurs.

   For example, let's say you are transferring money between accounts and taking out a fee. You have three queries: take money out, take fee out, and put money in. If the last query fails, you can roll back the first two. This is a must-have for many applications.

   Full Text Support

   MyISAM supports full text indices and full text searches. This lets you search through text fields extremely quickly. Instead of using LIKE on large tables, which is very ineficient, you can use the MATCH AGAINST syntax for a fraction of the time and processing power. This is a must-have if your tables have description fields that are searchable or you store entire articles in a database.

   Using "article_text LIKE '%mysql%'" on 100 5-page articles would take forever. However using "MATCH(article_text) AGAINST('mysql')" would take no time at all. Another advantage is advanced relevancy scores for searches. If an article mentions mysql 3 times, it will be ranked higher. If you search for "the dog and the cat," only the words "dog" and "cat" will be taken into account when calculating relevancy.

   Conclusion

   Which feature is better depends entirely on what your application is.

And the winner is...

InnoDB. Although InnoDB is often slower and uses more space than MyISAM, I consider transactions as too important to give up. If you look at the times, we're talking a difference of at most .05 seconds. Also, most hosting companies today offer at least 100mb of space per database. 10,000 rows with 100 characters each took up 600kb extra using InnoDB. If you store images and files in the database, space might be an issue, but for most applications, it isn't.

There are certain types of applications that should use MyISAM, most notably Search Engines, but for most applications, InnoDB is the right choice.

Making a Captcha Verification Image

This is a short tutorial on adding a captcha verification image to a web form. PHP 4 or higher and the GD Image Library (comes bundled with newer PHP versions) are required.

Making a Captcha image is a lot simpler than you might think. It involves two main steps: generating a random string and making an image of that string.

Let's start with generating the string. This is the first part of captcha.php

<?php
session_start();

//$string will hold our generated random string
$string = "";

//List of characters that are unique (ie. no "1" and "I")
//This changes based on the font you use
$chars = "2345789ABcdEfGhJkmNpQrstVwxy";

//Choose 5 random characters from the list and 
//add to our string.
for($i=0;$i<5;$i++)
{
  $string .= $chars{rand(0,strlen($chars)-1)};
}

//Store our string in a session variable
$_SESSION['captcha'] = $string;

Now let's generate the image of this string, which is a little more complicated. This is part 2 of the captcha.php file.

//Set the output as a PNG image
header("Content-type: image/png");

//Tell browser to not store captcha in cache
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");

// create a new image canvas 160x60 px
$iwidth = 160;
$iheight = 60;
$image = imagecreate($iwidth, $iheight);

//Create colors (RGB values)
//Background color - light gray
$bg_color = imagecolorallocate($image, 230, 230, 230);
//Noise color for dots - green
$noise_color1 = imagecolorallocate($image, 40, 120, 80);
//Noise color for lines - light green
$noise_color2 = imagecolorallocate($image, 50, 180, 120);
//Text color - dark green
$text_color = imagecolorallocate($image, 20, 100, 40);

//Add 500 random dots to the image
//Lower this number if it's too hard to read
for( $i=0; $i<500; $i++ ) {
   //Create ellipses with 1px width and height
   imagefilledellipse($image, mt_rand(0,$iwidth), 
     mt_rand(0,$iheight), 1, 1, $noise_color1);
}

//Add 30 random lines to the image
//Lower this number if it's too hard to read
for( $i=0; $i<30; $i++ ) {
   //Make line with two random end points
   imageline($image, mt_rand(0,$iwidth), 
     mt_rand(0,$iheight), mt_rand(0,$iwidth), 
     mt_rand(0,$iheight), $noise_color2);
}

//Choose font file (download link after code block)
//I picked this font because it's easy to read
$font = "annifont.ttf";

//Choose font size
$fsize = 26;

//Set angle of font (just dealing with horizontal text for now)
$fangle = 0;

/*Useful function for getting dimensions of text
Returns:
array(
  0=>bottom left x position,
  1=>bottom left y position,
  2=>bottom right x position,
  3=>bottom right y position,
  4=>top right x position,
  5=>top right y position,
  6=>top left x position,
  7=>top left y position
)
*/
$dims = imagettfbbox ( $fsize, $fangle, $font, $string );

//Height is same as -1 times top_right_y 
$fheight = -1*$dims[5];
//Width is same as bottom_right_x
$fwidth = $dims[2];

//Get starting x,y position so text is centered
$fy = ($iheight-$fheight)/2+$fheight;
$fx = ($iwidth - $fwidth)/2;

//Now the magic function.  Adds the text to our image
//Using all the variables we created
imagettftext($image, $fsize, $fangle, $fx, $fy, 
  $text_color, $font , $string);

//generate a png image and output to screen
imagepng($image);

// destroy image resources
imagedestroy($image);
?>

Here a link to the font file: http://www.urbanfonts.com/fonts/Annifont.htm. Now you should be able to test your captcha image. Here's a demo of what it should look like: http://jeremydorn.com/demos/captcha.php

Now we add it to our form. This part is easy.

<img src='captcha.php' /><br />
Enter the text above: 
<input type='text' name='captcha' />

Finally, we check to see if they enter the right code. This next part goes in your validation script.

if(isset($_SESSION['captcha']) &&
$_REQUEST['captcha']==$_SESSION['captcha'])  {
  //correct code
}
else {
  //incorrect code
}

To see a sample form, go to http://jeremydorn.com/demos/captcha_form.php.

Mysql Transactions with PHP

One feature of Mysql that is often overlooked is transactions. Transactions allow you to preform multiple queries and rollback the changes if any one fails.

In mysql, only InnoDB tables have transaction support. MyISAM, which is often the default table type, does not support this.

Let's say you are transferring funds between 2 accounts. You withdraw the money in one query and deposit the money in another one. What happens if the deposit query fails? The money just disappears. Transactions are an easy way to solve this problem.

We must add some helper functions to our mysql config file. First we'll make a wrapper function for mysql_query(). It takes a query and an optional error message.

function query_db($query,$error="Error")
{
  //perform query
  $result = mysql_query($query);
  if(!$result) //query fails
  {
    //exit with error message
    exit($error."<br>$query<br> ".mysql_error());
  }
  else //query succeeds
    return $result;
}

Next, are three transaction functions, begin_transaction(), commit_transaction(), and rollback_transaction().

//Begins the transaction
//Every query after this can be rolled back
function begin_transaction()
{
  query_db("SET AUTOCOMMIT=0");
  query_db("BEGIN");

  //Set the $begin_transaction variable to true 
  //so that other functions know a transaction started
  global $begin_transaction;
  $begin_transaction = true;
}

//Commits all queries.  Changes cannot be undone after this
function commit_transaction()
{
  //if transaction is started, commit changes
  global $begin_transaction;
  if($begin_transaction)
    query_db("COMMIT");

  //update global variable
  $begin_transaction = false;
}

//Rolls back all queries. All changes are canceled.
function rollback_transaction()
{
  //if transaction is started, roll back changes
  global $begin_transaction;
  if($begin_transaction)
    query_db("ROLLBACK");

  //update global variable
  $begin_transaction = false;
}

We need to alter the query_db() function to rollback changes if an error occurs.

function query_db($query,$error="Error")
{
  //get global variable
  global $begin_transaction;

  //perform query
  $result = mysql_query($query);
  if(!$result) //query fails 
  {
    //if transaction is started, rollback changes
    if($begin_transaction)
      rollback_transaction();

    //exit with error message
    exit($error."<br>$query<br> ".mysql_error());
  }
  else //query succeeds
    return $result;
}

That's all you need to use transactions. Here's an example of how to use this.

<?php
begin_transaction();
  query_db("UPDATE accounts 
    SET balance = balance-500
    WHERE id=1"
  );
  query_db("UPDATE accounts 
    SET balance = balance+500    
    WHERE id=2"
  );
commit_transaction();
?>

If either query fails, both accounts are unaffected. If both queries succeed, the accounts are updated.

Sometimes, you want to make sure a row is affected, not just that the query succeeded. I didn't build this into the functions, but it's easy to implement. Here's a simple way to do it:

<?php
begin_transaction();
  query_db("UPDATE accounts 
    SET balance = balance-500
    WHERE id=1"
  );
  if(!mysql_affected_rows()) //no rows affected
    exit("error".rollback_transaction());

  query_db("UPDATE accounts 
    SET balance = balance+500    
    WHERE id=2"
  );
  if(!mysql_affected_rows()) //no rows affected
    exit("error".rollback_transaction());

commit_transaction();
?>

Transactions also help with testing an application. You can perform 20 queries, do some tests, and then roll back the changes automatically. Think of how much time this could save.

AJAX Form Validation

Many sites today validate form inputs as the user types. These validations are usually simple, like checking if a username is available, but contribute to a great user interface. I'm going to walk through how to do this with PHP and AJAX, and the advantages and disadvantages.

I'm going to make a simple form with two inputs, a username and a password. I'm then going to validate the inputs as the user types using 2 different methods.

Let's start with the form.

<html>
<head>
  <title>Form</title>
</head>
<body>
<form>
  Username:
  <input type='text' id='username' />
  <br />Password:
  <input type='password' id='password' />
</form>
</body>
</html>

The first field we'll validate is the password field. We'll create a validatePassword() javascript function that makes sure the password is at least 6 characters long.

<script type='text/javascript'>
function validatePassword() {
  var field = document.getElementById('password');

  var test = true;

  //If password is less than 6 characters
  if(field.value.length < test =" false;" backgroundcolor =" 'green';" backgroundcolor =" 'red';">

Now we need to add a keyup event to our password input.

<input type='password' id='password'
onKeyUp='validatePassword();' />

Now we are going to validate the username by making sure it's available. We can't do this just using javascript since we have to check a database, so we use AJAX and PHP instead. Here's the php page (validateUsername.php).

<?php
//include mysql config file
include 'mysql.php';

//get passed username
$username = $_REQUEST['username'];

//sanitize passed username
$username = mysql_real_escape_string($username);

//make sure username is not empty
if(empty($username))
  exit("false");

//query the database for matches
$query = "SELECT id FROM users WHERE username='$username'";
$return = mysql_query($query) or
  die ("Error getting matches: " . mysql_error());

//If a match exists, return false, else return true
if(mysql_num_rows($return)>0)
  echo 'false';
else
  echo 'true';
?>

If the username 'jsmith' is already taken, validateUsername.php?username=jsmith would output 'false'. Otherwise it would output 'true'. Now we need ajax to interact with this page. I'm going to use Prototype for the ajax to make the code easier to understand.

<script type='text/javascript' src='prototype-1.6.0.2.js'></script>
<script type='text/javascript'>
function validateUsername()
{
  //get username input
  var field = document.getElementById('username');

  //Don't use ajax if username is empty
  if(field.value.length == 0)
  {
    field.style.backgroundColor = 'red';
    return;
  }

  //Initialize new ajax request
  new Ajax.Request('validateUsername.php', {
    method: 'get',
    parameters: {
      username: field.value
    },
    //If a response is sent back
    onSuccess: function(transport){
      //get response text
      var response = transport.responseText;

      if(response=='true') //validation passed
        field.style.backgroundColor = 'green';
      else //validation failed
        field.style.backgroundColor = 'red';
    }
  });
}
</script>

Once again we need to add a keyup event to our input.

<input type='text' id='username'
onKeyUp='validateUsername();' />

Now we have all out parts. Before I put it all together and show you a demo, understand that validating a form with Ajax should be the last resort. The only time you should use Ajax is if you must interact with a database. Anything else is unnecessary load on your servers. I do feel, however, that using Ajax where needed is well worth the server load.

Here is a link to a demo. Below is the complete source of the html page. The php source is above.

<html>
<head>
  <title>Form</title>

  <script type='text/javascript'>
  function validatePassword() {
    var field = document.getElementById('password');

    var test = true;

    //If password is less than 6 characters
    if(field.value.length < 6)
   test = false;

    if(test) //validation passed
    {
     //Change input background green
     field.style.backgroundColor = 'green';
    }
    else  //Validation failed
    {
   //Change input background red
   field.style.backgroundColor = 'red';
    }
  }
  </script>
  <script type='text/javascript' src='prototype-1.6.0.2.js'></script>
  <script type='text/javascript'>
  function validateUsername()
  {
    //get username input
    var field = document.getElementById('username');

    //Don't use ajax if username is empty
    if(field.value.length == 0)
    {
      field.style.backgroundColor = 'red';
      return;
    }

    //Initialize new ajax request
    new Ajax.Request('validateUsername.php', {
      method: 'get',
      parameters: {
        username: field.value
      },
      //If a response is sent back
      onSuccess: function(transport){
        //get response text
        var response = transport.responseText;
 
        if(response=='true') //validation passed
          field.style.backgroundColor = 'green';
        else //validation failed
          field.style.backgroundColor = 'red';
      }
    });
  }
  </script>
</head>
<body>
<form>
  Username:
  <input type='text' id='username'
onKeyUp='validateUsername();' />
  <br />Password:
  <input type='password' id='password'
onKeyUp='validatePassword();' />
</form>
</body>
</html>

Converting HTML to XHTML

Here are some tips on making an html page xhtml compliant.

Include a proper doctype

A doctype is the first line of code in a web page that tells the browser the file type. Here are two xhtml doctypes, strict and transitional:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Close all tags

With HTML, you can get away with tags like <img ...> and <br>. With XHTML, you must close these tags as follows: <img ... /> and <br />. In general, make sure every start tag has an end tag.

Remove deprecated tags

Many tags can be replaced with css, such as <center> and <font color=red>. Use <div style='text-align:center'> or <span style='color:red;'> instead.

Remove deprecated attributes

As with tags, many attributes can be replaced with css.
Replace <img width=500 height=300 border=0 /> with <img style='width:500px; height:300px; border:0px;' />

Include required attributes

XHTML is very strict when it comes to attributes. Some are forbidden (width, height) and some are required (alt). The required attribute that most comes to mind is the alt tag for images. If you don't have one, your site won't validate.

Use tables correctly

Tables have one purpose and one purpose only, to display tabular data. You wouldn't use Excel to layout a webpage, why use tables?
This is the hardest part to change if you're currently using a table for your site layout, seeing how at least half of your code is <tr> and <td> tags. It's often best to start from scratch with <div> tags.

These were off the top of my head, so I'm sure I missed a bunch of things. W3's site has a good XHTML validator that provides useful feedback. Give it a try at http://validator.w3.org/

Basic user accounts with PHP and MySQL

This is a tutorial on adding basic user accounts to your site.

Objective:

1. Create a registration and sign in page.
2. Require users to log in to view designated pages.

Target Audience:

1. Someone with basic MySQL and PHP knowledge.
2. Someone who wants user accounts without using a framework like Joomla or CakePHP.

First, let's create the 'users' mysql table.

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL,
`password_hash` varchar(40) NOT NULL,
`password_salt` varchar(8) NOT NULL,
`created` datetime default NULL,
PRIMARY KEY  (`id`)
);

To add some security to the site, I'm encrypting the passwords (hence the password_salt and password_hash fields). This stops anyone from knowing a password by looking in the database.

Next, we'll make the sign up form (signup.php). This is where users will register for your site.

<html>
<head>
<title>Sign Up</title>
</head>
<?php
if(isset($_REQUEST['username'])) //Form submitted
{
 //Connect to database
 $conn = mysql_connect('localhost', 'root', 'password') 
  or die('Could not connect: ' . mysql_error());
 mysql_select_db('database_name');

 //Sanitize entered info
 $username = mysql_real_escape_string($_REQUEST['username']);
 $password = mysql_real_escape_string($_REQUEST['password']);
 
 //Validate entered info
 $test = true;
 if(empty($username) || empty($password))
  $test = false;

 if($test) //Validation passed
 {
  //Generate random 8 character password salt
  $password_salt = "";

  //characters to choose from
  $chars = "0123456789abcdefghijklmnopqrstuvwxyz-_%#"; 
  for($C=0;$C<8;$C++)
  {
   $password_salt .= $chars{rand(0,strlen($chars)-1)};
  }
  
  //Generate hash based on entered password and salt
  $password_hash = md5($password_salt.$password);

  //Insert user in database
  $query = "INSERT INTO users 
    (username,password_salt,password_hash,created) VALUES 
    ('$username','$password_salt','$password_hash',NOW())";
  mysql_query($query) or 
    die ("Error creating new user: " . mysql_error());

  //Display success message and exit
  exit(
    "Account created successfully.  You may now 
    <a href='signin.php'>Sign In</a>"
  );
 }
 else //Validation failed
  echo "Please enter all information";
?>
<h1>Sign Up</h1>
<form action="signup.php" method="post">
Username: 
<input type="text" name="username" /><br />
Password: 
<input type="password" name="password" /><br />
<input type="submit" value="Sign Up" />
</form>
</body>
</html>

Make sure to change the mysql connection settings above to match your configuration.

Lastly, we'll make the sign in page (signin.php). If a user tries to access a restricted page, they will be redirected here.

<?php
session_start(); //Start session so we can sign user in
?>
<html>
<head>
<title>Sign In</title>
</head>
<?php
if(isset($_REQUEST['username'])) //Form submitted
{
 //Connect to database
 $conn = mysql_connect('localhost', 'root', 'password') 
  or die('Could not connect: ' . mysql_error());
 mysql_select_db('database_name');

 //Sanitize entered info
 $username = mysql_real_escape_string($_REQUEST['username']);
 $password = mysql_real_escape_string($_REQUEST['password']);
 
 //Select users with enetered username from database
 $query = "
    SELECT id,username,password_salt,password_hash 
    FROM users 
    WHERE username='$username'";
 $result = mysql_query($query) 
    or die("Error looking up user: " . mysql_error());
 
 
 if($row=mysql_fetch_assoc($result))//Row is returned
 {
  //Generate hash based on entered password and stored salt
  $password_hash = md5($row['password_salt'].$password);
  
  //If User entered correct password
  if($password_hash == $row['password_hash'])
  {
   //Sign them in by storing their id in a session variable
   $_SESSION['userid']=$row['id'];
   
   //Show message and exit
   exit( "You are successfully signed in." );
  }
  else //Incorrect password
  {
   echo "Incorrect Password";
  }
 }
 else //Incorrect Username
 {
  echo "Incorrect Username";
 }
}
//Show sign in form
?>
<h1>Sign In</h1>
<form action="signin.php" method="post">
Username: 
<input type="text" name="username" /><br />
Password: 
<input type="password" name="password" /><br />
<input type="submit" value="Sign In" />
</form>
</body>
</html>

Again, make sure you change the mysql settings.

Now that people can sign up and sign in, you need a way to require this. Put this at the top of a page to require the user to sign in:

<?php
session_start();//Start the session
if(!isset($_SESSION['userid']))//User not signed in
{
 header("Location: signin.php");//Redirect to sign in page
 exit();//Stop script from executing
}
?>

Here are a few things to keep in mind:

• "session_start();" must be the first line in a file. If there are any characters or whitespace before the opening php tag, it will not work.
• This post is meant to explain how something works, not the best way to implement it. There are many improvements to be made and I may address them in the future.

Welcome

Welcome to Jeremy Dorn Web Design

I'm a web developer and student at Arizona State University.

When I have a web design question, I often find the answer on various blogs. This is my attempt to give back.

I plan to include useful code snippets, commentary on different web technologies, and tutorials on various subjects.

Check back soon.