Archive by Author

Sorting records by distance using PHP & MySQL

by on July 8, 2009 in development

I recently got to the point in my project where I was ready to implement sorting for the search results.  I wanted to sort by the distance between two zip codes. However, I quickly realized I had a major problem on my hands. I had a (potentially) large number of search results that I needed to sort. Too many to sort using PHP; I needed a way to sort them in MySQL. But the method I’d planned to use to calculate the distance between zip codes, Micah Carrick’s distance calculation class, was implemented entirely in PHP. I needed some way for the distance to end up in the SQL query so that I could sort on it.

After searching around for a bit I came across this nice bit of code which creates a MySQL query using PHP variables to calculate the distance. By combining the two methods I was able to create a very workable solution for sorting by distance. Here’s what I did:

First, download the zipcode SQL from Micah’s page and run each SQL file. That should give you a new shiny table called zip_code filled to the brim with (what else?) zip codes.

Second, you’ll need the get_zip_point function from Micah’s code:

/**
 * This function pulls just the latitude and longitude from the
 * database for a given zip code.
 */
function get_zip_point($zip)
{
	$sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
	$r = mysql_query($sql);
	if (!$r)
	{
		$this->last_error = mysql_error();
		return FALSE;
	}
	else
	{
		$row = mysql_fetch_array($r);
		mysql_free_result($r);
		return $row;
	}
}

Third, you’ll want a function to generate the SQL you’ll use in your query:

/**
 * Returns the SQL to calculate distance based on latitude and longitude
 */
function get_distance_SQL($lat, $lon)
{
	$sql = "
	3957 * 2 *
	atan2(
		sqrt(
			pow((sin(0.0174*(lat-$lat)/2)),2) +
			cos(0.0174*$lat) * cos(0.0174*lat) *
			pow((sin(0.0174*(lon-$lon)/2)),2)
		)
		,
		sqrt(1-
			(
				pow((sin(0.0174*(lat-$lat)/2)),2) +
				cos(0.0174*$lat) * cos(0.0174*lat) *
				pow((sin(0.0174*(lon-$lon)/2)),2)
			)
		)
	)
	as distance";

	return $sql;
}

Fourth, you wrap it all together with a few lines of PHP. I’m using CodeIgniter to handle the SQL call, so your mileage may vary a bit on the exact implementation details.

list($lat, $lon) = $this->get_zip_point($user_zip_code);

$this->db->select("users.username, zip_code.city, zip_code.state_prefix," . $this->get_distance_SQL($lat, $lon), FALSE)
		->from('users')
		->join('zip_code',"users.zip_code = zip_code.zip_code")
		->order_by('distance', 'asc');

$search_results = $this->db->get();

And shazam, you now have a way to sort a set of SQL results by distance.

About Me

by on June 25, 2009 in personal

Recently I decided it would be wise to fill in my “about me” page. The page was looking a little sad considering I’ve been blogging here for a few months. Unfortunately I’m quite terrible at writing about myself. The fact that there was enough content on my plentyoffish profile for Rachel to be interested and message me still amazes me to this day.

Anyway, since I’m not good at writing about myself I tried to think of inventive ways that I could write about myself from a different perspective. And then I remembered reading an article on How to Hack Someone’s Mind. And I thought, hey, why can’t I hack my own mind?

And so I did. I started reviewing all of the bookmarks I’d saved to delicious while making notes on the categories and themes of the articles. I came to recognize that:

  • I’m a frugal entrepreneur trying to create a site in an established market (an underdog)
  • I’m a self-starter planning to develop the site myself (programming and design)
  • I’m planning to market the site using primarily social media

Sounds about right to me. Makes me wish I’d been using delicious back when I was dating — it would’ve made creating profiles roughly 27 times easier.

Making CodeIgniter’s Pagination library play nice with jQuery

by on June 22, 2009 in development

I’m back, and despite the fact that I haven’t posted in a bit I’ve been accomplishing quite a bit on the site …or maybe not. You see, I started keeping track of the hours I’ve been spending working on the site. Over the last 6 days I’ve put in 11 hours on the site. That’s a pretty good amount of time I’ve put in, actually, considering that my goal is to put in at least 8 hours (the equivalent of one work day) a week on the site.

