troubleshooting Question

Error Message " Unknown column '00Zip' in 'where clause' "

Avatar of nnrsc
nnrscFlag for United States of America asked on
PHPMySQL Server
3 Comments1 Solution737 ViewsLast Modified:
I have a  form which passes on zip code, along with other input, to return businesses within a certain radius of the given zip code. The query works well on some zip codes, but not always. For example, when I put in 95050 or 99919, the results page return records properly. When I put in 44061 or 00501, I get an error message:

Unknown column '00Zip' in 'where clause'

I checked with my zip code table, 44061 and 00501 both are valid zip codes in my table and have the respective lon and lat.

Below is query page and the zip code function page. Appreicate any ideas on the causes and solutions
code for r101.php
<?php
ini_set("memory_limit","256M");
/*  
   DEMO for using the zipcode PHP class. By: Micah Carrick 
   Questions?  Comments?  Suggestions?  email@micahcarrick.com
*/
require_once('zipcode.php');      // zip code class
require_once('highlight.php');
 
// Open up a connection to the database.  The sql required to create the MySQL
// tables and populate them with the data is in the /sql subfolder.  You can
// upload those sql files using phpMyAdmin or a MySQL prompt.  You will have to
// modify the below information to your database information.  
include("config.php");
mysql_query("SET character_set_results='utf8'");
$searchterm=mysql_real_escape_string($_REQUEST['searchterm']);
$Radius = trim($_REQUEST['Radius']);
$Destination = trim($_REQUEST['Destination']);
 
 
$second_search=mysql_real_escape_string($_POST['second_search']);
if($second_search){
$search_term = $second_search;
$searchterm = $search_term;
$Radius = trim($_REQUEST['Radius']);
$Destination = trim($_REQUEST['Destination']);
}else{
$search_term = mysql_real_escape_string($_POST['searchterm']);
$Radius = trim($_REQUEST['Radius']);
$Destination = trim($_REQUEST['Destination']);
}
 
$keywords =explode(" ",$searchterm);
$colors[0]='#00CCFF';
$html_text=$searchterm;
 
if (isset($_REQUEST['pageno'])) {
   $pageno = $_REQUEST['pageno'];
} else {
   $pageno = 1;
} // if
 
if( isset($_POST['Destination']) && !preg_match("/^[0-9]{5}$/", $_POST['Destination'] )) {
echo  "<p><font size=\"3\" face=\"Verdana, Arial\" color=\"#FF0000\"><b>Please go back and enter a valid 5-digit US Zip Code</b></font></p>";
}
 
$z = new zipcode_class;
 
$zips = $z->get_zips_in_range($Destination, $Radius, _ZIPS_SORT_BY_DISTANCE_ASC, true); 
$zipcodes = array_keys($zips); // this fetches all the zip codes in the $zips variable 
$zip1 = implode(",",$zipcodes);  
 
$sql5 = "select count(*) from Suppliers, test where Suppliers.Zip in (".$zip1.") AND MATCH (test.Description,test.Primary_Category,test.Secondary_Category,test.Tertiary_Category,test.Quaternary_Category) AGAINST ('$searchterm') AND test.SID = Suppliers.SID"; 
$result5 = mysql_query($sql5) or die (mysql_error());  
$query_data = mysql_fetch_row($result5);
$numrows = $query_data[0];
 
 
$rows_per_page = 15;
$lastpage      = ceil($numrows/$rows_per_page);
$startrow = ($pageno - 1)*$rows_per_page;
$startrow2 = $startrow + 1;
$startrow3 = $startrow + $rows_per_page;
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
 
// Below is an example of how to calculate the distance between two zip codes.
 
$z = new zipcode_class;
 
$zips = $z->get_zips_in_range($Destination, $Radius, _ZIPS_SORT_BY_DISTANCE_ASC, true); 
$zipcodes = array_keys($zips); // this fetches all the zip codes in the $zips variable 
$zip1 = implode(",",$zipcodes);  
 
 
        $sql = "select * from Suppliers, test where Suppliers.Zip in (".$zip1.") AND MATCH (test.Description,test.Primary_Category,test.Secondary_Category,test.Tertiary_Category,test.Quaternary_Category) AGAINST ('$searchterm') AND test.SID = Suppliers.SID $limit"; 
        $result = mysql_query($sql) or die (mysql_error());  
          $num_rows = mysql_num_rows($result);  
 
 
