while looping large results

[graham23s]

Hi Guys,

 

on the site i am making i have imported 42,000 us zip codes into mysql for the user to select accordingly, the UK codes work and cycle through fine as there is only a few thousand , but when it cycles through the us ones the page in un responsive for a short time then jumps back, is there any way to keep it reasonable or will i have to re-think this part

 

code:

 

<?php
if($users_country == 12)
{
  // select all british counties from mysql //
  $q_states_counties = "SELECT * FROM `cmd_counties_uk`";
  $r_states_counties = mysql_query($q_states_counties);
  //$r = mysql_fetch_array($r_states_counties);

while($r = mysql_fetch_array($r_states_counties))
{
   // vars //
   $var_name = $r['name'];
   
  print("<option value='$var_name'>$var_name</option>\n");
}
print("</select>");  
print("</td>\n");
print("</tr>\n");
print("<tr>\n");
print("<td class='form_style' align='left'><label><b>Post Code:</b></label></td><td class='form_style' align='left'>\n");
print("<select name='zip_post_code'>\n");

// select all british post codes //
$q_codes = "SELECT * FROM `cmd_codes_uk`";
$r_codes = mysql_query($q_codes);

while($row = mysql_fetch_array($r_codes))
{
   // vars //
   $var_code = $row['code']; 
   
  print("<option value='$var_code'>$var_code</option>\n");
}

// free up mysql //
mysql_close($r_codes);

print("</select>"); 
print("</td>\n");
print("</tr>\n");
########
# < ==============================================================================================
########  
  } elseif ($users_country == 2) {
  
  // select all british counties from mysql //
  $q_states_counties = "SELECT * FROM `cmd_counties_us`";
  $r_states_counties = mysql_query($q_states_counties);
  //$r = mysql_fetch_array($r_states_counties);

  while($r = mysql_fetch_array($r_states_counties))
  {
   // vars //
   //$var_id = $r['id'];
   $var_name = $r['name'];
   
   print("<option value='$var_name'>$var_name</option>\n");
}  
print("</td>\n");
print("</tr>\n");
print("<tr>\n");
print("<td class='form_style' align='left'><label><b>Zip Code:</b></label></td><td class='form_style' align='left'>\n");
print("<select name='zip_post_code'>\n");

// select all american post codes //
$q_codes = "SELECT * FROM `cmd_codes_us`";
$r_codes = mysql_query($q_codes);

while($row = mysql_fetch_array($r_codes))
{
   // vars //
   $var_code = $row['zip_code']; 
   
  print("<option value='$var_code'>$var_code</option>\n");
  
}  
// free up the resources on such a large query 42,000+ //
mysql_close($r_codes);

print("</select>");
print("</td>\n");
print("</tr>\n");
?>

 

cheers guys

 

Graham

[Cep]

Looking at your code, I must ask the question. Are you seriously expecting a user to open a drop down menu with 42000 results?

 

I think the problem here is you need to think a bit more about the poor user opening up your page. If you wanted to search for all holiday destinations in the world would you want to go through a drop down menu of tens of thousands of destinations? I think not, therefore do not expect your users to do the same here.

 

I would more likely use a text box and create a search function of some kind. Also I would try to reduce the amount of records you need to search based on extra fields for County, City, Town etc prior to reaching the post codes

 

 

[graham23s]

That ias all very true mate lol, it does seem crazy thinking back on it, i have looked at the code for a few other sites they have all the us codes in table, the good thing is they are numeric so a search box sounds good, then let them select one i pull out from mysql, thanks cep:)

 

Graham