New Search Engine

johnathanamber · October 20, 2009

Hey everyone,

I have a ZIP Code Radius search engine already functional. it displays all of the ZIP codes within a specified mileage around a central ZIP code.

I have an auction site that the client wants the search engine to be able to search for items within a given mileage radius.

I.e. if a person wants to search for corn within 20 miles of the 12345 ZIP code, they want those auctions displayed.

What is the best way to do this?

Like I said I already have the ZIP Code radius search engine working... now I need to be able to searhc for a particular item within those ZIP Codes.

Everything is running from the same MySQL Database.

You can see the site at http://www.harvestauction.com

Under the 'Buy' Menu item you see the 'Search by ZIP' page which directs you to the ZIP Code Radius search.

The database is named 'harvestauction', the table in the DB which are used by the Zip Code Radius is 'zip_codes' and the fields are:

zipcode

latitude

longitude

city

state

country

type

The table with the auctions is: 'jos_bid_auctions'. And those fields are:

id

userid

title

shortdescription

description

picture

link_extern

initial_price

currency

BIN_price

auction_type

automatic

payment

shipment_info

shipment_price

start_date

end_date

closed_date

params

published

closed_offer

closed_by_admin

hits

modified

newmessages

winnerid

cat (category)

auction_nr

nr_items

featured

reserve_price

min_increase

extended_offer

payment_info

Would it be better to first search via ZIP code and then the item in question... or the item first and than the zip codes within the specific area?

I figured that either way... I need the following fields searched in the jos_bid_auctions:

shortdescription

description

cat

And the following fields searched in zipcode:

zipcode

Thank you and God Bless,

Johnathan

lemmin · October 20, 2009

You could have tables for all the different radius categories and relate the zip codes to each other that way.

Like:

zipcode_relations_20[, 30, 40, etc.]

zipcode1 zipcode2

Or you could have a huge relation table with all the different distances that the zip codes are related.

zipcode_relations

zipcode1 zipcode3 distance

I'm sure that the second way will take more space and would be slower, but might be easier to query.

Anyway, once you have tables like that set up, you can do the search and limit the zip codes in the same query. That way, you won't be doing the same calculations at run-time.

mikesta707 · October 20, 2009

I would add a zip code column to the table with all the auctions, and in the query just retrieve all the rows of the specified type of auction, and in the correct zip code range

johnathanamber · October 20, 2009

@lemmin,

I was thinking that it would search via the ZIP code within the area and then search for the items via those Zip codes.

@mikesta707,

that's an idea... I can automatically parse from the aucntioneer profile for that field...

I'd still have to find all of the items within a range of Zip codes.

mikesta707 · October 20, 2009

Indeed, but if each row had a zip code column, that task would be pretty easy. depending, of course, on how your zip code search engine works

johnathanamber · October 20, 2009

@mikesta707,

Currently the PHP does the work, the DB is strictly just that... a DB.

So to add a hidden field on the PHP page and then add the field into the DB... no problem.

Then it is a matter of displaying the available auctions.

Just trying to figure out how to do that effectively.

mikesta707 · October 20, 2009

indeed. Without seeing any code though, I can't be of much help

johnathanamber · October 20, 2009

I'll see what I come up with after adding the ZIP field in the auctions themselves.

Thanks a ton for the idea!

God bless,

Johnathan

johnathanamber · October 28, 2009

Here is the code that I have thus far:

<?php ?>

<html>

<head>

</head>

<body style="font-family: arial; color: black; font-size: 10px;">



<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform">


<h3>Search for auctions</h4>
Use the form below to search for auctions within a ZIP radius as well as for other criteria.<br />


<fieldset>

<legend>ZIP Code Radius</legend>



<table style="font-family: arial; color: black; font-size: 10px;">

<tr>
<td>Enter your search term:</td><td><input type="text" name="description" size="20" maxlength="20" /></td>
</tr>
<tr>

    <td>Enter your ZIP Code:</td><td><input type="text" name="zipcode" size="5" maxlength="5" /></td>

</tr>

<tr>

    <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td>

    </tr>

<tr>

<td>Sort Results by:</td><td><select name="sortby">

								<option value="default">default</option>

								<option value="city">City</option>

								<option value="state">State</option>

								<option value="zip">ZIP</option>

								</select></td>

</tr>

</table>

    <input type="submit" name="submit" value="Submit" />



</fieldset>



</form>



</body>

</html>

<?php



if(isset($_POST['submit'])) {


$description = ($_POST['description']);

$zipcode = ($_POST['zipcode']);

$distance = ($_POST['distance']);

$sortby = ($_POST['sortby']);



if(!preg_match('/^[0-9]{5}$/', $zipcode)) {

	echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>";

}elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){

	echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>";

}else{

include("hadb.php");



	//query for coordinates of provided ZIP Code

	if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zipcode = '$zipcode'")) {

		echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>";

	}else{

		if(mysql_num_rows($rs) == 0){

			echo "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>";	

		}else{

			//if found, set variables

			$row = mysql_fetch_array($rs);

			$lat1 = $row['latitude'];

			$lon1 = $row['longitude'];

			$d = "$distance";

			//earth's radius in miles

			$r = 3959;



			//compute max and min latitudes / longitudes for search square

			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));

			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));

			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));



			//display information about starting point

			//provide max and min latitudes / longitudes

			echo "Your originating ZIP code:<table><tr><td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>ZIP</th><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td></tr>
				<tr><th>Search Term</th><td>$description</td></tr>

				<tr><th>City</th><td>$row[city]</td></tr>

				<tr><th>State</th><td>$row[state]</td></tr>

				<tr><th>Lat</th><td>$lat1</td></tr>

				<tr><th>Lon</th><td>$lon1</td></tr>

				</table>

				</td>

				<td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>Max Lat (N)</th><td>$latN</td></tr>

				<tr><th>Min Lat (S)</th><td>$latS</td></tr>

				<tr><th>Max Lon (E)</th><td>$lonE</td></tr>

				<tr><th>Min Lon (W)</th><td>$lonW</td></tr>";

			echo "</table>

				</td></tr></table><br />";



			//find all coordinates within the search square's area

			//exclude the starting point and any empty city values
			//also get the description from the DB





		if ($sortby == "default"){

			$query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state, city, latitude, longitude, zipcode";

		}

		if ($sortby == "state"){

			$query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY state";

		}

		if ($sortby == "city"){

			$query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY city";

		}

		if ($sortby == "zip"){

			$query = "SELECT * FROM zip_codes WHERE (latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != '' ORDER BY zipcode";

		}

			if(!$rs = mysql_query($query)) {

				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

			}elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.</p>";								

			}else{

				$auctionquery = mysql_query("SELECT 'description','zipcode' FROM jos_bid_auctions WHERE (zipcode = \"$query\") AND (description like \"%$description%\") ORDER BY 'zipcode'");

		}

			if(!$rs = mysql_query($auctionquery)) {

				echo "<p><strong>There was an error selecting nearby ZIP Codes/Information from the database. Please try again.</strong></p>";

			}elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No auctions have been located within the distance specified.</strong> Please try wider distance.</p>";								

			}else{
				while($auctionquery = mysql_fetch_array($rs)) {
					$zipcode = $auctionquery[zipcode];
					$description = $auctionquery[description];

				echo "<table>
				<tr>
				<td>Zip Code:</td><td>$row[zipcode]</td><td>Description:</td><td>$description</td>
				</tr>
				</table>";
				}

			}

		}

	}
}
}

?>

OK I am stuck... rather I am tired and a bit confused due to being tired.

What I am trying to do is gather a list of ZIP codes via the $query variable and display the data from the DB, namely the 'zipcode' and the 'description', and display them in a table.

The zipcodes are generated via a distance from the originating zipcode. Then I need to display the auctions within that radius.

Thank you very much and God bless,

Johnathan

DavidAM · October 28, 2009

You should be able to do it in a single query with something like this:

SELECT A.description, A.zipcode, Z.city, Z.state 
FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode
WHERE A.description like '%$description%'
AND Z.latitude BETWEEN $latN AND $latS 
AND Z.longitude BETWEEN $lonE AND $lonW 
AND Z.latitude != $lat1 AND Z.longitude != $lon1
ORDER BY Z.state, Z.city, Z.zipcode

I'm not sure why you have the last AND phrase in there, wouldn't that tend to exclude anything in the search zipcode?

johnathanamber · October 28, 2009

@DavidAM,

Thank you for the reply. I will work on this heavily tonight...

What are the A. and Z. before the terms?

johnathanamber · October 28, 2009

Oh the last AND was there to get the information of auctions that have the ZIP code from the array as well as the description that meets the term that they are looking for... i.e. cars, corn, etc.

GingerRobot · October 28, 2009

What are the A. and Z. before the terms?

They are aliases for the tables. It just means you don't have to write the full table name each time. You can specify a table alias in the FROM clause.

johnathanamber · October 28, 2009

oh... so then 'Z.' is for the zipcode table?

The what is 'A.'?

johnathanamber · October 28, 2009

You should be able to do it in a single query with something like this:
SELECT A.description, A.zipcode, Z.city, Z.state 
FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode
WHERE A.description like '%$description%'
AND Z.latitude BETWEEN $latN AND $latS 
AND Z.longitude BETWEEN $lonE AND $lonW 
AND Z.latitude != $lat1 AND Z.longitude != $lon1
ORDER BY Z.state, Z.city, Z.zipcode
I'm not sure why you have the last AND phrase in there, wouldn't that tend to exclude anything in the search zipcode?

OK so now I have this:

<?php ?>

<html>

<head>

</head>

<body style="font-family: arial; color: black; font-size: 10px;">



<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform">


<h3>Search for auctions</h4>
Use the form below to search for auctions within a ZIP radius as well as for other criteria.<br />


<fieldset>

<legend>ZIP Code Radius</legend>



<table style="font-family: arial; color: black; font-size: 10px;">

<tr>
<td>Enter your search term:</td><td><input type="text" name="description" size="20" maxlength="20" /></td>
</tr>
<tr>

    <td>Enter your ZIP Code:</td><td><input type="text" name="zip" size="5" maxlength="5" /></td>

</tr>

<tr>

    <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td>

    </tr>

<tr>