$row = mysql_fetch_assoc($result);
@mysql_data_seek($result,0);
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&searchterm=$searchterm&Radius=$Radius&Destination=$Destination'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&searchterm=$searchterm&Radius=$Radius&Destination=$Destination'>PREV</a> ";
} // if
 
echo " ( Page $pageno of $lastpage, ";
echo " Records $startrow2 to ";
echo min($startrow3, $numrows);
echo " of $numrows) ";
if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&searchterm=$searchterm&Radius=$Radius&Destination=$Destination'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&&searchterm=$searchterm&Radius=$Radius&Destination=$Destination'>LAST</a> ";
} // if
            
          if($numrows == 0) {  
               echo "Sorry. No records found. Please refine your creteria by including more details. If problem persists, please contact us.";  
          }  
          else {  
 
echo "<table border='0'>
<tr>
<th></th>
<th></th>
<th></th>
</tr>";
 
echo "<td align='left'><form name=form2 action='r101.php' method='POST'>
Search&nbsp;<input type='text' name='second_search' id='second_search' value='$searchterm' class='input'>&nbsp;within&nbsp;";
echo "<select name='Radius' id='Radius' class='input'>
        <option value='50'>50</option>
        <option value='100'>100</option>
        <option value='150'>150</option>
        <option value='200'>200</option>
        <option value='250'>250</option>
        <option value='300'>300</option>
        <option value='7000' selected='selected'>any</option>
      </select>"; 
echo "&nbspmiles from Zip Code (5-digit)&nbsp;";
echo "<input name='Destination' type='text' id='Destination' size='10' class='input' />&nbsp;&nbsp";
echo "<input type='submit'  value='Find'>";
echo "</form><td>";
 
echo "<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>";
 
echo "<td aligh='right'><form action='r200.php' method='POST'>
<input type='hidden' name='second_search' id='second_search' value='$searchterm'>
<input type='submit' class='button' value='Visit the Knowledge Center for tips on selecting right $searchterm '>
</form></td></table>";
 
echo "<table border='1'>
<tr>
<th>Product Photo</th>
<th>Product Information</th>
<th>Supplier Information</th>
<th>Contact Information</th>
</tr>";
 
 
while($myrow = mysql_fetch_array($result))
  {
  $zipcode = $myrow['Zip'];  
  $miles = $zips[$zipcode]; // return the value for key with the zip code in it. 
   
  echo "<tr>";
  echo "<td><a onclick=\"DescriptionWin(this); return false;\" href=\"" . $myrow["Photo_URL"] . "\"> <img src=\"" . $myrow["Photo_URL"] . "\" border=0 height=150 width=150 alt=\"" . $myrow["Photo_URL"] . "\"></a></td>";
  echo "<td><br /><b>Brief Description: </b>".highlight_search($keywords,'<span>'.$myrow['Description'].'</span>',$colors)."<br />";
  echo "<br /><b>Supplier Product Code: </b>".$myrow['Supplier_Product_Code']."<br />";
  echo "<br />"."<a onclick=\"DescriptionWin(this); return false;\" href=\"" .$myrow['Page_URL']. "\">" . "<b>Click here for more specifications</b>" . "</a>"."<br />"."</td>";
  echo "<td><br /><b>Supplier Name: </b>" . $myrow['Supplier_Name']."<br />";
  echo "<br /><b>Address: </b>".$myrow['Address']."<br />"; 
  echo "<br /><b>Distance: </b>".$miles ." miles"."<br />". "</td>";   
  echo "<td><b><br /> Tollfree: </b>".$myrow['Tollfree']."<br />";
  echo "<br /><b>Local: </b>".$myrow['Phone']."<br />";
  echo "<br /><b>Fax: </b>".$myrow['Fax']."<br />";
  echo "<br /><b>E-mail: </b>"."<a href=mailto:" . $myrow['Email'] . ">" . $myrow['Email']."<br />"."</td>";
  echo "</tr>";  
 }
 
echo "</table>";
}  
 
?>
 
Code for zipcode.php
 
