View Full Version : mysql_fetch_assoc(): ERROR

07-01-2010, 07:49 PM
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.





$z = new zipcode_class;
$zips = $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASC, true);

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


* 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.
* 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

* 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_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);
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);
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_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);

// sort array




$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;


07-01-2010, 08:05 PM
It means that

"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?

07-01-2010, 08:18 PM
Besides Keleth's advice, always test return values. The quick and sloppy way to find out what's happening is to do something like:

$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 (http://php.net/mysql_query) lists possible return values.

07-01-2010, 08:24 PM
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?

07-01-2010, 08:31 PM
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:

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.

07-01-2010, 08:37 PM
I get this


with this

$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?

07-01-2010, 08:42 PM
No... though I neglected to read all your code carefully... have you tested at which query the assoc is failing?

07-01-2010, 08:55 PM
Am I not just doing 1 query?

$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

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 =

$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 ??

07-01-2010, 09:19 PM
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 :p

07-01-2010, 09:39 PM
I get


Not sure if I am doing it right but both queries gives me that

$result = mysql_query("SELECT * FROM user WHERE zip_code IN (". $zips_in_range . ")")or die(mysql_error());
$r = mysql_fetch_array($result);
echo $r;

$zips = $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASC, true);
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.

07-01-2010, 09:44 PM
I keep sayin, just echo the string, not the query

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.

07-01-2010, 09:48 PM
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

07-01-2010, 09:51 PM
Its a string, no reason you can't echo it.

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

07-01-2010, 09:53 PM
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);

07-01-2010, 09:57 PM
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.

07-01-2010, 09:58 PM
I did have it as int but the tutorial set theirs up as the varchar. I will switch and see if that makes a difference

07-01-2010, 10:11 PM
same results



07-01-2010, 10:14 PM
Did you try doing print_r($row) like I said, instead of echoing it?

07-01-2010, 10:30 PM
with that i get

Array ( [userID] => test6 [pswd] => 111111 [email] => testeye@yahoo.com [first_name] => tes [last_name] => tse [gender] => 2 [genderPref] => 2 [bd_day] => 25 [bd_month] => 1 [bd_year] => 1971 [birth_date] => 1971-01-25 [street] => asdf [city] => Beverly Hills [state] => CA [zip_code] => 90210 [exp_date] => [billdays] => [sec_question] => 0 [sec_answ] => [signup_date] => 2010-07-01 11:04:02 )

07-01-2010, 10:47 PM
So now you're getting your results... whats the issue?

07-01-2010, 10:49 PM
echoing an array will show "Array". To see contents, use print_r, var_dump, var_export, etc.

07-01-2010, 10:52 PM
Oh, and the page will dump out with an error because of these POST variables:

$zips = $z->get_zips_in_range($_POST['zip_code'], $_POST['miles'], _ZIPS_SORT_BY_DISTANCE_ASC, true);
It requires that it gets form data rather than be accessed directly. Proper error-checking would test for those posted values and handle the condition where they're not present.

07-01-2010, 10:57 PM
ok thanks for the help!

Off to see if I can turn it into something useful.