New Search Engine
October 15, 2009 - 10:51pm
October 17, 2009 - 9:37am
October 18, 2009 - 4:53pm
@Peter, like your avatar BTW
Thank you for the reply Peter.
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?
Thank you and God bless,
Johnathan
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 18, 2009 - 6:41pm
I figured that either way...
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
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 23, 2009 - 12:42am
Sorry for the delay in answering!
As to whether you should search on Zip or on Product; Yes. I think you should offer both. For example, I might be interested in Whole Wheat Flour in 100lb+ quantities (actually, I am... I bake a lot of bread!). We currently buy it from Wheat Montana, but we're willing to try other locations. I don't care where it is as long as shipping is still reasonable. For a bunch of carrots, I might be more interested in a local place.
I get the impression that there is a table (at least one) missing. Do you have a data model of what you are trying to develop? (you could send it offline to pete at kpdirection.com if you wish) - I'll try to find time to look at it.
(p.s, the Avatar is part of the logo I designed for Kids Faith. Thanks for the props!)
Pete.
October 24, 2009 - 1:48pm
@Peter
I am not sure what you mean by a 'data model'.
If each auction has a ZIP code and a description... and the ZIP Code search engine is in place... what I need to do is come up with some code to take the results of the ZIP Code search and search auctions via the description.
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 24, 2009 - 6:22pm
What does the Zip Code search give you?
What does the Zip Code search give you? Does it return a list of cities, for example?
If so, you might have an array such as 'New York','Washington','Philadelphia'. Hopefully the order will be closest to furthest away.
Your jos_bid_auctions table needs a link back to the ZipCode table. Let's call it auction_id, and this will be a foreign key to the field zipcode in zip_codes (assuming that your zip codes table actually just contains a list of auctions and their references).
Your SQL statement for the Auctions would then be (in pseudocode)
$sql = sprintf("select * from jos_bid_auctions j join zip_codes z where j.auction_id = z.zipcodes and j.description like '%%%s%%' and z.city in (%s)",$searchdescription,implode(",", $cityarray));
// Carry on with database stuff....
This is a rough cut without knowing the exact parameters I'm playing with, but it might help.
HTH,
Pete.
October 26, 2009 - 9:14pm
@Peter
OK here is the code for the ZipCode Radius Search engine:
<?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">
<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{
//connect to db server; select database
$link = mysql_connect('localhost', 'username', 'password') or die('Cannot connect to database server');
mysql_select_db('db') or die('Cannot select database');
//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.</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{
//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 />";
}
}
}
}
}
?>The ZIPCodes table is separate from the jos_bids_auctions table... but they are both within the same DB.
I think what I need is a ZIPCODE field within each auction to better sort this out.
So when I return the list of ZIPs from a radius, I can correlate each returned ZIP with that auction.
I've already start to work on that. As you can see with the above.
That is evident via the link that is generated for each ZIP that gets returned.
I need to display the results and not just the links however.
BTW, What does 'HTH' mean?
God bless and thank you for your help,
Johnathan
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 26, 2009 - 3:16pm
Edit your code
You've just displayed in a public forum the details required to connect to your database; you should remove that section of code from public view, and change your database details immediately...
Also, your select statements return *, instead of a list of columns. That's not incorrect in itself (but inefficient for large result sets), however it makes it harder to see what information is being returned from the select statement.
HTH = Hope This Helps.
You need to think more abstractly before you hack the code. How is an Auction linked to a City? How is an Auction Item linked to an Auction? Can the same item appear in more than one auction? Can a City have more than one Auction going? (this is all handled by data models - http://en.wikipedia.org/wiki/Entity-relationship_m...).
October 26, 2009 - 9:34pm
Ouch
I guess that was dumb wasn't it... I knew better than that... it is OK, I've changed the password.
While I was waiting for your answer... I've attached a ZIPCODE field to each auction. So, now I need to figure out how to get the results from the ZIP Radius search engine and correlate that with the search term and display the results.
Check this page out:
http://www.harvestauction.com/index.php?option=com...
It is my Zip Code Search Engine... but you search for a term as well. Like 'auction' with a zipcode of 83605.
But it displayed each ZIP code individually and uses that term and makes a link for each code.
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 27, 2009 - 2:05pm
October 27, 2009 - 5:29pm
No problem
It gives a table with a list of ZIP codes and how far they are from the originating zip code.
If you click on the lin in the last posting from me, you will go to the search engine.
Enter the ZIP code :74012 with a radius of 10 miles.
74012 has auctions under it.
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 28, 2009 - 11:28am
OK, I need some help with this...
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
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 28, 2009 - 9:28pm
<?php
<?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>";
}
}
}
}
}
}
?>Updated code.
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 29, 2009 - 10:04am
OK I finally did get it
OK I finally did get it working...
<?php
//==================================================================================================================================
// Harvest Auction Search Form
// Created by:
// Johnathan Morlock
// johnathan_morlock@tsgcomputers.net
// <a href="http://www.tsgcomputers.net<br />
//" title="http://www.tsgcomputers.net<br />
//" rel="nofollow">http://www.tsgcomputers.net<br />
//</a> 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?
God bless,
Johnathan
Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
October 29, 2009 - 6:41pm
October 30, 2009 - 12:27am
@Peter
Hey man, thanks a ton for the help.
I got it working... want to see the finished job?
OK, ok, maybe it isn't totally finished... but it is functional.
<?php
//==================================================================================================================================
// Harvest Auction Search Form
// Created by:
// Johnathan Morlock
// johnathan_morlock@tsgcomputers.net
// <a href="http://www.tsgcomputers.net<br />
//" title="http://www.tsgcomputers.net<br />
//" rel="nofollow">http://www.tsgcomputers.net<br />
//</a> 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>
</table>";
echo "</td></tr></table><br />";
/*
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
//
// AND (latitude != $lat1
// AND longitude != $lon1)
//
//==================================================================================================================================
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)
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 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>";
}
}
}
}
}
?>Do you want to learn more about the Christian Worldview or need some prayer or other support? Go to:
Hey everyone,
God bless you all and your families!
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.
Thank you and God bless,
Johnathan