PDA

View Full Version : $10-$50+ for simple MySQL help


jlui
06-10-2008, 04:32 PM
1) Project Details: (be as specific as possible):
Take existing custom code (stored procedure and function) and get it working on my simple database and function with my simple search and results page.

2) Payment Amount:
$10-$50+ depends on how much time you think this would take.

3) Payment method/ details (Paypal, check? Timeline?):
Paypal is preferred. Will discuss other options if required.

4) Additional Info (about project or potential bidders):
I had an expert Php/MySQL coder create a custom procedure for me. Due to unexpected travel, is unable to help me get this installed and running and my knowledge as a web designer leaves me unable to finish this myself.

In a nut shell: A user enters his zip code, chooses an age from and age to as well as a distance range and sends the search. The results return all members that fall within the age range and live within the required distance from the users zip code.

The custom code is below: I need to find someone that can take this code and make it work with my zipcode/members database. Please PM me with your estimates and questions. I will of course supply all access needed.

Usage:
CALL GetNearby(76053, 100, '1945-12-01', '2000-01-31');


DELIMITER $$

DROP FUNCTION IF EXISTS `GetDistance`$$

CREATE FUNCTION `GetDistance`(
lat1 numeric (9,6),
lon1 numeric (9,6),
lat2 numeric (9,6),
lon2 numeric (9,6)
) RETURNS decimal (10,5)
BEGIN
DECLARE x decimal (20,10);
DECLARE pi decimal (21,20);
SET pi = 3.14159265358979323846;
SET x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180 ) + cos(
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos( abs( (lon2 * pi/180) -
(lon1 *pi/180) ) );
SET x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
RETURN ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344; END $$

DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `GetNearby`$$

CREATE PROCEDURE `GetNearby`(
zipbase varchar (6),
range numeric (15),
date1 DATE,
date2 DATE
)
BEGIN

DECLARE lat1 decimal (5,2);
DECLARE long1 decimal (5,2);
DECLARE rangeFactor decimal (7,6);
SET rangeFactor = 0.014457;

CREATE TEMPORARY TABLE TempTable ( zipcode int(5) ) TYPE=HEAP;

SELECT Lat,Lon into lat1,long1 FROM zipcode WHERE Zip = zipbase;

INSERT TempTable
SELECT B.Zip
FROM zipcode AS B
WHERE
B.Lat BETWEEN lat1-(range*rangeFactor) AND lat1+(range*rangeFactor)
AND B.Lon BETWEEN long1-(range*rangeFactor) AND long1+(range*rangeFactor)
AND GetDistance(lat1,long1,B.Lat,B.Lon) <= range;


SELECT * FROM member,TempTable WHERE member.dob BETWEEN date1 AND date2 AND TempTable.zipcode=member.ZipCode;


DROP TABLE TempTable;

END $$

DELIMITER ;