[armyguydave69]

The only thing I'm familiar with MySql is Insert, Select, Update, and Delete, besides that I don't know much about Mysql. I need to do what I believe would be called a stored procedure. I found some sample code online. I was just hoping somebody can point me in the right direction on how to use it.

Is it like MsSql where you actually store it in the database, or do I store it in the php code? also I provided the code below, to sort it, by the distance do I just add the sort command?

Thanks

#!/usr/bin/perl

#########################################################
# by R. Don Henning									 #
# zip_distance.pl									   #
#########################################################

use DBI;

#######################################################
#	 PRE: two zipcodes							   #
# PROCESS: validate zipcodes,						 #
#		  return error string if invalid			 #
#	POST: return mileage between two zipcodes		#
#######################################################

### MAIN ##############################################

	$dbh = DBI->connect('DBI:mysql:zipcodes','mtndew','p@$$w0rd') || die "Error connecting $DBI::errstr\n";

	my $zip1 = $ARGV[0];
	my $zip2 = $ARGV[1];
	my $miles = &zip_to_zip_distance($zip1,$zip2);
	print "$miles miles between zip codes $zip1,$zip2\n";
	
	$dbh->disconnect;

### END MAIN ##########################################

# get the distance in miles between two zipcodes
sub zip_to_zip_distance() {
	my ($zip1,
		$zip2,
		$earth_radius,
		$div,
		$count1,
		$count2,
		$miles,
		$lat1,
		$lon1,
		$lat2,
		$lon2,
		$x
		);

	$zip1 = $_[0];
	$zip2 = $_[1];

	$earth_radius = 3959;
	$div = 57.2958;

	# make sure we have valid zip codes that exist in the database
	($count1) = $dbh->selectrow_array("select count(*) from zipcodes where zipcode = $zip1");
	($count2) = $dbh->selectrow_array("select count(*) from zipcodes where zipcode = $zip2");

	if ($count1 eq '0' || $count2 eq '0') {
		$miles = "Error: Invalid Zipcode";
		$dbh->disconnect;
		exit;
	}
	else {
		# get the latitude and longitude for both zip codes
		($lat1,$lon1) = $dbh->selectrow_array("select latitude,longitude from zipcodes where zipcode = $zip1");
		($lat2,$lon2) = $dbh->selectrow_array("select latitude,longitude from zipcodes where zipcode = $zip2");

		# calculate the distance(miles) between two sets of longitude/latitude coordinates
		$x = (sin($lat1/$div) * sin($lat2/$div)) + (cos($lat1/$div) * cos($lat2/$div) * cos(($lon2/$div) - ($lon1/$div)));
		$miles = $earth_radius * atan2(sqrt(1 - ($x**2)),$x);
	
		# round float to 1 decimal place
		$miles = sprintf("%.1f",$miles);
	}
	return($miles);
}

[PsychoCoder]

Here's some information on using MySql Stored Procedures with PHP that I think you might find useful

[ajwsurfer]

These articles show the MySQL side of things:

Here is an article that might open the door
http://www.linux-mag...L/(kategorie)/0

These articles cover the topic in more depth, but you will need to sign up for a free account in order to access them:
http://www.linux-mag...q...amp;x=0&y=0

Here is the documentation for the "call procedure" statement:
http://dev.mysql.com....0/en/call.html

And finally, here is something about getting results from the PHP end of things:
http://amountaintop....-solution-qcodo
In the simplest form we have:
http://www.hotscript...-using-php.html

This post has been edited by ajwsurfer: 09 October 2009 - 07:33 AM