<?php
/*******************************************************************************
 *                ZIP Code and Distance Claculation Class
 *******************************************************************************
 *      Author:     Micah Carrick
 *      Email:      email@micahcarrick.com
 *      Website:    http://www.micahcarrick.com
 *
 *      File:       zipcode.class.php
 *      Version:    1.2.0
 *      Copyright:  (c) 2005 - Micah Carrick 
 *                  You are free to use, distribute, and modify this software 
 *                  under the terms of the GNU General Public License.  See the
 *                  included license.txt file.
 *
 *******************************************************************************
 *  VERION HISTORY:
 *      v1.2.0 [Oct 22, 2006] - Using a completely new database based on user
                                contributions which resolves many data bugs.
                              - Added sorting to get_zips_in_range()
                              - Added ability to include/exclude the base zip
                                from get_zips_in_range()
                              
 *      v1.1.0 [Apr 30, 2005] - Added Jeff Bearer's code to make it MUCH faster!
 
 *      v1.0.1 [Apr 22, 2005] - Fixed a typo :)
 
 *      v1.0.0 [Apr 12, 2005] - Initial Version
 *
 *******************************************************************************
 *  DESCRIPTION:
 
 *    A PHP Class and MySQL table to find the distance between zip codes and 
 *    find all zip codes within a given mileage or kilometer range.
 *      
 *******************************************************************************
*/
 
// constants for setting the $units data member
define('_UNIT_MILES', 'm');
define('_UNIT_KILOMETERS', 'k');
 
// constants for passing $sort to get_zips_in_range()
define('_ZIPS_SORT_BY_DISTANCE_ASC', 1);
define('_ZIPS_SORT_BY_DISTANCE_DESC', 2);
define('_ZIPS_SORT_BY_ZIP_ASC', 3);
define('_ZIPS_SORT_BY_ZIP_DESC', 4);
 
// constant for miles to kilometers conversion
define('_M2KM_FACTOR', 1.609344);
 
class zipcode_class {
 
   var $last_error = "";            // last error message set by this class
   var $last_time = 0;              // last function execution time (debug info)
   var $units = _UNIT_MILES;        // miles or kilometers
   var $decimals = 2;               // decimal places for returned distance
 
   function get_distance($zip1, $zip2) {
 
      // returns the distance between to zip codes.  If there is an error, the 
      // function will return false and set the $last_error variable.
      
      $this->chronometer();         // start the clock
      
      if ($zip1 == $zip2) return 0; // same zip code means 0 miles between. :)
   
   
      // get details from database about each zip and exit if there is an error
      
      $details1 = $this->get_zip_point($zip1);
      $details2 = $this->get_zip_point($zip2);
      if ($details1 == false) {
         $this->last_error = "No details found for zip code: $zip1";
         return false;
      }
      if ($details2 == false) {
         $this->last_error = "No details found for zip code: $zip2";
         return false;
      }     
 
 
      // calculate the distance between the two points based on the lattitude
      // and longitude pulled out of the database.
      
      $miles = $this->calculate_mileage($details1[0], $details2[0], $details1[1], $details2[1]);
      
      $this->last_time = $this->chronometer();
 
      if ($this->units == _UNIT_KILOMETERS) return round($miles * _M2KM_FACTOR, $this->decimals);
      else return round($miles, $this->decimals);       // must be miles
      
   }   
 
