Create The Internet… It's What I Do EVERY Day – Joseph Yancey

MySQL Joins

I hated joins as a young developer.  I just want the world to know that.  Once I found out exactly how they worked, I didn't hate them at all.  Joins can be your friend.

Joins simply relate rows in one table with rows in another table.

Take these tables for example:

Books
+---------+-----------+
| book_id | author    |
+---------+-----------+
|   1     |     joseph|
|   2     |     john  |
|   3     |      sue  |
|   4     |     NULL  |
|   5     |     john  |
|   6     |     sam   |
+---------+-----------+

Authors
+---------+-----------+
| author  | sex       |
+---------+-----------+
|   joseph|     male  |
|   john  |     male  |
|   sue   |     female|
|   lucy  |     female|
|   sam   |     NULL  | <- lets just pretend we don't know
+---------+-----------+

In MySQL, there are three important types of joins.  There are:

1. Inner Joins
2. Left Joins
3. Right Joins

Inner Joins

Inner joins combine rows that have matches in both tables.  There will be no nulls left in the resulting table at all.  This query would be written like:

• select * from Books, Authors where Books.author = Authors.author
• or
• select * from Books inner join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    3    |   sue   |     female|
|    5    |   john  |     male  |
+---------+---------+-----------+

Left Joins

Left joins take all results from the left table and combine the matching results from the right table. This query would be written like:

• select * from Books left join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    3    |   sue   |     female|
|    4    |   NULL  |     NULL  | <- because there isn't an author to match
|    5    |   john  |     male  |
|    6    |   sam   |     NULL  | <- because we don't know sam's sex
+---------+---------+-----------+

Right Joins

Right joins are exactly like left joins but they will give all results from the right table while matching the left table to it. This query would be written like:

• select * from Books right join Authors on (Books.author = Authors.author)

You will get back:

+---------+---------+-----------+
| book_id | author  | sex       |
+---------+---------+-----------+
|    1    |   joseph|     male  |
|    2    |   john  |     male  |
|    5    |   john  |     male  | <- john is here twice because there are 2 matches in the left table
|    3    |   sue   |     female|
|    NULL |   lucy  |     female| <- because this is a right join and lucy is in the right table
|    6    |   sam   |     NULL  |
+---------+---------+-----------+

Find files in PHP

There is a super easy way to get all files matching some particular pattern in PHP. In comes "glob". It sounds like some slimey green goop but really it is quite handy. Glob will return an array of files that match a pattern. Handy huh?

jQuery – Mouseover Fade

This one is really simple. You just make sure that you have jquery included on the page. Then you create a div like this:

That code should be very easy to understand.

My life Changed

On November 20th 2009 at 4:50pm my life changed. My son was born. At 7 pounds 6.5 ounces and 21 inches long he is a long skinny little guy. He has a head full of hair and looks like his daddy. I always wanted a family of my own and now I have it. My wife, my son, and I now make up my happy little family. He is quite perfect. I have pictures posted here.

Bit.ly API function

Everyone knows about the bit.ly url shortening service. I have created a php function to automatically shorten a url passed to it. First, you need to go to bit.ly and signup for an account.  You will need your api key and your username.  With my function, you simply insert your api key and username and it will return a shortened url to you.

If you notice, this function will retry 5 times before it gives up.  That is because bitly will only allow 3 concurrent requests per IP.  My function also will only make the request to bit.ly once per page per session.

reCAPTCHA

I'm sure that you have seen a captcha somewhere on the web.  A captcha is an image that you have to type the contents of to continue on in a form.  We use them at work from time to time.  I've been interested in but never used the reCAPTCHA system.  Recently purchased by google, I get the feeling that they will see alot more use in the coming months.

reCAPTCHA is a system that solves two problems in one.  Optical Character Recognition has always had the issue of hard to read words.  Computers simply can not read quite like humans.  This is precisely why captchas work so well.  To defeat a captcha and spread spam, a bot would have to be great at OCR.  reCAPTCHA takes scans of old books and presents them to users.  One of the words presented is known and the other is unknown.  When a certain number of people answer the same on the unknown word, it is considered known.  Using the reCAPTCHA system, we are digitising books one word at a time.

Bank Routing Number (ABA) Algorithm

At work, I have recently had to work on some validation for bank routing numbers.  Through much research, I have discovered the algorithm. 

The number that I am talking about here is the ABA Routing Number.

We'll start with a routing number like 231381116. Here's how the algorithm works. First, strip out any non-numeric characters (like dashes or spaces) and makes sure the resulting string's length is nine digits,

2 3 1 3 8 1 1 1 6

Then we multiply the first digit by 3, the second by 7, the third by 1, the fourth by 3, the fifth by 7, the sixth by 1, etc., and add them all up.

(2 x 3) + (3 x 7) + (1 x 1) +
(3x 3) + (8 x 7) + (1 x 1) +
(1 x 3) + (1 x 7) + (6 x 1) = 110

If the resulting number is an integer multiple of 10, then the number is valid. To calculate what the checksum digit should be, follow the above algorithm for the first 8 digits. In the case above, you would come up with 104. Thus, to make the total number an integer multiple of 10, the final check digit must be 6.

Enough with the details, on with the algorithm!

PHP5

Gmail HTML Signature

Those of us that use Gmail know that adding an HTML signature can be a pain in the behind.  Typically, Gmail only allows standard plain text signatures.  I have discovered a  great Firefox plugin named Blank Canvas Gmail Signatures.  This plugin allows you to easily add signatures to each sending address within your account.

Simply compose a message and click "Create Signature".  Once you have done that for the sending account that you want, your screen will look something like the image on the right.  From now on out, any email that you send from Gmail on that computer will have whatever HTML signature you want.

First Post!

Just switched to WordPress...  Still in twitter mode.  Posts shall be short!  By the way,  WordPress is pretty cool.  The backend could be labled a little better.  Has anyone ever heard of using the title tag on a link to explain what the link is?  Guess not...

I'm out...