MySQL Select from array

[luiddog]

I have two DBs, one is of dealerships and one is a zip code DB.

I'm using ZipCodesRange.class from phpclasses.org

Example:
include("ZipCodesRange.class.php");

//initialization, pass in DB connection, from zip code, distance in miles.
$zip = new ZipCodesRange($db,'98303',50);

//do the work
$zip->setZipCodesInRange(); //call to initialize zip array

//zip code output, other processing can be done from this array.
$zipArray = $zip->getZipCodesInRange();

It returns an array of zip codes and distances ($zipArray, $zipArray[0] = Zip, $zipArray[1] = Distance).

What I'm trying to do is SELECT dealerships from the DB of dealership within the returned zip codes. But how do I SELECT from an array?

Ideally, I would want to select dealerships from a given radius of a zip code and show the distance without using lat/lon.

Any ideas?

[HalfaBee]

You need to create a string (use foreach() ) to use in an IN clause in the query, so it looks like this

SELECT * from dealerships where zip IN ( 123456 , 456789 )

[troybtj]

Once you have the ZipArray of those in range:

$zips=explode(',', $ZipArray);
$query=SELECT * from ZIPDB where ZIP IN ($zips);

[luiddog]

This is where I started from:

include("ZipCodesRange.class.php");

//initialization, pass in DB connection, from zip code, distance in miles.
$zip = new ZipCodesRange($db,'98303',50);

//do the work
$zip->setZipCodesInRange(); //call to initialize zip array

//zip code output, other processing can be done from this array.
$zipArray = $zip->getZipCodesInRange();

And I found this:

function format_sql_array($array)
{
    $SQLstring = "";
    foreach($array as $item)
      {
        $SQLstring .= "'$item',";
      }
    $SQLstring = rtrim($SQLstring, ",");
    $SQLstring = str_replace("'',", "", $SQLstring);
    return $SQLstring;
}

With...

$my_array = $zipArray;
$sql_array = format_sql_array($my_array);
$query_Dealers = "SELECT * FROM Dealers WHERE Zip IN($sql_array)";

Obviously, this isn't the exact SELECT code but you get the idea.

The problem I'm having now it print_r($zipArray) results in:
Array ( [98002] => 26.04 [98004] => 39.45 [98005] => 40.52 [98006] ......

I've tried:

$my_array = $zipArray[0]; //or $my_array = $zipArray[1];
$sql_array = format_sql_array($my_array);
$query_Dealers = "SELECT * FROM Dealers WHERE Zip IN($sql_array)";

And nothing. It's a bit of a newbie question but I've tried numerous ways of making this work.

[HalfaBee]

$sql_array = explode(',', array_keys($ZipArray) );

should work.

[luiddog]

I replaced:
$my_array = $zipArray;
$sql_array = format_sql_array($my_array);

With
$sql_array = explode(',', array_keys($ZipArray) );

and get:
Warning: array_keys() [function.array-keys]: The first argument should be an array in

I'm thinking that the explode on "," is the error because the array is:
Array ( [98002] => 26.04 [98004] => 39.45 [98005] => 40.52 [98006] ......
Without any ","

Thanks for the idea?

[HalfaBee]

That will teach me to cut&paste

replace
$my_array = $zipArray;
$sql_array = format_sql_array($my_array);
with
$sql_array = implode( ',' , array_keys($ZipArray) );

[luiddog]

Still can't get it to work. Getting the following:

Warning: array_keys() [function.array-keys]: The first argument should be an array in /home/***.php on line 69

Warning: implode() [function.implode]: Bad arguments. in /home/**.php on line 69
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 ')' at line 1

[luiddog]

Using:

function format_sql_array($array)
{
$SQLstring = "";
foreach($array as $item)
{
$SQLstring .= "'$item',";
}
$SQLstring = rtrim($SQLstring, ",");
$SQLstring = str_replace("'',", "", $SQLstring);
return $SQLstring;
}
$sql_array = implode( ',' , array_keys($ZipArray) );

[HalfaBee]

ZipArray should be zipArray

[luiddog]

Works great. Thanks. Any ideas on how to show the distance? Because zipArray has the distance.

[HalfaBee]

When you fetch the row of data, use the zip code returned to supply the key for the distance.

$row = mysql_fetch_assoc( $result );

$distance = $zipArray[ $row['zip'] ];