   function get_zip_details($zip) {
      
      // This function pulls the details from the database for a 
      // given zip code.
 
      $sql = "SELECT lat AS lattitude, lon AS longitude, city, county, state_prefix, 
              state_name, area_code, time_zone
              FROM zip_code 
              WHERE zip_code='$zip'";
              
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      } else {
         $row = mysql_fetch_array($r, MYSQL_ASSOC);
         mysql_free_result($r);
         return $row;       
      }
   }
 
   function get_zip_point($zip) {
   
      // This function pulls just the lattitude and longitude from the
      // database for a given zip code.
      
      $sql = "SELECT lat, lon from zip_code WHERE zip_code='$zip'";
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      } else {
         $row = mysql_fetch_array($r);
         mysql_free_result($r);
         return $row;       
      }      
   }
 
   function calculate_mileage($lat1, $lat2, $lon1, $lon2) {
 
      // used internally, this function actually performs that calculation to
      // determine the mileage between 2 points defined by lattitude and
      // longitude coordinates.  This calculation is based on the code found
      // at http://www.cryptnet.net/fsp/zipdy/
       
      // Convert lattitude/longitude (degrees) to radians for calculations
      $lat1 = deg2rad($lat1);
      $lon1 = deg2rad($lon1);
      $lat2 = deg2rad($lat2);
      $lon2 = deg2rad($lon2);
      
      // Find the deltas
      $delta_lat = $lat2 - $lat1;
      $delta_lon = $lon2 - $lon1;
	
      // Find the Great Circle distance 
      $temp = pow(sin($delta_lat/2.0),2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0),2);
      $distance = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));
 
      return $distance;
   }
   
   function get_zips_in_range($zip, $range, $sort=1, $include_base) {
       
      // returns an array of the zip codes within $range of $zip. Returns
      // an array with keys as zip codes and values as the distance from 
      // the zipcode defined in $zip.
      
      $this->chronometer();                     // start the clock
      
      $details = $this->get_zip_point($zip);  // base zip details
      if ($details == false) return false;
      
      // This portion of the routine  calculates the minimum and maximum lat and
      // long within a given range.  This portion of the code was written
      // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
      // the time it takes to execute a query.  My demo took 3.2 seconds in 
      // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!
      
      // Find Max - Min Lat / Long for Radius and zero point and query
      // only zips in that range.
      $lat_range = $range/69.172;
      $lon_range = abs($range/(cos($details[0]) * 69.172));
      $min_lat = number_format($details[0] - $lat_range, "4", ".", "");
      $max_lat = number_format($details[0] + $lat_range, "4", ".", "");
      $min_lon = number_format($details[1] - $lon_range, "4", ".", "");
      $max_lon = number_format($details[1] + $lon_range, "4", ".", "");
 
      $return = array();    // declared here for scope
 
      $sql = "SELECT Zip, lat, lon FROM Suppliers ";
      if (!$include_base) $sql .= "WHERE Zip <> '$zip' AND ";
      else $sql .= "WHERE "; 
      $sql .= "lat BETWEEN '$min_lat' AND '$max_lat' 
               AND lon BETWEEN '$min_lon' AND '$max_lon'";
             
      $r = mysql_query($sql);
      
      if (!$r) {    // sql error
 
         $this->last_error = mysql_error();
         return false;
         
      } else {
          
         while ($row = mysql_fetch_row($r)) {
   
            // loop through all 40 some thousand zip codes and determine whether
            // or not it's within the specified range.
            
            $dist = $this->calculate_mileage($details[0],$row[1],$details[1],$row[2]);
            if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
            if ($dist <= $range) {
               $return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);
            }
         }
         mysql_free_result($r);
      }
      
      // sort array
      switch($sort)
      {
         case _ZIPS_SORT_BY_DISTANCE_ASC:
            asort($return);
            break;
            
         case _ZIPS_SORT_BY_DISTANCE_DESC:
            arsort($return);
            break;
            
         case _ZIPS_SORT_BY_ZIP_ASC:
            ksort($return);
            break;
            
         case _ZIPS_SORT_BY_ZIP_DESC:
            krsort($return);
            break; 
      }
      
      $this->last_time = $this->chronometer();
      
      if (empty($return)) return false;
      return $return;
   }
 
   function chronometer()  {
 
   // chronometer function taken from the php manual.  This is used primarily
   // for debugging and anlyzing the functions while developing this class.  
  
   $now = microtime(TRUE);  // float, in _seconds_
   $now = $now + time();
   $malt = 1;
   $round = 7;
  
   if ($this->last_time > 0) {
       /* Stop the chronometer : return the amount of time since it was started,
       in ms with a precision of 3 decimal places, and reset the start time.
       We could factor the multiplication by 1000 (which converts seconds
       into milliseconds) to save memory, but considering that floats can
       reach e+308 but only carry 14 decimals, this is certainly more precise */
      
       $retElapsed = round($now * $malt - $this->last_time * $malt, $round);
      
       $this->last_time = $now;
      
       return $retElapsed;
   } else {
       // Start the chronometer : save the starting time
    
       $this->last_time = $now;
      
       return 0;
   }
}
 
}
ASKER CERTIFIED SOLUTION
AndreeaN

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros