Alternative for huge database list

Question

Ok so I have three tables which contains list World's countries their states and their cities for my registration form. The problem is that the list of the cities is too huge. It contains 48,314 entries in total. So my site is getting hanged and the browser is showing messages to stop script. I am using mozilla for browser purpose.

This is the code I am using to get the cities, states and countries:

$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();

This is my jQuery code:

$(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);
    });
});

This is my 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>

Now can anyone please help me getting a solution as to how I can load it completely smoothly without getting my site hanged whenever the page is refreshed?

Answer 1

You could load the states and cities dynamically once the "parent" selection is made. This would reduce the amount of data. No clear code because I think you know what you are doing, but the idea:

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

EDIT: (code)

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 part: (subselects.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>

This dynamically generates full selectboxes and puts them into the empty divs "states and "cities". Of course you need to output the selectbox in the php code. Parent of states is country and parent of cities is states. Hope this explains it.