Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts

    mysql_fetch_assoc(): ERROR

    I am getting this error, can't figure out why! Please help!

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
    I am modifing some code for a zip code search and just trying to learn what everything is doing.

    The only changes I have made is inputting the connection to my database and changing the table it is selecting from.

    PHP Code:
    <?php

    require_once('zipcode.class.php');

             include(
    'library/login.php');
             
    login();

             
    mysql_select_db('test');
             
    $z = new zipcode_class;
    $zips $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASCtrue);

    if (
    $zips === false) {
        echo 
    'Error: '.$z->last_error;
    } else {
        
    $zips_in_range implode(','array_keys($zips) );
    }

    $result mysql_query("SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")");

    while (
    $row mysql_fetch_assoc($result) ) {

        echo 
    " $row ";
    }

    ?>
    This is the zipcode.class.php file I have made no changes in here

    PHP Code:
    <?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($rMYSQL_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 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_code, lat, lon FROM zip_code ";
          if (!
    $include_base$sql .= "WHERE zip_code <> '$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;
       }
    }

    }

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    It means that
    PHP Code:
    "SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")" 
    is invalid obviously.

    Test what $zips_in_range is set to... its the only issue I can see. Can you show us an exmple? Try to echo the whole query above and show us?

  • #3
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    Besides Keleth's advice, always test return values. The quick and sloppy way to find out what's happening is to do something like:
    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")") or die(mysql_error()); 
    That's horrible for a user experience, though, so add in error checking. The documentation for mysql_query lists possible return values.
    Are you a Help Vampire?

  • #4
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    If I echo the whole query I get a blank page

    If I echo the $zips_in_range I get a serious of zip codes in the range
    ie. I used 16921 and got 16920 & 16922 back

    I don't fully understand the
    IN (". $zips_in_range . ")
    section. is that suppose to pull all 3 of the zips?

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Wait... are you echoing the query in the function or the query string? I'm interested in seeing the string.

    What IN (stuff) does is it tests to see if any of those values are in the column in question. Its that instead of doing:
    Code:
    col1 = val1 OR col1 = val2 OR col1 = val3 OR col1 = val4...
    But again, can you show us what you get back? Its much more useful then simply telling us what you see.

  • #6
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    I get this

    Array
    with this

    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")")or die(mysql_error());

    while (
    $row mysql_fetch_assoc($result) ) {

        echo 
    " $row ";


    Is that what you are looking for?

    Not really sure what you mean by
    Try to echo the whole query above and show us?
    Last edited by harkly; 07-01-2010 at 07:40 PM.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    No... though I neglected to read all your code carefully... have you tested at which query the assoc is failing?

  • #8
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Am I not just doing 1 query?

    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")")or die(mysql_error());

    while (
    $row mysql_fetch_assoc($result) ) { 
    Or are they considered 2?


    This may not help but now when I do the search I get
    ARRAY
    If I refresh the page I get

    If I refresh the page by putting mouse in http box I get this
    Code:
    Error: 
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/i/d/2/id2294/html/singles/results.php on line 22
    The "Error" is suppose to print when $zips = false so I think the page is refreshing and sending 0 as zip.

    It probably just confuses the sitituation.


    If I change the query to =

    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE zip_code = $zips_in_range ")or die(mysql_error());

    while (
    $row mysql_fetch_assoc($result) ) {

        echo 
    " $row ";

    This is the error
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16942,16940,16935,16928,14898,14801,16929,14858' at line 1
    Is it correct to assume the '16942,16940,16935,16928,14898,14801,16929,14858' is the $zips_in_range ??
    Last edited by harkly; 07-01-2010 at 07:59 PM.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Well, in the get_zips_in_range function, you have a query as well...

    So try echoing "SELECT * FROM user WHERE zip_code IN (". $zips_in_range . ")" and tell us what you get. Because unless you're doing a comparison (ie, the zip entry is actually a delimited list), it won't work the second way. And yes, that list seems to be the zips in range, but you're the programmer, you should know

  • #10
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    I get

    PHP Code:
    ARRAY 
    Not sure if I am doing it right but both queries gives me that

    PHP Code:
    $result mysql_query("SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")")or die(mysql_error());
    $r mysql_fetch_array($result);
    echo 
    $r
    PHP Code:
    $zips $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASCtrue);
    echo 
    "$zips"
    Not much of a programmer here!

    What I don't understand is that if I echo the $zips_in_range I get a list of valid zip codes.

  • #11
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I keep sayin, just echo the string, not the query

    PHP Code:
    echo "SELECT * FROM user WHERE zip_code IN ("$zips_in_range ")"
    I guess it also depends on how your database is setup, but I think that's secondary.

  • #12
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    Sorry about that! Actually had no idea you could do that!

    SELECT * FROM user WHERE zip_code IN (16921,16922)
    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/i/d/2/id2294/html/singles/results.php on line 23

  • #13
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Its a string, no reason you can't echo it.

    Yah, the query looks fine... what is your database structure for that table?

  • #14
    Regular Coder
    Join Date
    Jun 2010
    Location
    Earth
    Posts
    305
    Thanks
    27
    Thanked 2 Times in 2 Posts
    CREATE TABLE user (userID VARCHAR(32) NOT NULL PRIMARY KEY,
    pswd VARCHAR(32),
    email VARCHAR(50),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    street VARCHAR(20),
    city VARCHAR(35),
    state VARCHAR(2),
    zip_code VARCHAR(5),
    signup_date TIMESTAMP);

  • #15
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,542
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Any reason you used VARCHAR(5) instead of MEDINT? This is where my mysql knowledge ends, but I suspect its failing because you're trying to find numbers in strings. Or you could put quotes around the individual zip codes.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •