Page 1 of 1

Array Woes [SOLVED]

Posted: Wed Aug 24, 2005 8:23 pm
by s.dot
Consider the following piece of code which behaves EXACTLY as I want it to.

Code: Select all

// Example $zips array [12345] => 1.235, [98547] => 2.514, [51485] =>3.214, [15845] => 9.5485

// Each key is a zip code, and each key value is a number in miles
// The array is sorted so that the value goes from smallest, to biggest

foreach ($zips AS $key => $value)
{
   $query = mysql_query("SELECT username FROM users WHERE zip = '$key' ORDER BY username ASC");
   while($array = mysql_fetch_assoc($query))
   {
      echo "<B>User:</B> ".$array['username']." - <B>Distance:</B> $value miles.<BR>";
   }
}
Result:

Code: Select all

User: cuddles - Distance: 1.47 miles.
User: jessipoo52 - Distance: 1.47 miles.
User: quinnbee2000 - Distance: 3.19 miles.
User: lilmissolivia06 - Distance: 9.45 miles.
User: d152guy - Distance: 16.61 miles.
User: shylilsweetie986 - Distance: 25 miles.
Perfect! The problem lies that this will loop xx amount of times, depending on the number of elements in the array... which can take several minutes in some cases. So I've been trying to do it in one query.. here's what I have so far.

Code: Select all

// Example $zips array [12345] => 1.235, [98547] => 2.514, [51485] =>3.214, [15845] => 9.5485

// Each key is a zip code, and each key value is a number in miles
// The array is sorted so that the value goes from smallest, to biggest

$ziparray = array_keys($zips); // returns array of ZIP codes only
$newzips = implode(",",$ziparray); // returns a comma delimited list
$query = mysql_query("SELECT username FROM users WHERE zip IN ($newzips)") or die(mysql_error());
while($array = mysql_fetch_assoc($query))
{
   echo "<B>User:</B> ".$array['username']."<BR>";
}
The problem with this piece of code is I do not know how to order the results by distance (which would be the key of the Example $zips array), and I don't know how to show the distance (the value of $key in the Example $zips array). Some help please. =)

I hope I explained this as clearly as possible :-D

Posted: Wed Aug 24, 2005 8:39 pm
by Stewsburntmonkey
I think the easiest thing to do would be instead of printing directly from the SQL query, to add the results to an array and then sort the array.

Code: Select all

// Example $zips array [12345] => 1.235, [98547] => 2.514, [51485] =>3.214, [15845] => 9.5485

// Each key is a zip code, and each key value is a number in miles
// The array is sorted so that the value goes from smallest, to biggest

$ziparray = array_keys($zips); // returns array of ZIP codes only
$newzips = implode(",",$ziparray); // returns a comma delimited list
$query = mysql_query("SELECT username, zip FROM users WHERE zip IN ($newzips)") or die(mysql_error());
while($array = mysql_fetch_assoc($query))
{
   // assumes username is unique
   $result[$array['username']]  = $zips[$array['zip']];
}

asort($result);

foreach($result as $user => $distance) {
   echo "<B>User:</B> $user - <B>Distance:</B> $distance miles.<BR>"; 
}
:)

Posted: Wed Aug 24, 2005 8:46 pm
by taldos
You should be able to use "in"

Code: Select all

$zip = "YOUR CREATED ARRAY";
$my_string = "";
$totalCount = count($zip);

foreach($zip as $key => $value)
{
	if($key + 1 == $totalCount)
		$my_string .= $key;
	else
		$my_string .= $key.",";
}

$query = mysql_query("SELECT zip, username FROM users WHERE zip in(".my_string.") ORDER BY username ASC"); 

while($row = mysql_fetch_array($query))
{
	echo ("<B>User:</B> ".$row['username']." - <B>Distance:</B> ".$zip[$row['zip']]." value miles.<BR>");
}

Posted: Wed Aug 24, 2005 8:50 pm
by Stewsburntmonkey
taldos wrote:

Code: Select all

foreach($zip as $key => $value)
{
	if($key + 1 == $totalCount)
		$my_string .= $key;
	else
		$my_string .= $key.",";
}
This is what the function "implode()" in the original code does. No need to reinvent the wheel after all.

Code: Select all

$query = mysql_query("SELECT zip, username FROM users WHERE zip in(".my_string.") ORDER BY username ASC"); 

while($row = mysql_fetch_array($query))
{
	echo ("<B>User:</B> ".$row['username']." - <B>Distance:</B> ".$zip[$row['zip']]." value miles.<BR>");
}
I believe the goal is to order by distance (as it relates to zipcode) not by name. :)

Posted: Wed Aug 24, 2005 8:52 pm
by s.dot
Stews, you are correct on all counts. :P I will give this piece of code a try, and let you know the outcome.

Posted: Wed Aug 24, 2005 8:56 pm
by Stewsburntmonkey
Heh, I just noticed that I multiplied the zipcode by the distance in my code for some reason (I guess am too used to doing conversions this way). I have fixed the code so that it should be correct now (no guarantees though). :)

Posted: Wed Aug 24, 2005 9:00 pm
by s.dot
I assumed that, as there was no need for any operators :P You also left out an ending ']' BUT the code works perfect, and very fast!

I shall bow down to your superior knowledge in all future posts. :)