2 Replies - 880 Views - Last Post: 09 October 2009 - 07:32 AM

#1 armyguydave69  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 17-September 09

Stored Procedure MySql

Posted 08 October 2009 - 11:17 AM

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);
}




Is This A Good Question/Topic? 0
  • +

Replies To: Stored Procedure MySql

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1633
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Stored Procedure MySql

Posted 08 October 2009 - 11:25 AM

Here's some information on using MySql Stored Procedures with PHP that I think you might find useful :)
Was This Post Helpful? 1
  • +
  • -

#3 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: Stored Procedure MySql

Posted 09 October 2009 - 07:32 AM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1