<td>Sort Results by:</td><td><select name="sortby">

								<option value="default">default</option>

								<option value="city">City</option>

								<option value="state">State</option>

								<option value="zip">ZIP</option>

								</select></td>

</tr>

</table>

    <input type="submit" name="submit" value="Submit" />



</fieldset>



</form>



</body>

</html>

<?php



if(isset($_POST['submit'])) {

//==================================================================================================================================
//													Variables from form
//==================================================================================================================================

$description = ($_POST['description']);

$zip = ($_POST['zip']);

$distance = ($_POST['distance']);

$sortby = ($_POST['sortby']);



if(!preg_match('/^[0-9]{5}$/', $zip)) {

	echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>";

}elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){

	echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>";

}else{

//==================================================================================================================================
//													Connect to the DB
//==================================================================================================================================

include("hadb.php");


//==================================================================================================================================
//												Get coordinates from zip_codes
//==================================================================================================================================


	if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zipcode = '$zip'")) {

		echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>";

	}else{

		if(mysql_num_rows($rs) == 0){

			echo "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>";	

		}else{


//==================================================================================================================================
//													Variables from DB
//==================================================================================================================================


			$row = mysql_fetch_array($rs);

			$lat1 = $row['latitude'];

			$lon1 = $row['longitude'];

			$d = "$distance";

			//earth's radius in miles

			$r = 3959;


//==================================================================================================================================
//												Compute max and min LAT and LONG 
//==================================================================================================================================


			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));

			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));

			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));


//==================================================================================================================================
//										Display Originating Information with LAT and LONG
//==================================================================================================================================


			echo "Your originating ZIP code:<table><tr><td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>ZIP</th><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td></tr>
				<tr><th>Search Term</th><td>$description</td></tr>

				<tr><th>City</th><td>$row[city]</td></tr>

				<tr><th>State</th><td>$row[state]</td></tr>

				<tr><th>Lat</th><td>$lat1</td></tr>

				<tr><th>Lon</th><td>$lon1</td></tr>

				</table>

				</td>

				<td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>Max Lat (N)</th><td>$latN</td></tr>

				<tr><th>Min Lat (S)</th><td>$latS</td></tr>

				<tr><th>Max Lon (E)</th><td>$lonE</td></tr>

				<tr><th>Min Lon (W)</th><td>$lonW</td></tr>";

			echo "</table>

				</td></tr></table><br />";



			//find all coordinates within the search square's area

			//exclude the starting point and any empty city values
			//also get the description from the DB


//==================================================================================================================================
//															Sort by...
//==================================================================================================================================

$query = "SELECT A.description, A.zipcode, Z.city, Z.state
FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode
WHERE A.description like '%$description%'
AND Z.latitude BETWEEN $latN AND $latS
AND Z.longitude BETWEEN $lonE AND $lonW
AND Z.latitude != $lat1 AND Z.longitude != $lon1
ORDER BY Z.state, Z.city, Z.zipcode";


			if(!$rs = mysql_query($query)) {

				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

			}elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.</p>";								

			}

//==================================================================================================================================
//											Display The Information - OLD ZIP table
//==================================================================================================================================

			 /*else{

				//output all matches to screen

				echo "Click on the ZIP Code to view the available auctions:<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

					<tr><th>ZIP</th>

					<th>City</th>

					<th>State</th>

					<!-- <th>Latitude</th>

					<th>Longitude</th> -->

					<th>Distance</th></tr>";

				while($row = mysql_fetch_array($rs)) {

					$truedistance = acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r;



					if($truedistance < $d) {

						echo "<tr><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td>

							<td>$row[city]</td>

							<td>$row[state]</td>

							<!-- <td>$row[latitude]</td>

							<td>$row[longitude]</td> -->

							<td>$truedistance</td></tr>";

					}

				}

				echo "</table><br />";

			} */

//==================================================================================================================================
//											Display The Information from Auctions
//==================================================================================================================================


			elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No auctions have been located within the distance specified.</strong> Please try wider distance.</p>";								

			}else{
				while($query = mysql_fetch_array($rs)) {
					$zipcode = $query[zipcode];
					$description = $query[description];

				echo "<table>
				<tr>
				<td>Zip Code:</td><td>$row[zipcode]</td><td>Description:</td><td>$description</td>
				</tr>
				</table>";
				}

			}

		}

	}
}
}

?>

JonnoTheDev · October 28, 2009

You could have tables for all the different radius categories and relate the zip codes to each other that way.

Like:

zipcode_relations_20[, 30, 40, etc.]

zipcode1 zipcode2

Or you could have a huge relation table with all the different distances that the zip codes are related.

zipcode_relations

zipcode1 zipcode3 distance

I'm sure that the second way will take more space and would be slower, but might be easier to query.

Anyway, once you have tables like that set up, you can do the search and limit the zip codes in the same query. That way, you won't be doing the same calculations at run-time.

That is completely crazy and would result in expensive queries. Your best solution is to use latitude & longitude (Haversine Formula) values to return items in a given distance from a point of origin.

DavidAM · October 28, 2009

That looks pretty close. Just a couple of points:

1) You have the "elseif(mysql_num_rows($rs) == 0){" in there twice. Once before the code you commented out and once after. You might want to fix that.