On the (perhaps) not so good side, the majority of that time has been spent implementing pagination on the search results page. You would think this would be incredibly easy considering that CodeIgniter has a Pagination class. It shouldn’t take me approximately 8 hours, right? Well, apparently it does when you’re trying to use AJAX to handle your pagination. I was even perfectly willing to accept a complete hack for a solution, but alas, anything I tried to do to cobble together a fix for one issue only introduced another issue somewhere else.

Eventually jQuery came to my rescue. In the configuration for Pagination I set the base_url to ” (an empty string) so that the href on the pagination links would contain only the number for the search results. Then I set up jQuery to find all of the anchor links in my paging div, stop the normal link function, and call my function to load in the search results. The jQuery code came out like this:

$(document).ready(function(){
  $("div.paging > a").click(function(e){
    // stop normal link click
    e.preventDefault();

    $('div#search_results').load("/search/get_results/"+$(this).attr("href"));
  });
});

This is still something of a hack as I’m basically creating fake href values for the anchor tags, but whatever, it works! I can come back later and fix it. At this point hacks are okay. Speed is much more valuable than perfectly elegant code as I want to find out if my idea is solid before investing oodles of time in it.

Form validation callbacks and private functions

by on June 1, 2009 in development

If you’re familiar with CodeIgniter you probably know about callbacks within form validation. Callbacks allow you to do your own validation of fields. For example, if you want to verify if a username is unique then you could create a username_check function to validate the field. You add the callback rule like this:

$this->form_validation->set_rules( 'username', 'Username',
  'trim|required|callback_username_check' );

And then create a matching function like this:

function username_check( $username )
{
  // some code
}
?>

However, as this is currently implemented someone could access your function as a page at a URL like example.com/index.php/login/username_check/ if they guessed the function name. While that may not have any ill side-effects, it’s probably just as well if no one can access the function besides you.

In come private functions for controllers, which allow you to create a function like this:

function _utility()
{
  // some code
}

And if you try to access the function via a URL, like example.com/index.php/login/_utility/, you’ll get a 404 (page not found).

You probably see where I’m going with this. If you create your callbacks as private functions, no one will be able to access the callbacks as pages. It’s quite simple to do. You add an underscore before your callback function name:

function _username_check( $username )
{
  $valid_username = TRUE; // You would perform some kind of check on the field here

  if ($valid_username == FALSE)
  {
    $this->form_validation->set_message('_username_check', 'The username you have provided is not valid.');
    return FALSE;
  }
  else
  {
    return TRUE;
  }
}

And then add an underscore in your callback rule (note the two underscores after callback):

$this->form_validation->set_rules( 'username', 'Username',
  'trim|required|callback__username_check' );

Done!

Authentication with CodeIgniter

by on May 29, 2009 in development

Having finished the registration process, I realized I needed some way to keep people logged in, let them log in, log out, retrieve their password, possibly have some kind of email validation, etc. All sorts of things. I started looking through the CodeIgniter user guide and about they closest things they have is the session library. While you could use it to track if a user is logged in, that’s about the extent of the value it would provide to you.

At first I looked for ways that I might be able to at least run checks on pages to see if a user is logged in or not. It turns out that’s not terribly difficult (once you figure out how to do it). You need to create your own authentication controller for use with pages where you want to check if the user is logged in. Cool, that works. I could throw the log in/log out functionality in easily enough, but resetting passwords and validating email addresses would be a whole different matter.

That’s when I decided that I should start looking for a user-contributed authentication library. The CodeIgniter Wiki has a nice list of them. As I started looking through them and all their various features I realized I should come up with a list of the features that I needed to have. Here’s what I was looking for in a library:

  • Good coding practices
  • Good documentation
  • Small number of files
  • Database implementation that’s not complex
  • Login using username or email address
  • Emails for lost passwords
  • Automatic login
  • Hashing of passwords
  • Maximum number of failed login attempts
  • Emails for activation (nice to have)
  • reCAPTCHA support (nice to have)

Not too harsh, right? Unfortunately most of the libraries ended up being too large, too simple, or completely lacking in documentation. It was hard to find a library that fit somewhere in the middle. I did find one library that might meet all of my requirements, and if not, it’s awfully close: Redux 2 (beta). It has all of the functionality I want, it doesn’t contain a crazy number of files, the database is simple and well thought out, there’s a complete sample application that makes use of the library, and more. I’m going to give it a try and comment in my next post on how well it works.

I should also mention in the course of my search I came across an excellent post on Stack Overflow regarding authentication libraries for CodeIgniter. There’s a lot of great discussion on that page and Redux was also listed there as being one of the better solutions available.