Search SQL database

elite311 · August 26, 2011

So I'm trying to make a web app for work that allows our reps to enter the customers address then it search a database and return the closest location in the database to the address they entered. I'm pretty new to SQL so any help or tutorials would be great.

With a lot of help from tutorials I have it working right now if you enter the 2 address's here's my code and you can see the working page here http://s262833979.onlinehome.us/Dan/servicecall.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<meta name="robots" content="noindex,follow" />
<title>Book A Service Call</title>
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAh2r2YFiBfUkYnPMfobDlIBSZzGBYWmrklI-wZRptp7_OVA51VBTka31gdS-Bl3rNQ2o2ceKs3gSO9g" type="text/javascript"></script>
<!-- According to the Google Maps API Terms of Service you are required display a Google map when using the Google Maps API. see: http://code.google.com/apis/maps/terms.html -->
<script type="text/javascript">

var geocoder, location1, location2, gDir;

function initialize() {
	geocoder = new GClientGeocoder();
	gDir = new GDirections();
	GEvent.addListener(gDir, "load", function() {
		var drivingDistanceMiles = gDir.getDistance().meters / 1609.344;
		var drivingDistanceKilometers = gDir.getDistance().meters / 1000;
		document.getElementById('results').innerHTML = '<strong>Customers Location: </strong>' + location1.address + ' (' + location1.lat + ':' + location1.lon + ')<br /><strong>Closest Superior branch: </strong>' + location2.address + ' (' + location2.lat + ':' + location2.lon + ')<br /><strong>Distance to closest Superior branch: </strong>' + drivingDistanceKilometers + ' kilometers';
	});
}

function showLocation() {
	geocoder.getLocations(document.forms[0].address1.value, function (response) {
		if (!response || response.Status.code != 200)
		{
			alert("Sorry, we were unable to geocode the first address");
		}
		else
		{
			location1 = {lat: response.Placemark[0].Point.coordinates[1], lon: response.Placemark[0].Point.coordinates[0], address: response.Placemark[0].address};
			geocoder.getLocations(document.forms[0].address2.value, function (response) {
				if (!response || response.Status.code != 200)
				{
					alert("Sorry, we were unable to geocode the second address");
				}
				else
				{
					location2 = {lat: response.Placemark[0].Point.coordinates[1], lon: response.Placemark[0].Point.coordinates[0], address: response.Placemark[0].address};
					gDir.load('from: ' + location1.address + ' to: ' + location2.address);
				}
			});
		}
	});
}

</script>
</head>

<body onload="initialize()">

<form action="#" onsubmit="showLocation(); return false;">
	<p>
		<input type="text" name="address1" value="Address 1" />
		<input type="text" name="address2" value="Address 2" />
		<input type="submit" value="Search" />
	</p>
</form>
<p id="results"></p>

</body>
</html>

I also set up an SQL database and here is what it looks like :

Field

Type

Length/values

id

INT

3

store_name

VARCHAR

20

street_number

VARCHAR

60

city

VARCHAR

60

province

VARCHAR

10

postal_code

VARCHAR

10

latitude

FLOAT

10.6

longitude

FLOAT

10.6

I have read a lot about the query and came up with this but I'm not sure how to incorporate it into my page, I'm also not sure if this is 100% correct

$query = "SELECT `store_name`,`street_number`,`city`,`province`,`postal_code`,`latitude`,`longitude`, ( 3959 * acos( cos( radians($user_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($user_long) ) + sin( radians($user_lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM locations ORDER BY distance";

I was really hoping someone could help me figure this out or point me in the right direction.

Thanks in advance

elite311 · August 27, 2011

Anyone? I'm really stumped, I just want to have to enter the customers address and it find the closest branch in the date base and then it display how many kilometers away the branch is from the customer

fenway · August 28, 2011

Don't use great circle distance -- it's just unnecessarily slow.

elite311 · August 31, 2011

I've been trying to get this to work for a week now, I can't figure it out. Does anyone know of a good tutorial? I have read a lot of them on store locators but I just want to modify what I already have as I want to build on it after. I really need some help with this, if someone knows how I can change the code above so it will search the database for the closest location to the one I enter instead of me having to enter a 2nd location.

elite311 · August 31, 2011

Ok so after a lot of reading I found a tutorial and have a working locator now. I'm defiantly starting to understand this a little better as I've been working through it but still have a ways to go.

You can see it here http://s262833979.onlinehome.us/Dan/postcode/search.php

The only hurdle now is it's using the Haversine formula to find the closest location, can someone help me figure out how I can make it show the distance as driving distance and it locate the closest location based on driving distance instead of the Haversine formula?

I have attached the file

Thanks

[attachment deleted by admin]

fenway · September 1, 2011

If you have to attach a script, that means it's too big.

It's too big because you're trying to post the entire thing.

I already told you not to use great circle distances -- and you ignored me.

Sign In

Search SQL database

Recommended Posts

elite311

Link to comment

Share on other sites

elite311

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

elite311

Link to comment

Share on other sites

elite311

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information