巨大的数据库列表的替代方案 - Alternative for huge database list

好的，我有三个表，其中包含世界上的国家，州和城市的列表，用于我的注册表单。问题是城市名单太大了。它总共包含48314个条目。所以我的网站被绞死，浏览器显示消息，以停止脚本。我使用mozilla作为浏览器。

这是我用来获取城市，州和国家的代码:

$country = "SELECT * FROM countries";
$country = $pdo->prepare($country);
$country->execute();
$state = "SELECT * FROM states";
$state = $pdo->prepare($state);
$state->execute();
$city = "SELECT * FROM cities";
$citq = $pdo->prepare($city);
$citq->execute();

这是我的jQuery代码:

$(document).ready(function () {
    $("#country").change(function() {
      if ($(this).data('options') == undefined) {
        $(this).data('options', $('#state option').clone());
      }
      var id = $(this).val();
      var options = $(this).data('options').filter('[value=' + id + ']');
      $('#state').html('<option value="">Select State</option>').append(options);
    });
    $("#state").change(function() {
      if ($(this).data('options') == undefined) {
        $(this).data('options', $('#city option').clone());
      }
      var id = $(this).val();
      var options = $(this).data('options').filter('[value=' + id + ']');
      $('#city').html('<option value="">Select City</option>').append(options);
    });
});

这是我的HTML:

<select name="country" id="country">
<option value="">Select Country</option>
<?php while($i = $country->fetch()){ extract($i); ?>
<option value="<?php echo $id; ?>"><?php echo $name; ?></option>
<?php } ?>
</select>
<select name="state" id="state">
<option value="">Select State</option>
<?php while($j = $state->fetch()){ extract($j); ?>
<option value="<?php echo $country_id; ?>" data="<?php echo $id; ?>"><?php echo $name; ?></option>
<?php } ?>
</select>
<select name="city" id="city">
<option value="">Select City</option>
<?php while($k = $citq->fetch()){ extract($k); ?>
<option value="<?php echo $id ; ?>" data="<?php echo $state_id; ?>"><?php echo $name ; ?></option>
<?php } ?>
</select>

现在有人可以帮助我得到一个解决方案，我如何能完全顺利地加载它没有得到我的网站挂每当页面刷新?

一旦选择了"parent"，就可以动态加载州和城市。这将减少数据量。没有明确的代码，因为我认为你知道你在做什么，但是这个想法:

-> [html] select 
-> [js] onChange call php with ajax
-> [php] SQL select states where country="chosencountry" 
-> [js] update form/selectbox

编辑:(代码)

JS:

<script>
        function BuildSelectbox(job,parent) {
                try { req = window.XMLHttpRequest?new XMLHttpRequest(): 
                      new ActiveXObject("Microsoft.XMLHTTP"); 
                     } catch (e) {  /* No AJAX Support */ }
                req.open('get','subselects.php?job='+job+'&parent='+parent);
                /* let the php echo the resultvalue */
                req.onreadystatechange = function() {
                 handleResponse(div);
                };
                req.send(null);
        }
        function handleResponse(div) {

               if ((req.readyState == 4) && (req.status == 200)) {
                   document.getElementById(job).value=req.responseText;
               }
        }
</script>

PHP部分:(subselect . PHP)

<?
if ($_GET["job"]=="states") {
// assuming there is a key country in states
$state = "SELECT * FROM states where country=".$_GET["parent"];
$state = $pdo->prepare($state);
$state->execute();
} else {
// assuming there is a key state in cities
$city = "SELECT * FROM cities where state=".$_GET["parent"];
$citq = $pdo->prepare($city);
$citq->execute();
}
// echo the whole selectbox
echo '<select id="'.$_GET["job"].'">';
// put the option loop from your queryresult here
echo '</select>';    
?>

HTML:

<div id="countries" onChange="BuildSelectbox('states',this.selectedIndex);>
<select name="country" id="country">
<option value="">Select Country</option>
<?php while($i = $country->fetch()){ extract($i); ?>
<option value="<?php echo $id; ?>"><?php echo $name; ?></option>
<?php } ?>
</select>
</div>
<div id="states"></div>
<div id="cities"></div>

这会动态地生成完整的选择框，并将它们放入空div "states "answers"cities"中。当然，您需要在php代码中输出选择框。州的上级是国家，城市的上级是州。