check if query exists in an array.

[grege]

Hey Guys,

I need some assistance, hopefully there is just a simple command that I am not aware of or an easy answer to this.

 

Suppose I have an array with x amount of values...

$arr = array("111111" => 10, "222222" => 20);

And i have a query..

mysql_query("SELECT * FROM table WHERE table.zip = "111111" ");

 

Suppose I want to modify the query, so it can check if table.zip exists anywhere in $arr, regardless of length of array. And Select the value that the array location refers to. So I need it to check if "111111" is anywhere in the array, and then take the value 10.

 

The example I am applying this to is that I have a database full of business addresses/zip codes and a database full of every zip code in country. So I have a script to get every zip within x miles, and the distance from it, but need to be able to apply it when I query my business addresses to see if it exists within x miles.

 

Thanks!

Greg

[JonnoTheDev]

<?php
$arr = array('111111' => 10, '222222' => 20);

$checkValue = '111111';
if(array_key_exists($checkValue, $arr)) {
print $arr[$checkValue];	
}
else {
print "no data found";	
}
?>	

[grege]

Thanks for the response. Is it possible to implement that into the sql query tho? So that I will then only take results where table.zip = *Any array key in $arr* and then retrieve the distance

[JonnoTheDev]

<?php
$arr = array('111111' => 10, '222222' => 20);
$result = mysql_query("SELECT * FROM table WHERE table.zip IN(".implode(",", array_keys($arr)).")");
?>	

[grege]

Exactly what I am looking for I think. Gonna try it out now! Thanks so much neil, such quick responses.

[grege]

Running into a problem... It could be a parenthesis problem I am facing, but not quite sure.

 

Also, is there a way to retrieve that array value while in the query??

 

$z = implode(",", array_keys($zipArray));

sql: AND zip IN ('$z')

 

select distinct * from pubs 
where id LIKE '%test%' 
OR title LIKE '%test%' 
OR city LIKE '%test%' 
OR state LIKE '%test%' 
OR zip LIKE '%test%' 
OR website LIKE '%test%' 
OR description LIKE '%test%' 
AND zip IN ('08002,08003,08004,08007,08009,08010,08011,08012,08016,08018,08020,08021,08022,08025,08026,08027,08028,08029,08030,08031,08032,08033,08034,08035,08036,08037,08039,08041,08042,08043,08045,08046,08048,08049,08051,08052,08053,08054,08055,08056,08057,08059,08060,08061,08062,08063,08065,08066,08068,08071,08073,08074,08075,08076,08077,08078,08080,08081,08083,08084,08085,08086,08088,08089,08090,08091,08093,08094,08095,08096,08097,08098,08099,08101,08102,08103,08104,08105,08106,08107,08108,08109,08110,08217,08310,08312,08318,08322,08326,08328,08341,08342,08343,08344,08346,08347,08350,08352,08360,08361,08362,08518,08554,08640,18966,19001,19003,19004,19006,19007,19009,19010,19012,19018,19019,19020,19021,19022,19023,19025,19026,19027,19029,19030,19031,19032,19033,19034,19035,19036,19038,19040,19041,19043,19046,19047,19050,19053,19054,19055,19056,19057,19064,19066,19070,19072,19074,19075,19076,19078,19079,19082,19083,19090,19092,19093,19095,19096,19098,19099,19101,19102,19103,19104,19105,19106,19107,19108,19109,19110,19111,19112,19113,19114,19115,19116,19118,19119,19120,19121,19122,19123,19124,19125,19126,19127,19128,19129,19130,19131,19132,19133,19134,19135,19136,19137,19138,19139,19140,19141,19142,19143,19144,19145,19146,19147,19148,19149,19150,19151,19152,19153,19154,19155,19160,19161,19162,19170,19171,19172,19173,19175,19176,19177,19178,19179,19181,19182,19183,19184,19185,19187,19188,19191,19192,19193,19194,19195,19196,19197,19244,19255,19428,19444,19462') 
order by title

[JonnoTheDev]

The IN clause should not be in quotes. It should be as follows:

IN(08002,08003,08004)

Also, is there a way to retrieve that array value while in the query??

No, wouldn't you retrieve the array key value from the results of the query. After all you will be looping through the query results which contain the zipcode, so you essentially just obtain the value from the initial array i.e

 

<?php
while($row = mysql_fetch_assoc($result)) {
print "Distance: ".$arr[$row['zip']];
}
?>

 

On another note this query is terrible. Using LIKE on so many table fields is bad. If you are storing lots of text content you will find that this query takes a long time. If you are experienced enough you should be using a FULL TEXT search engine such as Sphinx to do this sort of querying. You would use Sphinx to retrieve records matching the text search and then filter down to those only containing the desired zip codes.

However, before jumping into this, see how you get on using MySQL.

[grege]

Thanks for the assistance. It worked correctly. And I will look into sphinx! Appreciate the help