Running a query based on another query using CodeIgniter

Question

I am populating information from a database to a drop down list on my home page. I have a table called "banks" with the respective column names "id" and "bankname". I also have another table called "bankbranches" with the respective column names "id","bankbranch" and "bank". The "bank" column in the bank branch has the same "id" of the "table". I want to run a query in my model to popupulate the bank branch based on the choice of the bank drop down list before it. So far, in my model I have:

        function getBank()
        {
            $query = $this->db->query('SELECT id,bankname FROM banks');
            return $query->result_array();
        }
        function getBankBranch()
        {
            $query = $this->db->query('SELECT id,bankbranch FROM bankbranches');
            return $query->result_array();

But the bank branch drop down is listing all the bank branches in my database. I want the drop down list for the bank branch to be populated based on the selection of the bank drop down by the user. Here is my view:

        <label>Bank</label>
                <select name="banks" id="banks">
                <option value="">--Please select--</option>
                <?php
                foreach($banks as $bank)
                {
                echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';

                }
                ?>  
        </select> <br/>

        <label>Bank Branch</label>
                <select name="bankbranches" id="bankbranches">
                <option value=""></option>
                <?php
                foreach($bankbranch as $branch)
                {
                    echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
                }
                ?>  
        </select> <br/>

Here is my controller:

        public function index()
        { 
          $this->load->helper('form');
          $data['cities'] = $this->site_model->getCity();
          $data['modes'] = $this->site_model->getPaymentMode();
          $data['banks'] = $this->site_model->getBank();
          $data['bankbranch'] = $this->site_model->getBankBranch();
          $data['categories'] = $this->site_model->getCategories();
          $data['staffs'] = $this->site_model->getStaff();

          $this->load->view('supplier_add',$data); 
        }

I have really tried to be elaborate.

Answer 1

In your model add a param in getBankBranch function, param name is $bank_id and then add a condition in your sql query.

In Model :

<?php
function getBankBranch($bank_id){
    $query = $this->db->query("SELECT id,bankbranch FROM bankbranches where bank = '$bank_id'");
    return $query->result_array();
}
?>

Add a new method in your controller BankBranch. this method retrieve branch name bank_id wise.

In Controller :

<?php
public function BankBranch() { 
    $this->load->helper('form');
    if ($this->input->is_ajax_request()) {
        $bank_id = $this->input->post('bank_id');
        $bankbranch = $this->site_model->getBankBranch($bank_id);

        echo '<option value=""></option>';
        foreach($bankbranch as $branch)
        {
            echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
        }
    }
}
?>

View Page :

<label>Bank</label>
        <select name="banks" id="banks">
        <option value="">--Please select--</option>
        <?php
        foreach($banks as $bank)
        {
        echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';

        }
        ?>  
</select> <br/>

<label>Bank Branch</label>
<select name="bankbranches" id="bankbranches">

</select>

Finally you can use jquery, and call your BankBranch() method from your controller by using AJAX Request.

In Script :

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<script>
jQuery(document).ready(function ($) {
    $('#banks').on('change', function (e) {
        e.preventDefault();
        var bank_id = $(this).val();

        var base_url = "<?php base_url(); ?>";

        $.ajax({
            url: base_url+"your_controller/BankBranch", // Url to which the request is send
            type: "POST", // Type of request to be send, called as method
            data: {bank_id:bank_id}, // Data sent to server, a set of key/value pairs representing form fields and values
        }).done(function (data) {
            $("#bankbranches").html(data);            
        }).fail(function (data) {
            console.log('failed');
        });
    });
}(jQuery));
</script>

Answer 2

You can use jquery and ajax to get branches when select a bank.

In your view :-

<label>Bank</label>
<select name="banks" id="banks">
    <option value="">--Please select--</option>
    <?php foreach($banks as $bank) {
        echo '<option value="'.$bank['id'].'">'.$bank['bankname'].'</option>';
    } ?>  
</select><br/>

<label>Bank Branch</label>
<select name="bankbranches" id="bankbranches">
    <option value=""></option>
</select> <br/>

<script type= text/javascript>
    $('#banks').on('change', function() {
        var bank = $(this).val();
        $.ajax({
            type: 'post',
            url: '<?php echo base_url('controller/get_branches'); ?>, //set your controller name here
            data: { bank_id : bank },
            success: function (response) {
                 $('#bankbranches').html(response); 
            }
        });
    });
</script>

In your controller :-

public function index()
{ 
    $this->load->helper('form');
    $data['cities'] = $this->site_model->getCity();
    $data['modes'] = $this->site_model->getPaymentMode();
    $data['banks'] = $this->site_model->getBank();
    $data['categories'] = $this->site_model->getCategories();
    $data['staffs'] = $this->site_model->getStaff();

    $this->load->view('supplier_add',$data); 
}

public function get_branches()
{ 
     $bank_id = $this->input->post('bank_id');
     $bankbranch = $this->site_model->getBankBranch($bank_id);
     foreach($bankbranch as $branch) {
         echo '<option value="'.$branch['id'].'">'.$branch['bankbranch'].'</option>';
     }
}

In your model

function getBankBranch($bank_id)
{
     $query = $this->db->query('SELECT id,bankbranch FROM bankbranches WHERE bank='.$bank_id);
     return $query->result_array();
}