MySQL search from an array

Question

So first I do this:

$zip_code_array = mysql_query("SELECT * FROM zip_code WHERE (lon BETWEEN '$lng_min_rnd'   AND '$lng_max_rnd') AND (lat BETWEEN '$lat_min_rnd' and '$lat_max_rnd')") or die (mysql_error());
while($zip_code_cells = mysql_fetch_array($zip_code_array))
{
$zip_codes_raw = $zip_code_cells['zip_code'];
$zip_codes_in_distance .= $zip_codes_raw.", ";
}

This works and spits out the zipcodes like this EX: 07110, 07111, 07112 etc: But then I do this:

$user_list_array = mysql_query("SELECT * FROM member_search WHERE IN($zip_codes_in_distance) AND gender = '$gender_of_interest_session'");
while($user_list = mysql_fetch_array($user_list_array))
{
$id_from_array = $user_list['id'];
$username_from_array = $user_list['user_name'];
$defaultpic_from_array = $user_list['defaultpic'];
$city_from_array = $user_list['city'];

}

I get Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on

$user_list_array = mysql_query("SELECT * FROM member_search WHERE IN($zip_codes_in_distance) AND gender = '$gender_of_interest_session'");

I can't seem to figure out the proper way to query the list of zipcodes it comes out with. I do believe it has something to do with when I turn the zip code result into a string and have all of the zip codes seperated by a comma.

Shiplu Mokaddim · Accepted Answer · 2012-01-22 13:46:44Z

2

Your sql statement has errors. $zip_codes_in_distance has an ending , which is causing failure.

It should be like this.

$zips = array();
$zip_code_array = mysql_query("SELECT * FROM zip_code WHERE (lon BETWEEN '$lng_min_rnd'   AND '$lng_max_rnd') AND (lat BETWEEN '$lat_min_rnd' and '$lat_max_rnd')") or die (mysql_error());
while($zip_code_cells = mysql_fetch_array($zip_code_array))
{
    $zip_codes_raw = $zip_code_cells['zip_code'];
    $zips[]= $zip_codes_raw;
}

$zip_codes_in_distance = "'". implode("','" , $zips). "'";

answered Jan 22, 2012 at 13:46

Shiplu Mokaddim

57.8k20 gold badges147 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wouter J · Accepted Answer · 2012-01-22 13:31:13Z

First of all, check if the query is succeed:

<?php
$result = mysql_query("SELECT something FROM foo WHERE bar = 'foo'");

if( $result === false )
{ # Query not a succes :(
  echo 'We cannot execute the query, the error: '.mysql_error(); // Remove mysql_error() when you put the site online
}
else
{ # the query was a succes
  if( mysql_num_rows() <= 0 )
  { # If there are 0 or less rows
    echo 'We cannot find a result';
  }
  else
  { # You get 1 or more rows
    // Use fetch_assoc, because this is faster then fetch_array and you don't need a fetch_array
    while( $row = mysql_fetch_assoc($result) )
    {
      echo $row['something'];
    }
  }
}

Some resources:

user1053263 · Accepted Answer · 2012-01-22 22:13:14Z

0

Fixed it: Instead of splitting the array like this:

$zip_codes_in_distance .= $zip_codes_raw.", ";

I did it like this:

$zip_codes_in_distance .= "'".$zip_codes_raw."', ";

and THEN for the MySQL IN operator i did it like THIS:

IN($zip_codes_in_distance '0')

I added the zero with no , (comma) because the array adds a comma at the end of each zip code. When it did that, it left it with an extra comma at the end of the string creating a SQL error. the 0 just puts something at the end of the string and has no bearing on the search results.

answered Jan 22, 2012 at 22:13

user1053263

7422 gold badges16 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQL search from an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related