2) You are creating a table for each record, you should move the table tags outside the loop:

           }else{
               echo "<table>";
               while($query = mysql_fetch_array($rs)) {
                  $zipcode = $query[zipcode];
                  $description = $query[description];

               echo "<tr>
               <td>Zip Code:</td><td>$row[zipcode]</td><td>Description:</td><td>$description</td>
               </tr>";
               }
               echo "</table>';
            }

or something like that.

One thing I should have mentioned before; performance will (most likely) be better if you can put an index on the zipcode column in the auctions table, and it would (probably) help if you could put an index on latitude and/or longitude in the zipcodes table (actually, only one of those would be used, but I would probably create both unless space is an issue).

As GingerRobot said, The "A" and "Z" in the query are aliases for the tables in the FROM clause. Since both tables have a column named zipcode, we have to indicate which table we are referring to when we mention that column in the query. We could just write: jos_bid_auctions.zipcode = zipcodes.zipcode. But thats a lot of typing. So, when we add the tables to the FROM clause we give them an alias" "FROM jos_bid_auctions AS A JOIN zipcodes AS Z". I left the "AS" word out because it is optional and I'm lazy. I choose A (for the auctions table) to make it easier to read, every time you see A. you think Auctions; and I choose Z (for the zipcodes table); see, the letters match up so it's easy to remember which is which. I could just as easily have said: "FROM jos_bid_auctions AS Dog JOIN zipcodes AS Cat", but that would confuse me when I see "AND Cat.zipcode = 12345" which table is that, again? :confused:

johnathanamber · October 29, 2009

OK, here's what I've got now:

<?php ?>

<html>

<head>

</head>

<body style="font-family: arial; color: black; font-size: 10px;">



<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform">


<h3>Search for auctions</h4>
Use the form below to search for auctions within a ZIP radius as well as for other criteria.<br />


<fieldset>

<legend>ZIP Code Radius</legend>



<table style="font-family: arial; color: black; font-size: 10px;">

<tr>
<td>Enter your search term:</td><td><input type="text" name="description" size="20" maxlength="20" /></td>
</tr>
<tr>

    <td>Enter your ZIP Code:</td><td><input type="text" name="zip" size="5" maxlength="5" /></td>

</tr>

<tr>

    <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td>

    </tr>

<tr>

<td>Sort Results by:</td><td><select name="sortby">

								<option value="default">default</option>

								<option value="city">City</option>

								<option value="state">State</option>

								<option value="zip">ZIP</option>

								</select></td>

</tr>

</table>

    <input type="submit" name="submit" value="Submit" />



</fieldset>



</form>



</body>

</html>

<?php



if(isset($_POST['submit'])) {

//==================================================================================================================================
//													Variables from form
//==================================================================================================================================

$description = ($_POST['description']);

$zip = ($_POST['zip']);

$distance = ($_POST['distance']);

$sortby = ($_POST['sortby']);



if(!preg_match('/^[0-9]{5}$/', $zip)) {

	echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>";

}elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){

	echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>";

}else{

//==================================================================================================================================
//													Connect to the DB
//==================================================================================================================================

include("hadb.php");


//==================================================================================================================================
//												Get coordinates from zip_codes
//==================================================================================================================================


	if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zipcode = '$zip'")) {

		echo "<p><strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.</p>";

	}else{

		if(mysql_num_rows($rs) == 0){

			echo "<p><strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.</p>";	

		}else{


//==================================================================================================================================
//													Variables from DB
//==================================================================================================================================


			$row = mysql_fetch_array($rs);

			$lat1 = $row['latitude'];

			$lon1 = $row['longitude'];

			$d = "$distance";

			//earth's radius in miles

			$r = 3959;


//==================================================================================================================================
//												Compute max and min LAT and LONG 
//==================================================================================================================================


			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));

			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));

			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));


//==================================================================================================================================
//										Display Originating Information with LAT and LONG
//==================================================================================================================================


			echo "Your originating ZIP code:<table><tr><td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>ZIP</th><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td></tr>
				<tr><th>Search Term</th><td>$description</td></tr>

				<tr><th>City</th><td>$row[city]</td></tr>

				<tr><th>State</th><td>$row[state]</td></tr>

				<tr><th>Lat</th><td>$lat1</td></tr>

				<tr><th>Lon</th><td>$lon1</td></tr>

				</table>

				</td>

				<td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>Max Lat (N)</th><td>$latN</td></tr>

				<tr><th>Min Lat (S)</th><td>$latS</td></tr>

				<tr><th>Max Lon (E)</th><td>$lonE</td></tr>

				<tr><th>Min Lon (W)</th><td>$lonW</td></tr>";

			echo "</table>

				</td></tr></table><br />";



			//find all coordinates within the search square's area

			//exclude the starting point and any empty city values
			//also get the description from the DB


//==================================================================================================================================
//															Sort by...
//==================================================================================================================================
			if ($sortby == "default"){
				$query = "SELECT A.description, A.zipcode, Z.city, Z.state
					FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode
					WHERE A.description like '%$description%'
					AND Z.latitude BETWEEN $latN AND $latS
					AND Z.longitude BETWEEN $lonE AND $lonW
					AND Z.latitude != $lat1 AND Z.longitude != $lon1
					ORDER BY Z.state, Z.city, Z.zipcode";


			if(!$rs = mysql_query($query)) {

				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

//==================================================================================================================================
//											Display The Information - OLD ZIP table
//==================================================================================================================================

			 /*else{

				//output all matches to screen

				echo "Click on the ZIP Code to view the available auctions:<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

					<tr><th>ZIP</th>

					<th>City</th>

					<th>State</th>

					<!-- <th>Latitude</th>

					<th>Longitude</th> -->

					<th>Distance</th></tr>";

				while($row = mysql_fetch_array($rs)) {

					$truedistance = acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r;



					if($truedistance < $d) {

						echo "<tr><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td>

							<td>$row[city]</td>

							<td>$row[state]</td>

							<!-- <td>$row[latitude]</td>

							<td>$row[longitude]</td> -->

							<td>$truedistance</td></tr>";

					}

				}

				echo "</table><br />";

			} */

//==================================================================================================================================
//											Display The Information from Auctions
//==================================================================================================================================


			}elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No auctions have been located within the distance specified.</strong> Please try wider distance.</p>";								

			}else{
				echo "<table>";
				while($query = mysql_fetch_array($rs)){
					$zipcode = $query[zipcode];
					$description = $query[description];

				echo "<tr>
				<td>Zip Code:</td><td>$row[zipcode]</td><td>Description:</td><td>$description</td>
				</tr>";
				}
			echo "</table>";
			}

		}

	}
}
}
}

?>

It currently isn't working.

Question... in order for this to work right, we need to correlate the data from the 1st mysql query with the 2nd query.

I didn't see this with the 2nd query.

Also, good job on the alias thing... good idea.

Right now I am more concerned about getting it working rather than making it clean... only because it is due tomorrow... I'll clean it up afterwards.

Thank you and God bless,

Johnathan

johnathanamber · October 29, 2009

This should work, right?

I've displayed the tables and the fields I will eventually use... although I am just trying to just get it working with three fields right now.

//===================================================================
//										Sort by...
//
//				Table 1:	jos_bid_auctions
//					Fields:		userid, title, shortdescription, picture, link_extern, initial_price, currency,
//								BIN_price, auction_type, automatic, payment, shipment_info,
//								shipment_price, start_date, end_date, closed_date,
//								params, published, close_offer, close_by_admin, hits, modified,
//								newmessages, winner_id, cat, auction_nr, nr_items, nr_items_left, featured,
//								reserve_price, min_increase, extended_counter, payment_info, zipcode,
//								weight
//
//				Table 2:	zip_codes
//					Fields		zip, latitude, longitude, city, state, country, type
//
//				Example:
//					SELCT field1, field2, field3, etc (all fields from all tables)
//					FROM table1, tables2, etc (from all tables being used)
//					WHERE table1.field1 = tables2.field2
//
//===================================================================

				$query = "SELECT description, zipcode, zip
					FROM jos_bid_auctions, zip_code
					WHERE jos_bid_auctions.description like '%$description%'
					AND zip_codes.latitude BETWEEN $latN AND $latS
					AND zip_codes.longitude BETWEEN $lonE AND $lonW
					AND zip_codes.latitude != $lat1 AND zip_codes.longitude != $lon1
					ORDER BY jos_bid_auctions.zipcode";

DavidAM · October 29, 2009

It currently isn't working.

Well what is it doing? Are there any error messages? Is it outputting anything?

Question... in order for this to work right, we need to correlate the data from the 1st mysql query with the 2nd query.

I didn't see this with the 2nd query.

I'm not sure I understand the question. There were two queries, but we combined them into a single query. Other than the first query to get the requested zipcode's lat and long, there is only one query in this code, and it should return the data you were trying to get with the two queries in the original post.

The code in this post looks like it should work.

The query in your LAST post will NOT work. It is not the same as the query in the post BEFORE that, which should work.

johnathanamber · October 29, 2009

David,

I am just trying to figure this out as well as make it work.

I have this thus far:

//==================================================================================================================================
//															Sort by...
//
//				Table 1:	jos_bid_auctions
//					Fields:		userid, title, shortdescription, picture, link_extern, initial_price, currency, BIN_price,
//								auction_type, automatic, payment, shipment_info, shipment_price, start_date, end_date, closed_date,
//								params, published, close_offer, close_by_admin, hits, modified, newmessages, winner_id, cat,
//								auction_nr, nr_items, nr_items_left, featured, reserve_price, min_increase, extended_counter,
//								payment_info, zipcode, weight
//
//				Table 2:	zip_codes
//					Fields		zip, latitude, longitude, city, state, country, type
//
//				Example:
//					SELCT field1, field2, field3, etc (all fields from all tables)
//					FROM table1, tables2, etc (from all tables being used)
//					WHERE table1.field1 = tables2.field2
//
//==================================================================================================================================
			if ($sortby == "default"){
				$query = "SELECT description, zipcode, zip
					FROM jos_bid_auctions, zip_code
					(latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != ''
					ORDER BY jos_bid_auctions.zipcode";
			}

If I do an echo on the query I get this:

SELECT description, zipcode, zip FROM jos_bid_auctions, zip_code (latitude <= 36.286982858078 AND latitude >= 35.997537141922 AND longitude <= -95.699526863467 AND longitude >= -96.058611136533) AND (latitude != +36.142260 AND longitude != -095.879069) AND city != '' ORDER BY jos_bid_auctions.zipcode$

johnathanamber · October 29, 2009

David,

I am just trying to figure this out as well as make it work.

I have this thus far:

//==================================================================================================================================
//															Sort by...
//
//				Table 1:	jos_bid_auctions
//					Fields:		userid, title, shortdescription, picture, link_extern, initial_price, currency, BIN_price,
//								auction_type, automatic, payment, shipment_info, shipment_price, start_date, end_date, closed_date,
//								params, published, close_offer, close_by_admin, hits, modified, newmessages, winner_id, cat,
//								auction_nr, nr_items, nr_items_left, featured, reserve_price, min_increase, extended_counter,
//								payment_info, zipcode, weight
//
//				Table 2:	zip_codes
//					Fields		zip, latitude, longitude, city, state, country, type
//
//				Example:
//					SELCT field1, field2, field3, etc (all fields from all tables)
//					FROM table1, tables2, etc (from all tables being used)
//					WHERE table1.field1 = tables2.field2
//
//==================================================================================================================================
			if ($sortby == "default"){
				$query = "SELECT description, zipcode, zip
					FROM jos_bid_auctions, zip_code
					(latitude <= $latN AND latitude >= $latS AND longitude <= $lonE AND longitude >= $lonW) AND (latitude != $lat1 AND longitude != $lon1) AND city != ''
					ORDER BY jos_bid_auctions.zipcode";
			}

If I do an echo on the query I get this:

SELECT description, zipcode, zip FROM jos_bid_auctions, zip_code (latitude <= 36.286982858078 AND latitude >= 35.997537141922 AND longitude <= -95.699526863467 AND longitude >= -96.058611136533) AND (latitude != +36.142260 AND longitude != -095.879069) AND city != '' ORDER BY jos_bid_auctions.zipcode$

With your same code I get the same thing:

SELECT A.description, A.zipcode, Z.city, Z.state FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode WHERE A.description like '%auction%' AND Z.latitude BETWEEN 36.286982858078 AND 35.997537141922 AND Z.longitude BETWEEN -95.699526863467 AND -96.058611136533 AND Z.latitude != +36.142260 AND Z.longitude != -095.879069 ORDER BY Z.state, Z.city, Z.zipcode$

DavidAM · October 29, 2009

Those two queries are NOT the same. The first one:

SELECT description, zipcode, zip FROM jos_bid_auctions, zip_code (latitude <= 36.286982858078 AND latitude >= 35.997537141922 AND longitude <= -95.699526863467 AND longitude >= -96.058611136533) AND (latitude != +36.142260 AND longitude != -095.879069) AND city != '' ORDER BY jos_bid_auctions.zipcode$

is not even a valid query. The start of the WHERE clause is missing.

Even with the WHERE clause (from your previous post) the query is not correct:

$query = "SELECT description, zipcode, zip
                  FROM jos_bid_auctions, zip_code
                  WHERE jos_bid_auctions.description like '%$description%'
                  AND zip_codes.latitude BETWEEN $latN AND $latS
                  AND zip_codes.longitude BETWEEN $lonE AND $lonW
                  AND zip_codes.latitude != $lat1 AND zip_codes.longitude != $lon1
                  ORDER BY jos_bid_auctions.zipcode";

There is no JOIN and nothing to relate the two tables, so you will get a cartesean product.

The query you echoed from "my" code

SELECT A.description, A.zipcode, Z.city, Z.state 
FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode 
WHERE A.description like '%auction%' 
AND Z.latitude BETWEEN 36.286982858078 AND 35.997537141922 
AND Z.longitude BETWEEN -95.699526863467 AND -96.058611136533 
AND Z.latitude != +36.142260 
AND Z.longitude != -095.879069 
ORDER BY Z.state, Z.city, Z.zipcode$

looks like it will return the rows you indicated you were looking for. This is NOT the same as the first one you showed in your last post. What do you get when you execute this query?

JonnoTheDev · October 29, 2009

These queries are poor for distance calculations.

SELECT A.description, A.zipcode, Z.city, Z.state
FROM jos_bid_auctions A JOIN zipcodes Z ON A.zipcode = Z.zipcode

WHERE A.description like '%auction%'

AND Z.latitude BETWEEN 36.286982858078 AND 35.997537141922

AND Z.longitude BETWEEN -95.699526863467 AND -96.058611136533

AND Z.latitude != +36.142260

AND Z.longitude != -095.879069

ORDER BY Z.state, Z.city, Z.zipcode

As mentioned use a haversine formula. Add indexes to zipcode, latitude, longitude.

<?php
// get all zipcodes within 12 mile radius
$latitude  = 36.28698;
$longitude = 95.69952;
$distance  = 12;
$query = "
SELECT a.description, a.zipcode, z.city, z.state, SQRT(POW(69.1 * (z.latitude - ".$latitude."), 2) + POW(69.1 * (".$longitude." - z.longitude) * COS(latitude / 57.3) , 2)) AS distance 
FROM jos_bid_auctions a INNER JOIN zipcodes z ON (a.zipcode=z.zipcode) HAVING distance  < ".$distance." ORDER BY distance ASC";
?>

johnathanamber · October 29, 2009

@Neil,

I don't understand what the difference is. Or would do.

@David/All,

OK I finally did get it working... and yes I did include the JOIN in the mysql $query.

<?php
//==================================================================================================================================
//													Harvest Auction Search Form
//									Created by:
//										Johnathan Morlock
//										johnathan_morlock@tsgcomputers.net
//										http://www.tsgcomputers.net
//									For:
//										HarvestAuction.com
//									Date:
//										10.28.2009
//==================================================================================================================================
?>

<html>

<head>

</head>

<body style="font-family: arial; color: black; font-size: 10px;">


<?php
//==================================================================================================================================
//														Search Form
//==================================================================================================================================
?>


<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="zipform">


<h3>Search for auctions</h4>
Use the form below to search for auctions within a ZIP radius as well as for other criteria.<br />


<fieldset>

<legend>ZIP Code Radius</legend>



<table style="font-family: arial; color: black; font-size: 10px;">

<tr>
<td>Enter your search term:</td><td><input type="text" name="term" size="20" maxlength="20" /></td>
</tr>
<tr>

    <td>Enter your ZIP Code:</td><td><input type="text" name="zip" size="5" maxlength="5" /></td>

</tr>

<tr>

    <td>Select a distance in miles from this point:</td><td><input type="text" name="distance" size="4" maxlength="4" /></td>

    </tr>

<tr>

<td>Sort Results by:</td><td><select name="sortby">

								<option value="default">default</option>

								<option value="city">City</option>

								<option value="state">State</option>

								<option value="zip">ZIP</option>

								</select></td>

</tr>

</table>

    <input type="submit" name="submit" value="Submit" />



</fieldset>



</form>



</body>

</html>

<?php

if(isset($_POST['submit'])) {

//==================================================================================================================================
//													Variables from form
//==================================================================================================================================

$term = ($_POST['term']);

$zip = ($_POST['zip']);

$distance = ($_POST['distance']);

$sortby = ($_POST['sortby']);



if(!preg_match('/^[0-9]{5}$/', $zip)) {

	echo "<p><strong>Please enter a 5 digit ZIP Code.</strong> Please try again.</p>";

}elseif(!preg_match('/^[0-9]{1,4}$/', $distance)){

	echo "<p><strong>Please enter a distance less than 9999 miles.</strong> Please try again.</p>";

}else{

//==================================================================================================================================
//													Connect to the DB
//==================================================================================================================================

include("hadb.php");


//==================================================================================================================================
//											Is the ZIP in the zip_codes table?
//==================================================================================================================================


if(!$rs = mysql_query("SELECT * FROM zip_codes WHERE zip = '$zip'")) {

	echo "<p><strong>For some reason, there was an error attempting to retrieve your ZIP Code. Please try again.</strong></p>";

	}else{

		if(mysql_num_rows($rs) == 0){

			echo "<p><strong>No database match for provided ZIP Code. Please enter a new ZIP Code.</strong></p>";	

		}else{


//==================================================================================================================================
//													Variables from DB
//==================================================================================================================================


			$row = mysql_fetch_array($rs);

			$lat1 = $row['latitude'];

			$lon1 = $row['longitude'];

			$d = "$distance";

			//earth's radius in miles

			$r = 3959;


//==================================================================================================================================
//												Compute max and min LAT and LONG 
//==================================================================================================================================


			$latN = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(0))));

			$latS = rad2deg(asin(sin(deg2rad($lat1)) * cos($d / $r) + cos(deg2rad($lat1)) * sin($d / $r) * cos(deg2rad(180))));

			$lonE = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(90)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));

			$lonW = rad2deg(deg2rad($lon1) + atan2(sin(deg2rad(270)) * sin($d / $r) * cos(deg2rad($lat1)), cos($d / $r) - sin(deg2rad($lat1)) * sin(deg2rad($latN))));


//==================================================================================================================================
//										Display Originating Information with LAT and LONG
//==================================================================================================================================


			echo "Your originating ZIP code:<table><tr><td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>ZIP</th><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zip]&description=$term' target='_blank'>$row[zip]</a></td></tr>
				<tr><th>Search Term</th><td>$term</td></tr>

				<tr><th>City</th><td>$row[city]</td></tr>

				<tr><th>State</th><td>$row[state]</td></tr>

				<tr><th>Lat</th><td>$lat1</td></tr>

				<tr><th>Lon</th><td>$lon1</td></tr>

				</table>

				</td>

				<td valign='top'>

				<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

				<tr><th>Max Lat (N)</th><td>$latN</td></tr>

				<tr><th>Min Lat (S)</th><td>$latS</td></tr>

				<tr><th>Max Lon (E)</th><td>$lonE</td></tr>

				<tr><th>Min Lon (W)</th><td>$lonW</td></tr>";

			echo "</table>

				</td></tr></table><br />";



				//find all coordinates within the search square's area

				//exclude the starting point and any empty city values
				//also get the description from the DB


//==================================================================================================================================
//															Sort by...
//
//				Table 1:	jos_bid_auctions
//					Fields:		userid, title, shortdescription, picture, link_extern, initial_price, currency, BIN_price,
//								auction_type, automatic, payment, shipment_info, shipment_price, start_date, end_date, closed_date,
//								params, published, close_offer, close_by_admin, hits, modified, newmessages, winner_id, cat,
//								auction_nr, nr_items, nr_items_left, featured, reserve_price, min_increase, extended_counter,
//								payment_info, zipcode, weight
//
//				Table 2:	zip_codes
//					Fields		zip, latitude, longitude, city, state, country, type
//
//				Example:
//					SELCT field1, field2, field3, etc (all fields from all tables)
//					FROM table1, tables2, etc (from all tables being used)
//					WHERE table1.field1 = tables2.field2
//
//==================================================================================================================================
			if ($sortby == "default"){
			$query = "SELECT  id, title, shortdescription, description, picture, BIN_price, auction_type, start_date, end_date, hits, cat, auction_nr, nr_items_left, reserve_price, zipcode, weight, zip, city, state
					FROM jos_bid_auctions JOIN zip_codes ON zipcode = zip
					WHERE description OR shortdescription like '%$term%'
					AND(latitude <= $latN
						AND latitude >= $latS
						AND longitude <= $lonE
						AND longitude >= $lonW)
					AND (latitude != $lat1
						AND longitude != $lon1)
					ORDER BY zipcode";
			}

			if(!$rs = mysql_query($query)) {

				echo "<p><strong>There was an error selecting nearby ZIP Codes from the database. Please try again.</strong></p>";

//==================================================================================================================================
//											Display The Information - OLD ZIP table
//==================================================================================================================================

			 /*else{

				//output all matches to screen

				echo "Click on the ZIP Code to view the available auctions:<table style='border: solid silver 1px; font-family: arial; color: black; font-size: 10px;' cellspacing='0' cellpadding='4'>

					<tr><th>ZIP</th>

					<th>City</th>

					<th>State</th>

					<!-- <th>Latitude</th>

					<th>Longitude</th> -->

					<th>Distance</th></tr>";

				while($row = mysql_fetch_array($rs)) {

					$truedistance = acos(sin(deg2rad($lat1)) * sin(deg2rad($row['latitude'])) + cos(deg2rad($lat1)) * cos(deg2rad($row['latitude'])) * cos(deg2rad($row['longitude']) - deg2rad($lon1))) * $r;



					if($truedistance < $d) {

						echo "<tr><td><a href='http://www.harvestauction.com/index.php?option=com_bids&task=showSearchResults&zipcode=$row[zipcode]&description=$description' target='_blank'>$row[zipcode]</a></td>

							<td>$row[city]</td>

							<td>$row[state]</td>

							<!-- <td>$row[latitude]</td>

							<td>$row[longitude]</td> -->

							<td>$truedistance</td></tr>";

					}

				}

				echo "</table><br />";

			} */

//==================================================================================================================================
//											Display The Information from Auctions
//==================================================================================================================================


			}elseif(mysql_num_rows($rs) == 0){

				echo "<p><strong>No auctions have been located within the distance specified.</strong> Please try wider distance.</p>";								

			}else{
				echo "<table>";
				while($query = mysql_fetch_array($rs)){
					$id = $query[id];
					$title = $query[title];
					$shortdescription = $query[shortdescription];
					$description = $query[description];
					$picture = $query[picture];
					$BIN_price = $query[bIN_price];
					$auction_type = $query[auction_type];
					$start_date = $query[start_date];
					$end_date = $query[end_date];
					$hits = $query[hits];
					$cat = $query[cat];
					$auction_nr = $query[auction_nr];
					$nr_items_left = $query[nr_items_left];
					$reserve_price = $query[reserve_price];
					$zipcode = $query[zipcode];
					$weight = $query[weight];
					$zipcode = $query[zipcode];
					$city = $query[city];
					$state = $query[state];

// Current Price, BIN_price

				echo "<tr>
					<td width='800px'>
					<hr />
						<b><a href='http://www.harvestauction.com/index.php?option=com_bids&task=viewbids&id=$id&Itemid=0' target='_blank'>$title - $auction_nr</a></b><br />
						<table width='100%'>
						<tr>
						<td>QTY:</td><td>$nr_items_left</td><td>Start Date:</td><td>$start_date</td>
						</tr>
						<tr>
						<td>Weight:</td><td>$weight lbs</td><td>End Date:</td><td>$end_date</td>
						</tr>
						<tr>
						<td>Location:</td><td>$city</td><td>$state</td><td>$zipcode</td>
						</tr>
						</table>
						Short Description: $shortdescription
					</td>
					</tr>";
				}
				echo "</table>";
			}

		}

	}
}
}

?>

A couple of bugs though... it doesn't display also the originating ZIP... AND for some reason each search also displays the ZIP for 74133. Even if you do a search for 83605, with a radius of 10 miles. 83605 is in Idaho. and 74133 is in Okalahoma.

Any ideas?

BTW, thank you for your help David... like I said, I am making sure I understand how the mysql is working since I've never used JOIN.

God bless,

Johnathan

Sign In

New Search Engine

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information