0

Consider the following structure:

A table named "Regions" with the columns "id","name".

A table named "Cities" with the columns "id","region_id","name".

What is the best way to repopulate an html select options using PHP and Javascript?

For example lets say I have the following php code:

<select name = "region">
<option value = "">Select Region</option>
<?
 foreach ($regions as $region)
 {
  echo "<option value = '$region[id]'>region[name]</option>";
 }
?>
</select>

<select name = "city">
 <option value = "">Select City</option>
</select>

Once a region has been selected, all the cities with the proper foreign key "region_id" that corresponds to the selected region, will be populated into the cities select.

Assume that I use this kind of structure a lot, so I do want to have a minimal, easy to reuse code.

Jacob Cohen
  • 1,232
  • 3
  • 13
  • 33
  • check accepted answer in this question http://stackoverflow.com/questions/5861090/populating-one-select-box-based-on-the-selection-in-another-select-box-jquery – waldek_h Feb 16 '14 at 12:23
  • This is what I am already doing. This requires me to create a new file for different kind of tables, or basically for the slightest change in the structure, I need to change the table. I'm looking for a more elegant classic and dynamic way. Thanks for this answer anyway. – Jacob Cohen Feb 16 '14 at 12:26
  • Don't realy understand, to populate second option list dependend on value in first one use simple json structure, for example: `{data: {'itemId': 'itemValue'}}` then iterate on returned object, you only have to return data same way in every case, so your table structure doesn't matter – waldek_h Feb 16 '14 at 12:32
  • True, but consider I have 20 selects on the same page, forget 20, 100! I have 100 selects, each select influence another... Am I to make 100 php pages? I need something way more dynamic than the solution presented by the topic you linked. – Jacob Cohen Feb 16 '14 at 13:00
  • Why 20...100 pages? pass to ajax target additional params: table name, parent_key col name, and other info u need, of course you have to remember about data validation and security on server side – waldek_h Feb 16 '14 at 13:05
  • Yeah exactly what I am looking for! In the end of the day I want to be able to do something in php like print_associated_select(table_name,foreign_key); – Jacob Cohen Feb 16 '14 at 13:41

1 Answers1

1

HTML

<select class="selectbox" name = "region" data-table="regions" data-parent="parent_id" data-name="name" data-target="targetselect">
    <option value = "">Select Region</option>
    <? foreach ($regions as $region): ?>
    <option value = '<?php echo $region[id];?>'><?php echo region[name]; ?></option>
    <?php endforeach;?>
</select>

<select name = "city" class="targetselect">
    <option value = "">Select City</option>
</select>

* JS *

jQuery(document).ready(function($){
    $('.selectbox').on('change', function(e) {
        var target = $(this).data('target');
        var data = $(this).data();
        data.key = $(this).val();            

        $.ajax({
            url: 'ajax.php',
            data: data,
            type: 'POST'
        }).success(function(response){
            //check if response
            if(response.items) {
                 $.each(response.items, function(){
                     target.append($('<option></option>')
                         .attr('value', this.id)
                         .text(this.name));
                 });
            }
        });
    })
});

* PHP *

/***
 * Check if You have all required data
 **/

 if(isset($_POST['table']) &&  .... ) {
     /**
      * Do all security checks for example if table is allowed to query etc
      **/
     $query = 'SELECT required_cols ....';
     $result = /** Query results **/;
     echo json_encode($result, true);
 }

it's lots of not checked code, i was writing it without testing, but should give you idea where to start

waldek_h
  • 930
  • 9
  • 16
  • Ill run some tests, It seems like we're on the right way! I can tell you by just browsing on it that append may screw things up when you choose different options in succession, but html() should do the trick. This should be a built in php event IMO! – Jacob Cohen Feb 16 '14 at 13:38
  • it's just an idea, if You want to return html options in your ajax response, fine by me :) – waldek_h Feb 16 '14 at 13:57